>>Here is a pair of SELECTs that order a table of monthly revenue postings by category into columns by month. Some months are left out for simplicity.
>>
>>select dist fisyear from rta_revenues order by fisyear into cursor crsA
>>
>>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
>>
>>
>>I would like to combine these in such a way that all the fisyears in the table, not just the first one. Can someone help?
>>Thanks
>
>The question is unclear. Whare result you're currently getting and what do you want? Can you show some data samples?
>
>Thanks.
The table looks like this:
receiveddate;category;amount;fisyear
There is only one record per category per month. So there is no need to sum.
The pair of selects produces a cursor containing a column for category and each month. It looks like this:
category;sep;oct;nov;fisyear (plus the other months.)
This is exactly what I want except I only get the first fisyear. What I would like to do now is add a second set of records to the result for the second fisyear and a third set of records for the third fisyear an so on. I could do this by creating an empty result cursor and then appending the results into it while SCANing through the fisyears but I thought there might be a way to accomplish it with just the SELECT.