Search code examples
sqloracle-databasebi-publisher

How to create column for selected parameter?

I have this query that pulls results based on the selected LOV parameter: nvl(:Role, role))

SELECT role,subject 
FROM HS_SUBJECT_INCIDENTS 
WHERE entitlement not in(select entitlement from HS_SUBJECT_INCIDENTS where role in nvl(:Role, role))
AND subject in (select subject from HS_SUBJECT_INCIDENTS where role in nvl(:Role, role))

So if the user selected 'Finance' for the parameter value, the results now show:

|   Role    | Subject  | 
------------------------
| Marketing | Business |  
| Marketing | Business |  
| Analytics | Business |

I want the results to show like this:

|   Role    | Subject  | SelectedParameter |
--------------------------------------------
| Marketing | Business |  Finance          |
| Marketing | Business |  Finance          |
| Analytics | Business |  Finance          |

What do I have to put in the select statement to include a column for the parameter value that was selected?

Solution

  • Just select it:

    SELECT role, subject, :Role as SelectedParameter
FROM . . .