i need dynamic SQL because this long sql that i have wrote, has to be reuse by a team, that has no idea about this. for simplifying that, i want to add this long sql in a procedure and then the member of the team has just to call this procedure to execute this query.

I am facing a similar situation which I think the original poster was inquiring about. In my situation, I have a 200 column staging table. One hundred of those columns represent a score and the other 100 columns are correlated dates to each score.

I successfully used UNPIVOT to turn the 100 score columns into 100 rows which was the main goal. But now I'm struggling to pick up the value of the related score date column for that score.

It seems that a case statement is the solution:

CASE colm -- colm is used in UNPIVOT to capture column name
WHEN 'score_column_1' THEN date_column_1
WHEN 'score_column_2' THEN date_column_2

But I prefer not to have 100 when statements if possible. So far, the best I can do is to get the CASE statement to return the name of the date column, but I really want the value.

I believe that the original poster was facing a similar scenario. If colm = 'score_column_1' then I can manipulate that string to turn into 'date_column_1', but I don't want that string I want the value of date_column_1.

This probably looks ridiculous, but it may help further explain what I an I believe the original poster are trying to accomplish. I tried the following to get the date value to be return with its related score:

WHEN colm IN ('X','Y','Z') THEN EXECUTE IMMEDIATE 'BEGIN :1 := '||colm||'_DATE; END;' USING OUT N
END

For example, if the score column was 'X', then the UNPIVOT is giving me both the column name of 'X' and the column value of say 89. Now I know that I want the value from the X_DATE column in the 200 column staging table, but it does not seem possible in a case statement.

Any thoughts would be appreciated.

Thank you for the prompt response. I'll go the static typing route as I assume that there is not a way to do what I want to dynamically with a few lines of code.

I apologize for my comment that you did not understand the question, as clearly you did. Big fan!

How to create dynamic case when expression rather than use hard code value like "(1,2)"?

example
select amount,
case
when :p_para in (1, 2) then
' --- display normal column --- '
when :para in (3, 4) then
' --- display normal column --- '
end output_1,

case
when :p_para in (1, 2) then
amount * ' --- display normal column --- '
when :para in (3, 4) then
amount * ' --- display normal column --- '
end output_2,

case
when :p_para in (1, 2) then
(amount * ' --- display normal column --- ') + tax
when :para in (3, 4) then
(amount * ' --- display normal column --- ') + tax
end output_3
from dual

var v1 number;
var v2 number;
select case
         when 1 in ( :v1, :v2 ) then 'Y'
         else 'N'
       end
from   dual;

CASEWHEN1IN(:V1,:V2)THEN'Y'ELSE'N'END   
N    

exec :v1 := 1;
select case
         when 1 in ( :v1, :v2 ) then 'Y'
         else 'N'
       end
from   dual;

CASEWHEN1IN(:V1,:V2)THEN'Y'ELSE'N'END   
Y