[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 365
  • Last Modified:

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
0
riazpk
Asked:
riazpk
  • 3
  • 2
2 Solutions
 
Jinesh KamdarCommented:
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
 
sujith80Commented:
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
 
sdstuberCommented:
One more variation just for fun...
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
sdstuberCommented:
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
 
Jinesh KamdarCommented:
riazpk - May we ask why just a B grade? Was there anything more that you had wanted ??
0
 
sdstuberCommented:
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

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now