Oracle BI Publisher, select different columns by parameter?

Question

I'm just starting with Oracle BI Publisher and I really don't know how to get along with my task. My project is to ask the user for a parameter (that's ok), and if the user chooses 01, I should display ONLY 'january' with some data. If the user chooses 03, I should display 3 columns, 'january' 'february' 'march' with some data in each. I have all of the columns till december, I just don't know how to select different number of columns based on the given parameter. EDITED, ADDED CODE! declare p SYS_REFCURSOR; begin if (:month)=02 then open p for select 'bazisev' as evtipus, month-1 as month, january, february from db.tablename where month=(:month)-1 and sorszam between 4 and 15 union all select 'targyev' as evtipus, month, january, february from db.tablename where month=(:month) and sorszam between 4 and 15 union all select 'targyev/bazisev' as evtipus, month, january, february from db.tablename where month=(:month) and sorszam between 32 and 39 order by sorszam; elsif (:month)=01 then open p for select 'bazisev' as evtipus, month-1 as month, january from db.tablename where month=(:month)-1 and sorszam between 4 and 15 union all select 'targyev' as evtipus, month, january from db.tablename where month=(:month) and sorszam between 4 and 15 union all select 'targyev/bazisev' as evtipus, month, january from db.tablename where month=(:month) and sorszam between 32 and 39 order by sorszam; end if; end So right now i'm doing it like this with 2 months, but it says: ORA-00907: missing right parenthesis I'm pretty sure i have all the parenthesis i needed. Any suggesting?

Answer 1

The format you used for display the columns with month names is not correct

you will need to use the format

'value' AS month name

where value is either empty or month name if match the month number

I updated your code (see below) with the correct format above:

:

declare p SYS_REFCURSOR;

begin

if (:month)=02 then

open p for

select 'bazisev' as evtipus, month-1 as month, 'january' AS january, '' AS february from db.tablename

where month=(:month)-1

and sorszam between 4 and 15

union all

select 'targyev' as evtipus, month, '' AS january, 'february' AS february from db.tablename

where month=(:month)

and sorszam between 4 and 15

union all

select 'targyev/bazisev' as evtipus, month, '' AS january, 'february' AS februaryfrom db.tablename

where month=(:month)

and sorszam between 32 and 39

order by sorszam;

elsif (:month)=01 then

open p

for select 'bazisev' as evtipus, month-1 as month, '' AS january from db.tablename

where month=(:month)-1

and sorszam between 4 and 15

union all

select 'targyev' as evtipus, month, 'january' AS january from db.tablename

where month=(:month) and sorszam between 4 and 15

union all

select 'targyev/bazisev' as evtipus, month, 'january' AS january from db.tablename

where month=(:month) and sorszam between 32 and 39

order by sorszam;

end if; end

Hope this helps!