?
Solved

Formatted output

Posted on 2011-05-03
14
Medium Priority
?
247 Views
Last Modified: 2012-05-11
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
0
Comment
Question by:GRChandrashekar
  • 8
  • 6
14 Comments
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 35511856
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
 

Author Comment

by:GRChandrashekar
ID: 35511870
@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
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 35511907
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:GRChandrashekar
ID: 35511916
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
 

Author Comment

by:GRChandrashekar
ID: 35511918
You need to use dynamic sql for this kind of requirement.

How can i do this
0
 

Author Comment

by:GRChandrashekar
ID: 35511927
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
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 35511934
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
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 35511940
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
 

Author Comment

by:GRChandrashekar
ID: 35512054
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
 

Author Comment

by:GRChandrashekar
ID: 35512073
Preferrably pl/sql loop / function / procedure etc is ok
0
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 35512411
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
 

Author Comment

by:GRChandrashekar
ID: 35512480
I shall do... atleast a hint to start ..?
0
 

Author Comment

by:GRChandrashekar
ID: 35512606
I meant alteast the steps that a Proc/function/sql should do
0
 
LVL 28

Accepted Solution

by:
Naveen Kumar earned 2000 total points
ID: 35657862
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

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
Suggested Courses
Course of the Month14 days, 5 hours left to enroll

807 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question