• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 866
  • Last Modified:

How to write a union using pivot?

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
thamilto0410
Asked:
thamilto0410
1 Solution
 
lwadwellCommented:
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
 
thamilto0410Author Commented:
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
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.

Join & Write a Comment

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Tackle projects and never again get stuck behind a technical roadblock.
Join Now