query problem
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: query problem

  1. #1
    Join Date
    Nov 2000
    Posts
    440

    query problem

    Hi, i have the following:

    CREATE OR REPLACE TABLE T007
    (
    NO_COMM NUMBER(6),
    QUAN_RAMA NUMBER(9),
    NO_PROD VARCHAR2(20)
    );
    DELETE FROM T007;
    --
    INSERT
    INTO T007
    (NO_COMM, QUAN_RAMA, NO_PROD)
    VALUES (1, 10, 'PONTIAC-FIREBIRD');
    INSERT
    INTO T007
    (NO_COMM, QUAN_RAMA, NO_PROD)
    VALUES (2, 5, 'PONTIAC-FIREBIRD');
    INSERT
    INTO T007
    (NO_COMM, QUAN_RAMA, NO_PROD)
    VALUES (3, 5, 'CHEVROLET-CAMARO');
    COMMIT;
    --
    --
    --
    SELECT a.no_prod no_prod, a.quan_rama qte_total, b.no_comm, b.quan_rama qte_comm
    FROM
    (
    SELECT no_prod, SUM(NVL(quan_rama,0)) quan_rama
    FROM t007
    WHERE no_comm IN (1, 2)
    GROUP BY no_prod
    ) a,
    (
    SELECT no_prod, no_comm, quan_rama
    FROM t007
    WHERE no_comm IN (1, 2)
    ) b
    WHERE a.no_prod = b.no_prod;







    -- This is what the preceeding query fetch as result :
    /*
    SQL> SELECT a.no_prod no_prod, a.quan_rama qte_total, b.no_comm, b.quan_rama qte_comm
    2 FROM
    3 (
    4 SELECT no_prod, SUM(NVL(quan_rama,0)) quan_rama
    5 FROM t007
    6 WHERE no_comm IN (1, 2)
    7 GROUP BY no_prod
    8 ) a,
    9 (
    10 SELECT no_prod, no_comm, quan_rama
    11 FROM t007
    12 WHERE no_comm IN (1, 2)
    13 ) b
    14 WHERE a.no_prod = b.no_prod;

    NO_PROD__________QTE_TOTAL__NO_COMM__QTE_COMM
    -------------------- ------------ ----------- ---------
    PONTIAC-FIREBIRD___________15_________1_______10
    PONTIAC-FIREBIRD___________15_________2________5
    */
    (underscore mean space)


    -- The two first cell of the second row should be empty, like this example below :
    /*
    SQL> SELECT a.no_prod no_prod, a.quan_rama qte_total, b.no_comm, b.quan_rama qte_comm
    2 FROM
    3 (
    4 SELECT no_prod, SUM(NVL(quan_rama,0)) quan_rama
    5 FROM t007
    6 WHERE no_comm IN (1, 2)
    7 GROUP BY no_prod
    8 ) a,
    9 (
    10 SELECT no_prod, no_comm, quan_rama
    11 FROM t007
    12 WHERE no_comm IN (1, 2)
    13 ) b
    14 WHERE a.no_prod = b.no_prod;

    NO_PROD___________QTE_TOTAL__NO_COMM__QTE_COMM
    -------------------- ------------ ----------- ------------
    PONTIAC-FIREBIRD___________15_________1__________10
    ______________________________________2___________5

    (underscore mean space)




    /*
    I want the following results from my query,
    the first line will show:
    1st cell : no_prod
    2nd cell : the total of qte for all no_comm
    3rd cell : the first no_comm who had that product
    4th cell : the qte for that order
    --
    and the second line (when there's more than a no_comm with the same product)
    --
    1st cell : EMPTY
    2nd cell : EMPTY
    3rd cell : the second no_comm who had that product
    4th cell : the qte for that order


    */



    How can i do that?
    Last edited by steeve123; 07-26-2006 at 11:16 AM.

  2. #2
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    What is the PK?

    Tamil

  3. #3
    Join Date
    Nov 2000
    Posts
    440
    Hum, does the PK matters ?
    supposed it's NO_COMM & NO_PROD,
    the real PK is not showed, cause there's too much column in the real table.

    Thanks.

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