Post reply

Oracle syntax needed

  • August 24, 2005 at 2:20 pm

    #90635

    Does anyone know how to write a UNION in Oracle? 

    TIA

    I wasn't born stupid - I had to study.

  • August 25, 2005 at 6:30 am

    #584925

    Hello Farrel,

    from Oracle 9.2 documentation:

    UNION ALL Example(s)

    The UNION operator returns only distinct rows that appear in either result, while the UNION ALL operator returns all rows. The UNION ALL operator does not eliminate duplicate selected rows:

    SELECT product_id FROM order_items
    UNION
    SELECT product_id FROM inventories;
    SELECT location_id  FROM locations 
    UNION ALL 
    SELECT location_id  FROM departments;

    A location_id value that appears multiple times in either or both queries (such as '1700') is returned only once by the UNION operator, but multiple times by the UNION ALL operator.

    Hope it will help you.

    Regards,

    Carl

  • August 25, 2005 at 6:37 am

    #584927

    Isn't UNION [ALL] ainsi standard??

  • August 25, 2005 at 7:29 am

    #584947

    Thank you!  This is not actually my query, but since I had this forum available to me I told them I would help.  The error, (I only got today) is probably not the UNION.  It is a datatype incompatibility.  I have suggested other approaches for them to take...  (Its kind of like getting a posting here without the DDL or error statement ). 

     

    I wasn't born stupid - I had to study.

  • August 25, 2005 at 7:33 am

    #584949

    I'd say it's exactly like that .

  • August 25, 2005 at 11:57 am

    #585111

    We probably won't search this down since we can get what need by going around the barn and this is not run on a regular basis.  But it was odd, and I thought it would be interesting to post. 

    This is what I got for the error:

    The error we get is from DTS package, either sql statement is ok, union them together will get "incorrect datatye…..' error, second select statement is copied from the first and take out ICD_ASGN_INVOLVEMENTS

    But, when they used UNION ALL instead of UNION, it ran perfectly.  Bizarre.  Unfortunately, that does allow for duplicates.  I am not going to go into the code because it is not my db and I can not explain the fields - anyway, they did find a way to get the data they needed. 

    I just found odd that UNION failed with a data type error, but UNION ALL worked...

    I wasn't born stupid - I had to study.

  • August 25, 2005 at 12:06 pm

    #585113

    Union all doesn't have to do the distinct... distinct implies comparaison and that takes data types compatibility, which you get by converting .

  • August 25, 2005 at 12:48 pm

    #585132

    UNION and UNION ALL are ANSI SQL UNION does an implict Distinct to remove the duplicates while UNION ALL leaves the duplicates in.  I think you can use the convert to satisfy the same order of datatypes requirement in UNION.  Hope this helps.

    Kind regards,

    Gift Peddie

    Kind regards,
    Gift Peddie

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply