Home > Default > Need help creating package

Need help creating package

November 30Hits:0
Table A
column1 column2
1 A
2 A
3 A
4 B
Table B (aggregate of Table A)
column1 column2
A 3
B 1
Table C is new data for Table A and Table A.column1 = Table B.column1
column1 column2
1 C
2 C
3 C
4 D
I need to make a package that will aggregate the data in Table C, update the existing records in Table B, and insert records into Table B. The result should look like this:
Table A
column1 column2
A 0 (3-3)
B 0 (1-1)
C 3
D 1


Assuming that the same id (like 'A', 'B', ...) never occurs in both table_b and table_c (which is the case in your test data):
WITH     a_summary     AS
     SELECT       column2     AS id
     ,       COUNT (*)     AS cnt
     FROM       table_a
     GROUP BY  column2
,     b_c_summary     as
     SELECT     column1          AS id
     ,     COLUMN2          AS cnt
     FROM     table_b
     SELECT    column2     AS id
     ,       COUNT (*)     AS cnt
     FROM       table_c
     GROUP BY  column2
SELECT     bc.id               AS column1
,     bc.cnt - NVL (a,cnt, 0)     AS column2
FROM              b_c_summary  bc
LEFT OUTER JOIN      a_summary    a          ON     bc.id     = a.id
;If there is some overlap between b and c, you'll have to change b_c_summary; it may involve a FULL OUTER JOIN instead of UNION ALL. If so, post a new set of sample data (CREATE TABLE and INSERT statements) and the results you want from that data.
Like Anurag said, you don't need a package or any kind of PL/SQL to get these results, but you can use a query like this in a package if you want to.
Edited by: Frank Kulash on Dec 10, 2009 10:46 AM
If you need to change table_b, you can use a query like this as a sub-query in a MERGE statement, as Beijing said.

Read other 6 answers


Related Articles

Copyright (C) 2019 wisumpire.com, All Rights Reserved. webmaster#wisumpire.com 14 q. 0.632 s.