# How to generate Additional Rows

Dear Experts,

I have the following scenario:

create table t1(val number, carg varchar2(10));
insert into t1 values(10,'ST');
insert into t1 values(3,'RF');
create table t2(id number, val number, carg varchar2(10));
insert into t2 values(1,3,'ST');
insert into t2 values(1,1,'ST');
insert into t2 values(1,3,'RF');

COMMIT;

Currently if i join t1 to t2 on the basis of 'carg' column, i get following output:

1  SELECT t1.*, t2.*
2  FROM t1, t2
3* WHERE t1.carg=t2.carg
SQL>/

VAL CARG   ID   VAL CARG
---------- ----- ---   --- ----
10 ST      1    3  ST
10 ST      1    1  ST
3 RF      1    3  RF

I want to generate additional record if for each value of t1.carg, we have t1.val > sum(t2.val). So taking above example, i should get output like:

VAL CARG   ID   VAL CARG
---------- ----- ---   --- ----
10 ST      1    3  ST
10 ST      1    1  ST
3 RF      1    3  RF
10 ST   NULL    6  ST

Where 6 in the last row = Value of t1.Val - sum(t2.val) for that carg = 10 - 4 = 6

And for carg 'RF' there wouldn't be any additional row since Vale of t1.val = sum(t2.val) for carg 'RF'.

Thanks in advance for your help.

Riaz
LVL 13
Asked:
###### Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Commented:
SELECT t1.*, t2.*
FROM t1, t2
WHERE t1.carg = t2.carg
UNION ALL
SELECT t1.val, t2.carg, NULL, t1.val - SUM(t2.val), t2.carg
FROM t1, t2
WHERE t1.carg = t2.carg
GROUP BY t1.val, t2.carg
HAVING SUM(t1.val) > SUM(t2.val)
0
Data ArchitectCommented:
select T1.*, T2.*
from T1, T2
where T1.carg = T2.carg
UNION ALL
select T1.val, T1.carg, null, T1.val - sval, X.carg
from
(select carg, sum(val) sval
from t2
group by carg) X, T1
where T1.carg = X.carg
and T1.val > sval
/
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Commented:
One more variation just for fun...
0
Commented:
oops, might help if I posted the query too!  :)

I had to do one without the extra union all on a query of the same data.
This does do half the "gets" as the others but adds another sort.

/* Use a join to a dummy table of 2 rows to replicate just the data we want*/
SELECT t1val, t1carg, ID, t2val, t2carg
FROM (SELECT DISTINCT x, t1val, t1carg, DECODE(x, 'base', ID, NULL) ID,
DECODE(x, 'base', t2val, sumt2) t2val, t2carg
FROM (SELECT t1.val t1val, t1.carg t1carg, t2.ID, t2.val t2val, t2.carg t2carg,
t1.val - SUM(t2.val) OVER(PARTITION BY t2.carg) sumt2
FROM t1, t2
WHERE t1.carg = t2.carg),
(SELECT 'base' x
FROM DUAL
UNION ALL
SELECT 'extra'
FROM DUAL)
WHERE x = 'base' OR(x = 'extra' AND sumt2 > 0))
0
Commented:
riazpk - May we ask why just a B grade? Was there anything more that you had wanted ??
0
Commented:
Hi jinesh good to have you back in the Oracle questions

riazpk,  I know it took 5 months for you to accept an answer but please don't accept prematurely and then penalize the volunteers with low grades without giving them the opportunity to help more if you needed it.
0
###### It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.