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  |
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 #326669 is a reply to message #326657] |
Thu, 12 June 2008 03:55   |
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   |
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 #326946 is a reply to message #326917] |
Fri, 13 June 2008 02:56   |
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   |
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 #326955 is a reply to message #326952] |
Fri, 13 June 2008 03:23  |
 |
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
|
|
|
Goto Forum:
Current Time: Sat Feb 08 16:48:34 CST 2025
|