Home » SQL & PL/SQL » SQL & PL/SQL » an interesting sql query with difficult o/p (oracle 10g r2)
an interesting sql query with difficult o/p [message #326657] Thu, 12 June 2008 03:20 Go to next message
p.bhaskar
Messages: 33
Registered: February 2007
Location: kolkata
Member
Hi i have the data set like this -->


with t as
(
select 1 as invoice,5000 as tot_amount,'1/1/2008' as pay_date,100 as payment from dual
union
select 1 ,5000 ,'5/1/2008' ,200 from dual
union
select 2 ,5000 ,'10/1/2008' ,400 from dual
union
select 2 ,5000 ,'15/1/2008' ,500 from dual
union
select 3 ,5000 ,'19/1/2008' ,600 from dual
)
select
invoice,tot_amount,
max(decode(extract(day from to_date(pay_date,'dd/mm/yyyy')),1,payment))day_1,
max(decode(extract(day from to_date(pay_date,'dd/mm/yyyy')),5,payment))day_5,
max(decode(extract(day from to_date(pay_date,'dd/mm/yyyy')),10,payment))day_10,
max(decode(extract(day from to_date(pay_date,'dd/mm/yyyy')),15,payment))day_15,
max(decode(extract(day from to_date(pay_date,'dd/mm/yyyy')),19,payment))day_19
from t
group by
invoice,tot_amount
order by 1

it's o/p is


------------------------------------------------------------------
|INVOICE|TOT_AMOUNT |1/2008 |5/2008 |10/2008|15/2008|19/2008 |
|-------|---------------|-------|-------|-------|-------|--------|
|1 |5000 |100 |200 | | | |
|2 |5000 | | |400 |500 | |
|3 |5000 | | | | | 600 |
------------------------------------------------------------------

Now how i can replace the header like day_1 with it's corresponding month/year format?
------------------------------------------------------------------
|INVOICE|TOT_AMOUNT |DAY_1 |DAY_5 |DAY_10 |DAY_15 |DAY_19 |
|-------|---------------|-------|-------|-------|-------|--------|
|1 |5000 |100 |200 | | | |
|2 |5000 | | |400 |500 | |
|3 |5000 | | | | | 600 |
------------------------------------------------------------------

Thanks in advance
  • Attachment: output.txt
    (Size: 0.76KB, Downloaded 524 times)
Re: an interesting sql query with difficult o/p [message #326663 is a reply to message #326657] Thu, 12 June 2008 03:37 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
Check SQL*Plus command

column <column_name> Heading
Re: an interesting sql query with difficult o/p [message #326664 is a reply to message #326663] Thu, 12 June 2008 03:41 Go to previous messageGo to next message
p.bhaskar
Messages: 33
Registered: February 2007
Location: kolkata
Member
Actually i have to show this data with this formatted heading in a report so sql* plus i can not use i have to do it in a single sql statement.
Re: an interesting sql query with difficult o/p [message #326665 is a reply to message #326664] Thu, 12 June 2008 03:43 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
What Report are you using to display this data??
Re: an interesting sql query with difficult o/p [message #326667 is a reply to message #326665] Thu, 12 June 2008 03:48 Go to previous messageGo to next message
p.bhaskar
Messages: 33
Registered: February 2007
Location: kolkata
Member
Crystal report
Re: an interesting sql query with difficult o/p [message #326669 is a reply to message #326657] Thu, 12 June 2008 03:55 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
You are dragging these fields on The Data Section??

When you drag the columns from the Sql Statement then header section displays the column name by default.

you have to get the date field also with your result.and then you can create a custom function in crystal report to make your header section out of this date field.
Re: an interesting sql query with difficult o/p [message #326671 is a reply to message #326669] Thu, 12 June 2008 04:02 Go to previous messageGo to next message
p.bhaskar
Messages: 33
Registered: February 2007
Location: kolkata
Member
Hi

I know this default header comes of corresponding data field.

But say i have this columns fixed

INVOICE TOT_AMOUNT DAY_1 DAY_5 DAY_10 DAY_15

Now it's ok for INVOICE TOT_AMOUNT but for

DAY_1 DAY_5 DAY_10 DAY_15 how i will write function that will automatically fetch the corresponding month/year?

Re: an interesting sql query with difficult o/p [message #326683 is a reply to message #326671] Thu, 12 June 2008 04:42 Go to previous messageGo to next message
Frank
Messages: 7901
Registered: March 2000
Senior Member
You swapped your output and your desired output in the original (first) post.
That causes it to be a totally different question!
You want dynamic column headers, instead of standard aliases
Re: an interesting sql query with difficult o/p [message #326686 is a reply to message #326683] Thu, 12 June 2008 04:46 Go to previous messageGo to next message
p.bhaskar
Messages: 33
Registered: February 2007
Location: kolkata
Member
sorry for that


"You want dynamic column headers, instead of standard aliases"

yes but how i can achieve it?
Re: an interesting sql query with difficult o/p [message #326687 is a reply to message #326686] Thu, 12 June 2008 05:01 Go to previous messageGo to next message
Frank
Messages: 7901
Registered: March 2000
Senior Member
Not possible in a single query.
Re: an interesting sql query with difficult o/p [message #326691 is a reply to message #326686] Thu, 12 June 2008 05:18 Go to previous messageGo to next message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
I posted several times a couple of solutions for this type of question.
Search them.

Regards
Michel
Re: an interesting sql query with difficult o/p [message #326703 is a reply to message #326686] Thu, 12 June 2008 05:53 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Check this link.

http://asktom.oracle.com/pls/asktom/f?p=100:11:706335815984809::::P11_QUESTION_ID:52266643928180

And search in this post for the solution (one of the ingenious idea) given by Michael Cadot dated November 27, 2005 - 2pm .

Regards

Raj


Re: an interesting sql query with difficult o/p [message #326738 is a reply to message #326703] Thu, 12 June 2008 08:26 Go to previous messageGo to next message
Frank
Messages: 7901
Registered: March 2000
Senior Member
I stand corrected (partially)
The example you referred to does a nice job, it will be sufficient for most purposes.
However, since it's not a real header, it will not repeat after a page break, or the like.
Re: an interesting sql query with difficult o/p [message #326739 is a reply to message #326738] Thu, 12 June 2008 08:29 Go to previous messageGo to next message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Yes, in another thread on AskTom on the kind of question, I said I cheated. Smile
Another way I posted for SQL*Plus is to first search for the column names and then query the values.

Regards
Michel
Re: an interesting sql query with difficult o/p [message #326740 is a reply to message #326739] Thu, 12 June 2008 08:30 Go to previous messageGo to next message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Dynamic Report Headings

Regards
Michel
Re: an interesting sql query with difficult o/p [message #326749 is a reply to message #326740] Thu, 12 June 2008 08:40 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
I tried to search for this post only because I remember seeing your post there but i searched for the keyword "dynamic column headings" Smile

thanks for posting the links.

Regards

Raj
Re: an interesting sql query with difficult o/p [message #326887 is a reply to message #326686] Thu, 12 June 2008 23:52 Go to previous messageGo to next message
p.bhaskar
Messages: 33
Registered: February 2007
Location: kolkata
Member
thanx all for reply and valuable links.

It works fine for me.
Re: an interesting sql query with difficult o/p [message #326889 is a reply to message #326887] Thu, 12 June 2008 23:59 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
P.Bhasker can you please post the results.

Regards,
Rajat Ratewal
Re: an interesting sql query with difficult o/p [message #326914 is a reply to message #326740] Fri, 13 June 2008 01:23 Go to previous messageGo to next message
p.bhaskar
Messages: 33
Registered: February 2007
Location: kolkata
Member
Your solution there really ends my try sir .

An intelligent and tricky solution.

Thanks a lot
Re: an interesting sql query with difficult o/p [message #326917 is a reply to message #326914] Fri, 13 June 2008 01:48 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
P.Bhasker can you please post the results.

I am very keen to see what you have tried.I am getting
Ora-32033 unsupported column aliasing in with clause because of Pay_Date.

Regards,
Rajat Ratewal
Re: an interesting sql query with difficult o/p [message #326928 is a reply to message #326917] Fri, 13 June 2008 02:23 Go to previous messageGo to next message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
If you don't post your query and table description we can't help you find what is wrong in it.

Regards
Michel
Re: an interesting sql query with difficult o/p [message #326946 is a reply to message #326917] Fri, 13 June 2008 02:56 Go to previous messageGo to next message
p.bhaskar
Messages: 33
Registered: February 2007
Location: kolkata
Member
hi
sorry for delay

send your code where u getting problem.

see i have did it with my code where i needed this --->

i HVE ATTACHED ALL THIS IN ATTACHED SEE IT.

with ta as
(
select
distinct
m.billing_month,m.invoice_no,m.invoice_date,m.bill_amount,k.payment_id,
round(sum(n.actual_rebate_amount)over(partition by n.invoice_no))rebate,
m.customer_id,k.actual_date_of_payment,
extract(month from to_date(k.actual_date_of_payment,'dd/mm/yyyy'))||'/'||extract(year from to_date(k.actual_date_of_payment,'dd/mm/yyyy')) month_year,
round(sum(t.curr_amount)over(partition by m.invoice_no,m.billing_month))payment,
round((m.bill_amount-(sum(n.actual_rebate_amount)over(partition by n.invoice_no)+sum(t.curr_amount)over(partition by m.invoice_no,m.billing_month))))outstanding
from
intercompany_transaction_qry t,INTER_PAYMENT_UNION k,SALE_ENRG_BILL_HDR M
,REBATE_INFO n
where m.billing_month between &from_month and &to_month
and m.customer_id =&benefeciary_
and k.payment_id=t.payment_id
and t.rolledback<>'TRUE'
and M.invoice_no=t.ledger_item_id
and k.payment_type_code_db <>'CUSTOFF'
and n.invoice_no=m.invoice_no
order by 1,2
)
select
1,
'Benefeciary' as h1,
'Bill_Period' as h2,
'Bill_No' as h3,
'Bill_Date' as h4,
'Bill_Amount' as h5,
max(decode(
(extract (month from to_date(actual_date_of_payment,'dd/mm/yyyy'))),1,
(extract (month from to_date(actual_date_of_payment,'dd/mm/yyyy')))||'/'||
(extract (year from to_date(actual_date_of_payment,'dd/mm/yyyy')))
))month_1 ,
max(decode(
(extract (month from to_date(actual_date_of_payment,'dd/mm/yyyy'))),2,
(extract (month from to_date(actual_date_of_payment,'dd/mm/yyyy')))||'/'||
(extract (year from to_date(actual_date_of_payment,'dd/mm/yyyy')))
))month_2 ,
max(decode(
(extract (month from to_date(actual_date_of_payment,'dd/mm/yyyy'))),3,
(extract (month from to_date(actual_date_of_payment,'dd/mm/yyyy')))||'/'||
(extract (year from to_date(actual_date_of_payment,'dd/mm/yyyy')))
))month_3 ,
max(decode(
(extract (month from to_date(actual_date_of_payment,'dd/mm/yyyy'))),4,
(extract (month from to_date(actual_date_of_payment,'dd/mm/yyyy')))||'/'||
(extract (year from to_date(actual_date_of_payment,'dd/mm/yyyy')))
))month_4 ,
max(decode(
(extract (month from to_date(actual_date_of_payment,'dd/mm/yyyy'))),5,
(extract (month from to_date(actual_date_of_payment,'dd/mm/yyyy')))||'/'||
(extract (year from to_date(actual_date_of_payment,'dd/mm/yyyy')))
))month_5,
max(decode(
(extract (month from to_date(actual_date_of_payment,'dd/mm/yyyy'))),6,
(extract (month from to_date(actual_date_of_payment,'dd/mm/yyyy')))||'/'||
(extract (year from to_date(actual_date_of_payment,'dd/mm/yyyy')))
))month_6 ,
max(decode(
(extract (month from to_date(actual_date_of_payment,'dd/mm/yyyy'))),7,
(extract (month from to_date(actual_date_of_payment,'dd/mm/yyyy')))||'/'||
(extract (year from to_date(actual_date_of_payment,'dd/mm/yyyy')))
))month_7 ,
max(decode(
(extract (month from to_date(actual_date_of_payment,'dd/mm/yyyy'))),8,
(extract (month from to_date(actual_date_of_payment,'dd/mm/yyyy')))||'/'||
(extract (year from to_date(actual_date_of_payment,'dd/mm/yyyy')))
))month_8 ,
max(decode(
(extract (month from to_date(actual_date_of_payment,'dd/mm/yyyy'))),9,
(extract (month from to_date(actual_date_of_payment,'dd/mm/yyyy')))||'/'||
(extract (year from to_date(actual_date_of_payment,'dd/mm/yyyy')))
))month_9 ,
max(decode(
(extract (month from to_date(actual_date_of_payment,'dd/mm/yyyy'))),10,
(extract (month from to_date(actual_date_of_payment,'dd/mm/yyyy')))||'/'||
(extract (year from to_date(actual_date_of_payment,'dd/mm/yyyy')))
))month_10 ,
max(decode(
(extract (month from to_date(actual_date_of_payment,'dd/mm/yyyy'))),11,
(extract (month from to_date(actual_date_of_payment,'dd/mm/yyyy')))||'/'||
(extract (year from to_date(actual_date_of_payment,'dd/mm/yyyy')))
))month_11 ,
max(decode(
(extract (month from to_date(actual_date_of_payment,'dd/mm/yyyy'))),12,
(extract (month from to_date(actual_date_of_payment,'dd/mm/yyyy')))||'/'||
(extract (year from to_date(actual_date_of_payment,'dd/mm/yyyy')))
))month_12 ,
'Payment' as h6
from ta
group by customer_id
union
select
2,
customer_id,
cast(billing_month as varchar2(10)),
cast(invoice_no as varchar2(10)),
cast(invoice_date as varchar2(10)),
cast(bill_amount as varchar2(10)),
nvl(cast(max(decode(extract(month from to_date(actual_date_of_payment,'dd/mm/yyyy')),1,payment))as varchar2(10)),0)month_1,
nvl(cast(max(decode(extract(month from to_date(actual_date_of_payment,'dd/mm/yyyy')),2,payment))as varchar2(10)),0)month_2,
nvl(cast(max(decode(extract(month from to_date(actual_date_of_payment,'dd/mm/yyyy')),3,payment))as varchar2(10)),0)month_3,
nvl(cast(max(decode(extract(month from to_date(actual_date_of_payment,'dd/mm/yyyy')),4,payment))as varchar2(10)),0)month_4,
nvl(cast(max(decode(extract(month from to_date(actual_date_of_payment,'dd/mm/yyyy')),5,payment))as varchar2(10)),0)month_5,
nvl(cast(max(decode(extract(month from to_date(actual_date_of_payment,'dd/mm/yyyy')),6,payment))as varchar2(10)),0)month_6,
nvl(cast(max(decode(extract(month from to_date(actual_date_of_payment,'dd/mm/yyyy')),7,payment))as varchar2(10)),0)month_7,
nvl(cast(max(decode(extract(month from to_date(actual_date_of_payment,'dd/mm/yyyy')),8,payment))as varchar2(10)),0)month_8,
nvl(cast(max(decode(extract(month from to_date(actual_date_of_payment,'dd/mm/yyyy')),9,payment))as varchar2(10)),0)month_9,
nvl(cast(max(decode(extract(month from to_date(actual_date_of_payment,'dd/mm/yyyy')),10,payment))as varchar2(10)),0)month_10,
nvl(cast(max(decode(extract(month from to_date(actual_date_of_payment,'dd/mm/yyyy')),11,payment))as varchar2(10)),0)month_11,
nvl(cast(max(decode(extract(month from to_date(actual_date_of_payment,'dd/mm/yyyy')),12,payment))as varchar2(10)),0)month_12,
cast(payment as varchar2(10))
from ta
group by customer_id,billing_month,invoice_no,invoice_date,bill_amount,payment


and my result is like this ---->

1 1 Benefeciary Bill_Period Bill_No Bill_Date Bill_Amount 3/8 4/8 Payment
2 2 B100022 200802 NH/CHAMERA 05-MAR-08 1585968 0 0 1558409 0 0 0 0 0 0 0 0 0 1558409
3 2 B100022 200802 NH/CHAMERA 05-MAR-08 3661942 0 0 3590640 0 0 0 0 0 0 0 0 0 3590640
4 2 B100022 200802 NH/DHAULIG 05-MAR-08 1923464 0 0 1876244 0 0 0 0 0 0 0 0 0 1876244
5 2 B100022 200802 NH/DULHAST 05-MAR-08 6496414 0 0 6319094 0 0 0 0 0 0 0 0 0 6319094
6 2 B100022 200802 NH/SALAL/U 05-MAR-08 815457 0 0 800249 0 0 0 0 0 0 0 0 0 800249
7 2 B100022 200802 NH/TANAKPU 05-MAR-08 -448038 0 0 -444540 0 0 0 0 0 0 0 0 0 -444540
8 2 B100022 200802 NH/URI/UPC 05-MAR-08 6902945 0 0 6773155 0 0 0 0 0 0 0 0 0 6773155
9 2 B100022 200803 NH/CHAMERA 05-APR-08 2321042 0 0 0 2276096 0 0 0 0 0 0 0 0 2276096
10 2 B100022 200803 NH/CHAMERA 05-APR-08 5555594 0 0 0 5452393 0 0 0 0 0 0 0 0 5452393
11 2 B100022 200803 NH/DHAULIG 05-APR-08 2908058 0 0 0 2849567 0 0 0 0 0 0 0 0 2849567
12 2 B100022 200803 NH/DULHAST 05-APR-08 11942590 0 0 0 11701387 0 0 0 0 0 0 0 0 11701387
13 2 B100022 200803 NH/SALAL/U 05-APR-08 1476085 0 0 0 1447996 0 0 0 0 0 0 0 0 1447996
14 2 B100022 200803 NH/TANAKPU 05-APR-08 -399920 0 0 0 -392510 0 0 0 0 0 0 0 0 -392510
15 2 B100022 200803 NH/URI/UPC 05-APR-08 13577875 0 0 0 13322530 0 0 0 0 0 0 0 0 13322530
  • Attachment: output.txt
    (Size: 1.73KB, Downloaded 527 times)
Re: an interesting sql query with difficult o/p [message #326948 is a reply to message #326928] Fri, 13 June 2008 02:59 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
Dear Michel,

Sorry i was missing something.I corrected it and it's working fine now.

I tried the approach that you have provided at
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:595725000346503967.
and applied it on The original problem.

The one problem that i am getting is of formating the output.
Please check the query for further details.
(Check val column value in it)

Please find the details.

CREATE TABLE TEST
(
  INVOICE       NUMBER,
  AMOUNT        NUMBER,
  PAY_DATE      DATE,
  PAYMENT       NUMBER,
  HEADERFORMAT  VARCHAR2(20 BYTE),
  DAY           NUMBER
);


INSERT INTO TEST ( INVOICE, AMOUNT, PAY_DATE, PAYMENT, HEADERFORMAT,
DAY ) VALUES ( 
1, 5000,  TO_Date( '01/01/2008 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'), 100, '01/2008'
, 1); 
INSERT INTO TEST ( INVOICE, AMOUNT, PAY_DATE, PAYMENT, HEADERFORMAT,
DAY ) VALUES ( 
1, 5000,  TO_Date( '01/05/2008 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'), 200, '05/2008'
, 5); 
INSERT INTO TEST ( INVOICE, AMOUNT, PAY_DATE, PAYMENT, HEADERFORMAT,
DAY ) VALUES ( 
2, 5000,  TO_Date( '01/10/2008 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'), 400, '10/2008'
, 10); 
INSERT INTO TEST ( INVOICE, AMOUNT, PAY_DATE, PAYMENT, HEADERFORMAT,
DAY ) VALUES ( 
2, 5000,  TO_Date( '01/15/2008 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'), 500, '15/2008'
, 15); 
INSERT INTO TEST ( INVOICE, AMOUNT, PAY_DATE, PAYMENT, HEADERFORMAT,
DAY ) VALUES ( 
3, 5000,  TO_Date( '01/19/2008 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'), 600, '19/2008'
, 19); 
INSERT INTO TEST ( INVOICE, AMOUNT, PAY_DATE, PAYMENT, HEADERFORMAT,
DAY ) VALUES ( 
1, 5000,  TO_Date( '01/05/2008 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'), 400, '05/2008'
, 5); 
COMMIT;

SELECT 
   'Invoice' AS Invoice, 
    TRANSLATE(SUBSTR(sys_connect_by_path(LPAD 
    payDate,7),'|'),2),'|',' ') val
FROM 
   (SELECT DISTINCT 
         HeaderFormat payDate,
         dense_rank () over (ORDER BY HeaderFormat) rk,
    COUNT(DISTINCT HeaderFormat) over () cnt FROM TEST)
 WHERE rk = cnt
 CONNECT BY PRIOR rk = rk - 1
 START WITH rk = 1
 UNION ALL
   SELECT 
         TO_CHAR(invoice) invoice,
      	  TRANSLATE(SUBSTR(sys_connect_by_path(LPAD(val,7),'/'),2),'/',' ') val
   FROM 
   (
 	SELECT 
	a.invoice, 
	b.rk, 
        b.cnt,
	NVL((SELECT SUM(payment) FROM TEST c   
             WHERE c.invoice = a.invoice
               AND c.HeaderFormat = b.payDate) ,0) val
   FROM 
	TEST a, 
	(SELECT DISTINCT 
              HeaderFormat payDate,
              dense_rank () over (ORDER BY HeaderFormat) rk,
              COUNT(DISTINCT HeaderFormat) over () cnt FROM  
               TEST) b
GROUP BY 
	a.invoice, b.payDate, b.rk, b.cnt
 	)
   WHERE rk = cnt
   CONNECT BY PRIOR rk = rk - 1 AND PRIOR invoice = invoice
   START WITH rk = 1

<Output Not Formatted>
INVOICE	VAL
Invoice	01/2008 05/2008 10/2008 15/2008 19/2008
1	100  600       0       0       0
2	  0       0  400     500       0
3	  0       0 0       0     600


As we are using LPAD to format it but we don't know the number of digits in payment column.

Regards,
Rajat

[Updated on: Fri, 13 June 2008 03:17] by Moderator

Report message to a moderator

Re: an interesting sql query with difficult o/p [message #326951 is a reply to message #326948] Fri, 13 June 2008 03:01 Go to previous messageGo to next message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Quote:
As we are using LPAD to format it but we don't know the number of digits in payment column.

But you know the maximum number of them.

Regards
Michel
Re: an interesting sql query with difficult o/p [message #326952 is a reply to message #326951] Fri, 13 June 2008 03:06 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
Yes you are right Michel.That's why you have provided
TO_CHAR(val,'99') in formatting it at asktom.

But i applied that also and still i am not getting the desired
output.


Regards,
Rajat
Re: an interesting sql query with difficult o/p [message #326955 is a reply to message #326952] Fri, 13 June 2008 03:23 Go to previous message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
I tried your query and got (thanks for providing the test case):
SQL> SELECT 
  2     'Invoice' AS Invoice, 
  3      TRANSLATE(SUBSTR(sys_connect_by_path(LPAD 
  4      payDate,7),'|'),2),'|',' ') val
  5  FROM 
  6     (SELECT DISTINCT 
  7           HeaderFormat payDate,
  8           dense_rank () over (ORDER BY HeaderFormat) rk,
  9      COUNT(DISTINCT HeaderFormat) over () cnt FROM TEST)
 10   WHERE rk = cnt
 11   CONNECT BY PRIOR rk = rk - 1
 12   START WITH rk = 1
 13   UNION ALL
 14     SELECT 
 15           TO_CHAR(invoice) invoice,
 16           TRANSLATE(SUBSTR(sys_connect_by_path(LPAD(val,7),'/'),2),'/',' ') val
 17     FROM 
 18     (
 19    SELECT 
 20   a.invoice, 
 21   b.rk, 
 22          b.cnt,
 23   NVL((SELECT SUM(payment) FROM TEST c   
 24               WHERE c.invoice = a.invoice
 25                 AND c.HeaderFormat = b.payDate) ,0) val
 26     FROM 
 27   TEST a, 
 28   (SELECT DISTINCT 
 29                HeaderFormat payDate,
 30                dense_rank () over (ORDER BY HeaderFormat) rk,
 31                COUNT(DISTINCT HeaderFormat) over () cnt FROM  
 32                 TEST) b
 33  GROUP BY 
 34   a.invoice, b.payDate, b.rk, b.cnt
 35    )
 36     WHERE rk = cnt
 37     CONNECT BY PRIOR rk = rk - 1 AND PRIOR invoice = invoice
 38     START WITH rk = 1
 39  /
    TRANSLATE(SUBSTR(sys_connect_by_path(LPAD
                     *
ERROR at line 3:
ORA-00909: invalid number of arguments

After fixing:
SQL> col invoice format a7
SQL> col val format a50
SQL> set head off
SQL> SELECT 
  2     'Invoice' AS Invoice, 
  3      TRANSLATE(SUBSTR(sys_connect_by_path(LPAD(payDate,7),'|'),2),'|',' ') val
  4  FROM 
  5     (SELECT DISTINCT 
  6           HeaderFormat payDate,
  7           dense_rank () over (ORDER BY HeaderFormat) rk,
  8      COUNT(DISTINCT HeaderFormat) over () cnt FROM TEST)
  9   WHERE rk = cnt
 10   CONNECT BY PRIOR rk = rk - 1
 11   START WITH rk = 1
 12   UNION ALL
 13     SELECT 
 14           TO_CHAR(invoice) invoice,
 15           TRANSLATE(SUBSTR(sys_connect_by_path(LPAD(val,7),'/'),2),'/',' ') val
 16     FROM 
 17     (
 18    SELECT 
 19   a.invoice, 
 20   b.rk, 
 21          b.cnt,
 22   NVL((SELECT SUM(payment) FROM TEST c   
 23               WHERE c.invoice = a.invoice
 24                 AND c.HeaderFormat = b.payDate) ,0) val
 25     FROM 
 26   TEST a, 
 27   (SELECT DISTINCT 
 28                HeaderFormat payDate,
 29                dense_rank () over (ORDER BY HeaderFormat) rk,
 30                COUNT(DISTINCT HeaderFormat) over () cnt FROM  
 31                 TEST) b
 32  GROUP BY 
 33   a.invoice, b.payDate, b.rk, b.cnt
 34    )
 35     WHERE rk = cnt
 36     CONNECT BY PRIOR rk = rk - 1 AND PRIOR invoice = invoice
 37     START WITH rk = 1
 38  /
Invoice 01/2008 05/2008 10/2008 15/2008 19/2008
1           100     600       0       0       0
2             0       0     400     500       0
3             0       0       0       0     600

4 rows selected.

It seems fine for me.

Regards
Michel
Previous Topic: decode script
Next Topic: about sql queries
Goto Forum:
  


Current Time: Sat Feb 08 16:48:34 CST 2025