Home » SQL & PL/SQL » SQL & PL/SQL » Critical Query
Critical Query [message #258943] Mon, 13 August 2007 18:45 Go to next message
rameshbabupallapolu
Messages: 18
Registered: September 2005
Location: WA
Junior Member
Hi,

I have one staging table having three columns like this

Invoice_num, amount, check_number.

Each check number having more then one invoice number, now i want to populate the data like, for each check_number , for row maximum 8 invoices and corresponging amount columns with check number, if more than 8 it has to popluate next row with 9th invoice number etc.

How is there anyway to write a query like this.




Re: Critical Query [message #258973 is a reply to message #258943] Tue, 14 August 2007 00:02 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Do you mean you want to concatenate multiple invoice-numbers into a single column?
That would be a bad idea, since you would not be able to get the individual invoice numbers from it, without going through a hell of a lot of trouble.
But maybe I misunderstood you, in which case you might want to elaborate your question with an example record.
Re: Critical Query [message #258984 is a reply to message #258943] Tue, 14 August 2007 00:57 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Normalize your model.
1 row, 1 invoice and vice versa.

Regards
Michel
Re: Critical Query [message #259230 is a reply to message #258973] Tue, 14 August 2007 15:24 Go to previous messageGo to next message
rameshbabupallapolu
Messages: 18
Registered: September 2005
Location: WA
Junior Member
Hi,

Actually, my requirement is

Invoice_number Amout Check_num
------------ ----- ---------
xx1 100 123456
xx2 150 123456
xx3 160 123456
xx4 120 123456
xx5 110 123456
xx6 130 123456
xx7 120 123456
xx8 110 123456
xx9 100 123456
xx10 120 123456

I want to display like this

xx1 100 xx2 150 xx3 160 xx4 120 xx5 110 xx6 130 xx7 120 xx8 110
xx9 100 xx10 120

exc..

each row maximun 8 rows for one check number.

Re: Critical Query [message #259330 is a reply to message #259230] Wed, 15 August 2007 01:23 Go to previous message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> select * from t order by 1;
INVOICE_NUMBER     AMOUNT
-------------- ----------
           901        110
           902        120
           903        130
           904        140
           905        150
           906        160
           907        170
           908        180
           909        190
           910        200

10 rows selected.

SQL> with 
  2    data as (
  3      select row_number () over (order by invoice_number) - 1 rn,
  4             invoice_number, amount 
  5      from t
  6    )
  7  select max(decode(mod(rn,8),0,invoice_number)) col1,
  8         max(decode(mod(rn,8),0,amount)) col2,
  9         max(decode(mod(rn,8),1,invoice_number)) col3,
 10         max(decode(mod(rn,8),1,amount)) col4,
 11         max(decode(mod(rn,8),2,invoice_number)) col5,
 12         max(decode(mod(rn,8),2,amount)) col6,
 13         max(decode(mod(rn,8),3,invoice_number)) col7,
 14         max(decode(mod(rn,8),3,amount)) col8
 15  from data
 16  group by trunc(rn/8)
 17  order by trunc(rn/8)
 18  /
      COL1       COL2       COL3       COL4       COL5       COL6       COL7       COL8
---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
       901        110        902        120        903        130        904        140
       909        190        910        200

2 rows selected.

Regards
Michel
Previous Topic: Find names containing letters (Merged)
Next Topic: Error(80,12): PLS-00306: wrong number or types of arguments in call to 'PUT_LINE'
Goto Forum:
  


Current Time: Wed Dec 07 08:31:40 CST 2016

Total time taken to generate the page: 0.21041 seconds