Parsing Error

[Prakash Bhai]

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.

[Lynn]

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

[peritas-chris]

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!