?
Solved

How to write a union using pivot?

Posted on 2012-08-23
2
Medium Priority
?
861 Views
Last Modified: 2012-08-24
Good Evening,

I have the following query (used to fill a dataset) which works perfectly with 1 query 2 tables but does not work when I attempt to union it.  I know I don't have it written right.  Someone please look it over and tell me how I got it wrong.  Thanks.

Here is what I have :

strSQL = "select * from (select team, pages_reviewed, month from "
strSQL += " (select team, pages_reviewed, nvl(to_char(date_stamp, 'MM-YYYY'),0) month from " & roleTable & "@" & MYConnect & " t1, " & EvalTable & " t2 where t1.osuser=t2.las  and t1.code_status='A' and t1.unit in ('" & gettheunits.Value & "') "
strSQL += "  and trunc(t2.date_stamp) between   to_date('" & start_date.Text.ToString & "', 'MM/DD/YYYY')  and   to_date('" & end_date.Text.ToString & "', 'MM/DD/YYYY') and t2.active is not null "
strSQL += "  order by substr(team, 1,1)))"
strSQL += " PIVOT(sum(pages_reviewed) for MONTH IN ('01-2011' as ""Jan 2011"",'02-2011' as ""Feb 2011"",'03-2011' as ""Mar 2011"",'04-2011' as ""Apr 2011"",'05-2011' as ""May 2011"",'06-2011' as ""Jun 2011"", '07-2011' as ""Jul 2011"", '08-2011' as ""Aug 2011"", '09-2011' as ""Sep 2011"", '10-2011' as ""Oct 2011"", '11-2011' as ""Nov 2011"", '12-2011' as ""Dec 2011"",'01-2012' as ""Jan 2012"",'02-2012' as ""Feb 2012"",'03-2012' as ""Mar 2012"",'04-2012' as ""Apr 2012"",'05-2012' as ""May 2012"",'06-2012' as ""Jun 2012"", '07-2012' as ""Jul 2012"", '08-2012' as ""Aug 2012"", '09-2012' as ""Sep 2012"", '10-2012' as ""Oct 2012"", '11-2012' as ""Nov 2012"", '12-2012' as ""Dec 2012""))"
strSQL += " order by substr(team, 1, 1),team "
strSQL += " UNION ALL "
strSQL += " select * from (select team, pages_reviewed, month from (select team, pages_reviewed, nvl(to_char(date_stamp, 'MM-YYYY'),0) month from contractors t1, " & EvalTable & " t2 where t1.username=t2.las  and t1.code_status='A' and t1.unit in ('" & gettheunits.Value & "') "
strSQL += " and trunc(t2.date_stamp) between   to_date('" & start_date.Text.ToString & "', 'MM/DD/YYYY')  and   to_date('" & end_date.Text.ToString & "', 'MM/DD/YYYY') and t2.active is not null "
strSQL += " order by substr(team, 1, 1)))"
strSQL += " PIVOT(sum(pages_reviewed) for MONTH IN ('01-2011' as ""Jan 2011"",'02-2011' as ""Feb 2011"",'03-2011' as ""Mar 2011"",'04-2011' as ""Apr 2011"",'05-2011' as ""May 2011"",'06-2011' as ""Jun 2011"", '07-2011' as ""Jul 2011"", '08-2011' as ""Aug 2011"", '09-2011' as ""Sep 2011"", '10-2011' as ""Oct 2011"", '11-2011' as ""Nov 2011"", '12-2011' as ""Dec 2011"",'01-2012' as ""Jan 2012"",'02-2012' as ""Feb 2012"",'03-2012' as ""Mar 2012"",'04-2012' as ""Apr 2012"",'05-2012' as ""May 2012"",'06-2012' as ""Jun 2012"", '07-2012' as ""Jul 2012"", '08-2012' as ""Aug 2012"", '09-2012' as ""Sep 2012"", '10-2012' as ""Oct 2012"", '11-2012' as ""Nov 2012"", '12-2012' as ""Dec 2012""))"
 strSQL += " order by substr(team, 1, 1),team "
                     
Thanks.
0
Comment
Question by:thamilto0410
2 Comments
 
LVL 25

Accepted Solution

by:
lwadwell earned 1400 total points
ID: 38327844
remove the ORDER BY before the UNION ALL ... you can only have one at the end of a SQL statement ... including UNION ALL's.

However, as you are doing SELECT * (which I personally dislike) ... the final order by might have an issue too.  Why are you doing substr() on team in the order by ... is it char/varchar2?  ... is so ... order by team will be fine.  For this, while using select *, you may need to do ORDER BY 1.

I find that I build the SQL and test it direct against the database (hard code where required for testing) to ensure that it is a working SQL before I "code-ify" it.
0
 
LVL 1

Author Closing Comment

by:thamilto0410
ID: 38328831
THANK YOU SO MUCH!!  That was it.  I had to remove the inner order by and on the outer order by I did have to do an order by 1 as I was still getting "Order by item must be the number" error.  All is working now.  Thanks again.  YOU GUYS ROCK!!
0

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

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

This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
Suggested Courses

864 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