>>>>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.
>
>I thought just by looking at your select and trying to figure it in my mind that it should have worked for all the years.
>
>But if it's not working the way you want you can try to create your months/year/category as one extra derived table and join with the rest of information based on the category.
>
>E.g.
>
>select (sum(iif(Month=1,Amount,000000000000)) as Jan, ...., Year, Category group by Year, Category - use this as a derived table.
>
>Actually, looks like this select alone will give you what you want.
There should be no need to sum. We are not combining records, only positioning them. What I'm getting is:
Category, Sep, Oct, Nov, Fisyear
Cat1, ##.##, ##.##, ##.##,2007
Cat2, ##.##, ##.##, ##.##,2007
Cat3, ##.##, ##.##, ##.##,2007
What I want is:
Category, Sep, Oct, Nov, Fisyear
Cat1, ##.##, ##.##, ##.##,2007
Cat2, ##.##, ##.##, ##.##,2007
Cat3, ##.##, ##.##, ##.##,2007
Cat1, ##.##, ##.##, ##.##,2008
Cat2, ##.##, ##.##, ##.##,2008
Cat3, ##.##, ##.##, ##.##,2008
The reason I'm only getting the first year is because the first cursor (crsA) which contains the year, is not being iterated. I had thought that I could use the second select as the derived table and wrap it into the first as in
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.