-
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 10:16 AM.
-
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|