Hi All,
I am using a value prompt in which I am using 3 static variables called M1,M2,M3.
when I select M1, it has to give values (between 1 and 5000000) for M2( between 5000000 and 10000000) and for M3(greater than 10000000).
So the code is like this,
CASE
WHEN ?Par_v?= 'M1'
THEN [T] BETWEEN 1 AND 5000000
CASE
WHEN ?Par_t? = 'M2'
THEN [T] BETWEEN 5000000 AND 10000000
CASE
WHEN ?Par_t? = 'M3'
THEN [T] > 10000000
END
but I m getting parsing error.. can any 1 pls help me on this.
One "CASE" and one "END" with multiple when/then statments is the correct syntax. Including an ELSE is usually a good idea. Also your ranges overlap so that 5000000 is included in M1 and M2 conditions. Might want the second one to be 5000001. Just a thought.
CASE
WHEN ?Par_v?= 'M1'
THEN [T] BETWEEN 1 AND 5000000
WHEN ?Par_t? = 'M2'
THEN [T] BETWEEN 5000001 AND 10000000
WHEN ?Par_t? = 'M3'
THEN [T] > 10000000
END
So from what I understand, you are using this prompt to build a filter? You should really use a prompt macro, where the SQL is passed directly from the prompt into the filter statement. Please confirm that this is the intention - thanks!