Ora_Techie
asked on
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
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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
One more variation just for fun...
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))
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))
riazpk - May we ask why just a B grade? Was there anything more that you had wanted ??
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.
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.