Home » SQL & PL/SQL » SQL & PL/SQL » New Row in Table
New Row in Table [message #224294] Tue, 13 March 2007 15:00 Go to next message
milford_f
Messages: 18
Registered: July 2006
Junior Member
Hello There,

I have 2 table which i am joining using an outer join, however I need an addition line in the final table. How do I write my script. Thanks in advance.

Table 1
Tracer     Line No.     Item     Total Qty     Invoiced Qty
11111       6           4455        100            90

Table 2
Tracer     Line No.     Delivery Date     Delivery Qty
11111       6           20/3/2007             5
11111       6           30/3/2007             2

Required Table
Tracer     Line No.     Item     Delivery Date    Outstanding Qty
11111       6           4455      20/3/2007          5
11111       6           4455      30/3/2007          2  
11111       6           4455                         3


Cheers
Milford.

[Mod-Edit: Added code tags for readability]

[Updated on: Tue, 13 March 2007 15:14] by Moderator

Report message to a moderator

Re: New Row in Table [message #224296 is a reply to message #224294] Tue, 13 March 2007 15:14 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
You say you already use an outer join.
What does your query look like, and what is its output?
Re: New Row in Table [message #224345 is a reply to message #224294] Wed, 14 March 2007 00:56 Go to previous messageGo to next message
milford_f
Messages: 18
Registered: July 2006
Junior Member
My output is
Table 2
Tracer Line No. Item Delivery Date Outstanding Qty
11111 6 4455 20/3/2007 5
11111 6 4455 30/3/2007 2

Outstanding Qty = Total Qty – Invoiced Qty (i.e 100 -90 = 10)
In My table Outstanding Qty for that item/line no. is 7. I am missing 3, So I need to insert another line, with a blank delivery date, and put the balance outstanding qty (which is 3) in the table.

Cheers
Milford.
Re: New Row in Table [message #224368 is a reply to message #224345] Wed, 14 March 2007 01:59 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Ah, now I understand.

SQL> drop table faq;

Table dropped.

SQL> drop table faq2;

Table dropped.

SQL> create table faq
  2  ( tracer	    number
  3  , line_no	    number
  4  , item	    number
  5  , total_qty    number
  6  , invoiced_qty number
  7  );

Table created.

SQL> create table faq2
  2  ( tracer	     number
  3  , line_no	     number
  4  , delivery_date date
  5  , delivery_qty  number
  6  );

Table created.

SQL> insert into faq
  2  ( tracer, line_no, item, total_qty, invoiced_qty) values ( 11111, 6, 4455, 100, 90);

1 row created.

SQL> insert into faq2
  2  ( tracer, line_no, delivery_date, delivery_qty) values (11111, 6, to_date('20-03-2007', 'dd-mm-yyyy'), 5);

1 row created.

SQL> insert into faq2
  2  ( tracer, line_no, delivery_date, delivery_qty) values (11111, 6, to_date('30-03-2007', 'dd-mm-yyyy'), 2);

1 row created.

SQL> 
SQL> select faq.tracer
  2  ,	    faq.line_no
  3  ,	    faq.item
  4  ,	    faq2.delivery_date
  5  ,	    faq2.delivery_qty
  6  from   faq
  7  ,	    faq2
  8  where  faq.tracer = faq2.tracer
  9  and    faq.line_no = faq2.line_no
 10  union all
 11  select faq.tracer
 12  ,	    faq.line_no
 13  ,	    faq.item
 14  ,	    to_date(null)
 15  ,	    (faq.total_qty - faq.invoiced_qty) - nvl(sum(faq2.delivery_qty), 0)
 16  from   faq
 17  ,	    faq2
 18  where  faq.tracer = faq2.tracer
 19  and    faq.line_no = faq2.line_no
 20  group  by faq.tracer
 21  ,	    faq.line_no
 22  ,	    faq.item
 23  ,	    faq.total_qty
 24  ,	    faq.invoiced_qty
 25  having (faq.total_qty - faq.invoiced_qty) - nvl(sum(faq2.delivery_qty), 0) > 0
 26  order  by 3 nulls last
 27  ;

    TRACER    LINE_NO       ITEM DELIVERY_ DELIVERY_QTY
---------- ---------- ---------- --------- ------------
     11111          6       4455 20-MAR-07            5
     11111          6       4455 30-MAR-07            2
     11111          6       4455                      3

Re: New Row in Table [message #225496 is a reply to message #224368] Tue, 20 March 2007 02:16 Go to previous message
milford_f
Messages: 18
Registered: July 2006
Junior Member
Hey Frank,

First of all, apologies for not writing back earlier.

Your solution worked, I was able to create my table just as i wanted it. Thanks a million.

Cheers
Milford.
Previous Topic: Generate a report using PL/SQL
Next Topic: sql help
Goto Forum:
  


Current Time: Wed Dec 07 16:24:30 CST 2016

Total time taken to generate the page: 0.09269 seconds