asked on

Formatted output

Dear Expert friends,

I am stuck with very important financial report. I have below query with attached actual output and required output

Request alternate way of getting the output as per desired format

/* Formatted on 03/05/2011 4:20:44 PM (QP5 v5.136.908.31019) */
SELECT TAX.ABBREVIATION, SUM (TAXDETAIL.TAXAMOUNT) AS TAX, LEDGER.DESCRIPTION
FROM POINTOFSALEBILL
INNER JOIN POINTOFSALEBILLDETAIL
ON POINTOFSALEBILL.POINTOFSALEBILL_ID =
POINTOFSALEBILLDETAIL.POINTOFSALEBILL_ID
INNER JOIN TAXDETAIL
ON POINTOFSALEBILL.POINTOFSALEBILL_ID =
TAXDETAIL.POINTOFSALEBILL_ID
AND POINTOFSALEBILLDETAIL.POINTOFSALEBILLDETAIL_ID =
TAXDETAIL.POINTOFSALEBILLDETAIL_ID
INNER JOIN TAX
ON TAX.TAX_ID = TAXDETAIL.TAX_ID
INNER JOIN LEDGER
ON LEDGER.LEDGER_ID = POINTOFSALEBILLDETAIL.LEDGER_ID
GROUP BY POINTOFSALEBILL.BILLAMOUNT,
TAX.ABBREVIATION,
TAXDETAIL.TAXAMOUNT,
LEDGER.DESCRIPTION
Pivot.xls

Naveen Kumar

try this :

select ledger.description,
sum(decode(tax.abbreviation,'ST',tax,0)) ST,
sum(decode(tax.abbreviation,'VAT5',tax,0)) VAT5,
sum(decode(tax.abbreviation,'A',tax,0)) A
from (
SELECT TAX.ABBREVIATION, SUM (TAXDETAIL.TAXAMOUNT) AS TAX, LEDGER.DESCRIPTION
FROM POINTOFSALEBILL
INNER JOIN POINTOFSALEBILLDETAIL
ON POINTOFSALEBILL.POINTOFSALEBILL_ID =
POINTOFSALEBILLDETAIL.POINTOFSALEBILL_ID
INNER JOIN TAXDETAIL
ON POINTOFSALEBILL.POINTOFSALEBILL_ID =
TAXDETAIL.POINTOFSALEBILL_ID
AND POINTOFSALEBILLDETAIL.POINTOFSALEBILLDETAIL_ID =
TAXDETAIL.POINTOFSALEBILLDETAIL_ID
INNER JOIN TAX
ON TAX.TAX_ID = TAXDETAIL.TAX_ID
INNER JOIN LEDGER
ON LEDGER.LEDGER_ID = POINTOFSALEBILLDETAIL.LEDGER_ID
GROUP BY POINTOFSALEBILL.BILLAMOUNT,
TAX.ABBREVIATION,
TAXDETAIL.TAXAMOUNT,
LEDGER.DESCRIPTION
)
group by ledger.description

GRChandrashekar

ASKER

@nav_kum_v:
Well the whole of problem arises here...I do not know the abbreviations like 'VAT5' since it comes from DB values.

I dont mind hardcoding 100 Columns for the purpose, but not sure how to go about

Naveen Kumar

run this query to get all the distinct codes from the database and hard code them accordingly in my query for a quick and easy thing though it is not the perfect way. You need to use dynamic sql for this kind of requirement.

SELECT distinct TAX.ABBREVIATION
FROM POINTOFSALEBILL
INNER JOIN POINTOFSALEBILLDETAIL
ON POINTOFSALEBILL.POINTOFSALEBILL_ID =
POINTOFSALEBILLDETAIL.POINTOFSALEBILL_ID
INNER JOIN TAXDETAIL
ON POINTOFSALEBILL.POINTOFSALEBILL_ID =
TAXDETAIL.POINTOFSALEBILL_ID
AND POINTOFSALEBILLDETAIL.POINTOFSALEBILLDETAIL_ID =
TAXDETAIL.POINTOFSALEBILLDETAIL_ID
INNER JOIN TAX
ON TAX.TAX_ID = TAXDETAIL.TAX_ID
INNER JOIN LEDGER
ON LEDGER.LEDGER_ID = POINTOFSALEBILLDETAIL.LEDGER_ID
GROUP BY POINTOFSALEBILL.BILLAMOUNT,
TAX.ABBREVIATION,
TAXDETAIL.TAXAMOUNT,
LEDGER.DESCRIPTION

GRChandrashekar

ASKER

Well getting SELECT distinct TAX.ABBREVIATION is not a issue. Problem is since it pertains to financial data, tax.abbreviation keeps on adding and i have to alter the query every time tax.abbreviation is added to tax table !

GRChandrashekar

ASKER

You need to use dynamic sql for this kind of requirement.

How can i do this

GRChandrashekar

ASKER

OR, is there a way to write a function and pass to SP with loop or stuff like though am not sure of actual way to be done

Naveen Kumar

yes, i can understand.

The only ideal solution is to first execute the distinct tax abbreviation query first to get all the codes in the code and then dynamically prepare the final query (on same lines using sum(decode(..)) ) using dynamic sql to use all the different codes and then execute it.

If you are on 11g, then you can try using pivot/unpivot

Naveen Kumar

if you want to do it with pl/sql loop / function / procedure etc, then you can do it as you are saying.

1) Have a loop to traverse all the different codes
2) for each code execute the sql and take the results ( but you will not be able to get it in the same format as you need )
3) So you may have to use utl_file package etc to write to file in the format you need.

Thanks

GRChandrashekar

ASKER

I am on 9i

pl/sql loop / function / procedure etc is ok

ELSE

for each code execute the sql and take the results ( but you will not be able to get it in the same format as you need )
3) So you may have to use utl_file package etc to write to file in the format you need.

This also fine. But not able to get it right. can you please help me

GRChandrashekar

ASKER

Preferrably pl/sql loop / function / procedure etc is ok

Naveen Kumar

it is not an easy thing for me to come up with that kind of code because of time issues. But yes, if you can start to come up with that kind of thing, i can help to oversee any issues if you have as and when required.

GRChandrashekar

ASKER

I shall do... atleast a hint to start ..?

GRChandrashekar

ASKER

I meant alteast the steps that a Proc/function/sql should do

ASKER CERTIFIED SOLUTION

Naveen Kumar

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial