1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

A SQL question,Help!

Discussion in 'General' started by virusx1984, Feb 7, 2011.

  1. virusx1984

    virusx1984 Active Member

    Messages:
    33
    Likes Received:
    0
    Trophy Points:
    80
    I have 2 table:
    Table plan:
    pn|2/1|2/2|2/3
    A|10|0|20
    B|5|5|15

    and

    Table act:
    pn|2/1|2/2|2/3
    A|9|2|14
    B|3|6|8

    How to write a SQL ,and the query result will be:
    Table rt:
    pn|2/1|2/2|2/3
    A|10|0|20
    A|9|2|14
    A|-1|2|-6
    B|5|5|15
    B|3|6|8
    B|-2|1|-7

    *row 1: plan of A
    row 2: actual of A
    row 3: actual of A - plan of A
    ......

    before I know how to use 1 SQL to solve it,I use code to accomplish that problem,can you help me?thks.
     
  2. Sadik

    Sadik Community Moderator Forum Guru

    Messages:
    1,906
    Likes Received:
    252
    Trophy Points:
    1,455
    Are 2/1 2/2 2/3 columns?
     
  3. virusx1984

    virusx1984 Active Member

    Messages:
    33
    Likes Received:
    0
    Trophy Points:
    80
    yes, 2/1 is date (Feb 1); 2/2 (Feb 2) ...
     
  4. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Not if you don't tell us which release of Oracle you're using.
     
  5. virusx1984

    virusx1984 Active Member

    Messages:
    33
    Likes Received:
    0
    Trophy Points:
    80
    the version is 10g. thks
     
  6. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Marketing information doesn't help much, please report what Oracle displays at the SQL*Plus login banner or what this query returns:

    select banner from v$version;
     
  7. virusx1984

    virusx1984 Active Member

    Messages:
    33
    Likes Received:
    0
    Trophy Points:
    80
    Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
    PL/SQL Release 11.2.0.1.0 - Production
    CORE 11.2.0.1.0 Production
    TNS for 32-bit Windows: Version 11.2.0.1.0 - Production
    NLSRTL Version 11.2.0.1.0 - Production
     
  8. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    This is far from being '10g'. That notwithstanding you can use the WITH clause to generate three separate queries then union them together in the final query ordering by column 1 ascending and column 2 descending (this presumes, of course, that actual values are always less than the planned values), for example:

    Code (SQL):
     
    WITH a AS (SELECT * FROM x),
    b AS (SELECT * FROM y),
    c AS (SELECT p.pn, a."2/1"-p."2/1" diff1, a."2/2" - p."2/2" diff2, a."2/3" - p."2/3" diff3
    FROM x p, y a WHERE a.pn = p.pn)
    SELECT * FROM a
    UNION
    SELECT * FROM b
    UNION
    SELECT * FROM c
    ORDER BY 1, 2 DESC;
     
     
    virusx1984 likes this.
  9. virusx1984

    virusx1984 Active Member

    Messages:
    33
    Likes Received:
    0
    Trophy Points:
    80
    Thank you very very much!!!!!!!!!!!! It works.