>>select dist fisyear, ; >>(select dist category, ; >> nvl((select amount ; >> from rta_revenues ; >> where fisyear = crsA.fisyear and ; >> category = crsB.category and ; >> month(receiveddate) = 9),0) as sep , ; >> nvl((select amount ; >> from rta_revenues ; >> where fisyear = crsA.fisyear and ; >> category = crsB.category and ; >> month(receiveddate) = 10),0) as oct , ; >> nvl((select amount ; >> from rta_revenues ; >> where fisyear = crsA.fisyear and ; >> category = crsB.category and ; >> month(receiveddate) = 11),0) as nov , ; >> crsA.fisyear as fisyear ; >> from rta_revenues crsB) ; >> from rta_revenues crsA order by fisyear >>>>but this throws an error that says the subquery is invalid.
>select Category, CAST(iif(Month(receiveddate)=1,Amount,0000000000.00) as N(5,2)) as Jan, ...., FisYearHere is the final
select orderby, jul,aug,sep,oct,nov,dec,Jan,Feb,Mar,apr,may,jun,FisYear ; from rta_revenuecategories crsA; inner join ( ; select ; sum(iif(month(receiveddate)=7,Amount,000000000000.00)) as jul, ; sum(iif(month(receiveddate)=8,Amount,000000000000.00)) as aug, ; sum(iif(month(receiveddate)=9,Amount,000000000000.00)) as sep, ; sum(iif(month(receiveddate)=10,Amount,000000000000.00)) as oct, ; sum(iif(month(receiveddate)=11,Amount,000000000000.00)) as nov, ; sum(iif(month(receiveddate)=12,Amount,000000000000.00)) as dec, ; sum(iif(month(receiveddate)=1,Amount,000000000000.00)) as Jan, ; sum(iif(month(receiveddate)=2,Amount,000000000000.00)) as Feb, ; sum(iif(month(receiveddate)=3,Amount,000000000000.00)) as Mar, ; sum(iif(month(receiveddate)=4,Amount,000000000000.00)) as apr, ; sum(iif(month(receiveddate)=5,Amount,000000000000.00)) as may, ; sum(iif(month(receiveddate)=6,Amount,000000000000.00)) as jun, ; FisYear, Category ; from rta_revenues ; group by FisYear, Category) crsB; on crsA.Category = crsB.Category ; order by FisYear, orderbyActually, your original worked. It seems the sum was needed to enable the group by. I had to add an additional table with an inner join because the Group By messed up the sort order of the output. This one seems to work fine. Thanks for your help. :)