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
GRChandrashekarAsked:
Who is Participating?
 
Naveen KumarConnect With a Mentor Production Manager / Application Support ManagerCommented:
these are the basics to start with :

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.


so for 1), have a procedure which has a loop as shown below.

for x in (  SELECT distinct TAX.ABBREVIATION myvalue
    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
order by 1
)
loop
-- for each and every iteration of the loop, the below has to get executed and then store the results in table or write to file or store in array. etc

select ledger.description,
sum(decode(tax.abbreviation,x.myvalue,tax,0)) col1
into some_var1, some_var2
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;
end loop;
0
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
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
   
0
 
GRChandrashekarAuthor Commented:
@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
0
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

 
Naveen KumarProduction Manager / Application Support ManagerCommented:
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
0
 
GRChandrashekarAuthor Commented:
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 !
0
 
GRChandrashekarAuthor Commented:
You need to use dynamic sql for this kind of requirement.

How can i do this
0
 
GRChandrashekarAuthor Commented:
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
0
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
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
0
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
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
0
 
GRChandrashekarAuthor Commented:
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
0
 
GRChandrashekarAuthor Commented:
Preferrably pl/sql loop / function / procedure etc is ok
0
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
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.
0
 
GRChandrashekarAuthor Commented:
I shall do... atleast a hint to start ..?
0
 
GRChandrashekarAuthor Commented:
I meant alteast the steps that a Proc/function/sql should do
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.