Guys this is urgent
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: Guys this is urgent

  1. #1
    Join Date
    Nov 2002
    Location
    INDIA
    Posts
    38

    Guys this is urgent

    I HAVE A PROBLEM IN HAND,

    I have a table A with records as under

    Col1 col2 col3
    ----- ----- -----
    A 1 ok
    B 1 ok
    C 1 ok
    D 1 ok

    table B

    Col1 col2 col3
    ----- ----- -----
    C 0 ok
    D 0 ok
    E 0 ok
    F 0 ok

    I want to output to be

    Col1 col2 col3
    ----- ----- -----
    A 1 ok
    B 1 ok
    C 1 ok
    D 1 ok
    E 0 ok
    F 0 ok


    Thanks and regards,

    Paddy

  2. #2
    Join Date
    Oct 2001
    Location
    Madrid, Spain
    Posts
    763
    The only really urgent thing here is you need to read sql docs

  3. #3
    Join Date
    Oct 2002
    Location
    Singapore
    Posts
    16

    Cool

    Paddy,

    Please...What you have posted in not a problem.

    Hope this helps.

    select * from a
    union
    select * from b
    where b.col1 not in ( select COL1 from a intersect select COL1 from b);

    This will work considering the data pattern being the same as you have given in the example
    Never Ever Give Up!

  4. #4
    Join Date
    Apr 2001
    Location
    Louisville KY
    Posts
    295
    alternative

    select a,max(b),c
    from { select * from a
    union
    select * from b)
    group by b
    order by a,b,c
    Joseph R.P. Maloney, CSP,CDP,CCP
    'The answer is 42'

  5. #5
    Join Date
    Apr 2002
    Location
    Shenzhen, China
    Posts
    327
    A BIT MODIFICATION

    select a,max(b),c
    from ( select * from a
    union
    select * from b)
    group by a,c
    order by a

    aonther query:

    SELECT DECODE(A.COL1, '', B.COL1, A.COL1) "C",
    DECODE(A.COL2, '', 0, A.COL2)+DECODE(B.COL2, '', 0, B.COL2) "I",
    DECODE(A.COL3, '', B.COL3, A.COL3) "OK"
    FROM A FULL OUTER JOIN B ON (A.COL1=B.COL1)
    ORDER BY C
    Oracle Certified Master - September, 2003, the Second OCM in China
    *** LOOKING for PART TIME JOB***
    Data Warehouse & Business Intelligence Expert
    MCSE, CCNA, SCJP, SCSA from 1998

  6. #6
    Join Date
    Nov 2002
    Location
    INDIA
    Posts
    38
    Calvin_Qiu,Joseph and eeswar thanks for the response..

    Actually I was working on a querry which have lots of subquerries and unions..The problem I stated was just a part of that...

    I have tried all the options submitted by you guys..

    In my case Josephs soln seem to give a better performance..

    And calvin the second query submitted by you seems to not work in Oracle...however I am very curious to work on it..

    Thanks for all the help extended,

    Regards,

    Paddy

  7. #7
    Join Date
    Apr 2002
    Location
    Shenzhen, China
    Posts
    327
    It is a new feature of Oracle 9i.
    Oracle Certified Master - September, 2003, the Second OCM in China
    *** LOOKING for PART TIME JOB***
    Data Warehouse & Business Intelligence Expert
    MCSE, CCNA, SCJP, SCSA from 1998

  8. #8
    Join Date
    Nov 2002
    Location
    INDIA
    Posts
    38
    Hi Calvin,

    I am working on oracle 8i,
    Can we rephrase this so that it will work in 8i as well..

    I was interested in it because it doesnot have any union or a sub query..This will positively reduce the execution time..

    If you look into this it will be of a great help.

    Thanks for you help Calvin.


    Warm Regards,

    Paddy

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width