Resolved: CASE WHEN

[SGD]

Hi,

I am using Cognos RS 8.4.1 and getting following error when using 'CASE WHEN' statement.

UDA-SQL-0358 Line 54: Syntax error near "and".

Case when statement:

case
when (?p_axle_than? = 'ge')
then ([Axle Level View].[Axle Fact].[Number of Axles] >= ?p_axle?)
when (?p_axle_than?= 'le')
then ([Axle Level View].[Axle Fact].[Number of Axles] <= ?p_axle?)
when (?p_axle_than?= 'all')
then ([Axle Level View].[Axle Fact].[Number of Axles])
end

I am applying above statement as a filter which validates without any issue; even report validaes without any error message.

Above error message displays when I click 'Finish' button after selecting appropriate prompt values.

Could please anyone guide me to resolve this issue?

[PRIT AMRIT]

I am applying above statement as a filter which validates without any issue; even report validaes without any error message.

Try this?

Code Select Expand

?p_axle_than? = 'ge' and
[Axle Level View].[Axle Fact].[Number of Axles] >= ?p_axle?
or
?p_axle_than?= 'le' and
[Axle Level View].[Axle Fact].[Number of Axles] <= ?p_axle?
or
?p_axle_than?= 'all' and
[Axle Level View].[Axle Fact].[Number of Axles] is not null

[navissar]

try:

Code Select Expand


case ?p_axle_than?
when 'ge' then [Axle Level View].[Axle Fact].[Number of Axles] >= ?p_axle?
when 'le' then [Axle Level View].[Axle Fact].[Number of Axles] <= ?p_axle?
else [Axle Level View].[Axle Fact].[Number of Axles]
end



[hthakkar]

Hi Shirish Dawane,

Are you trying to cascade something out from this case statement??

Thanks,
Hitesh

--------------------------------------------------------
hthakkar@infocepts.com|www.infocepts.com
--------------------------------------------------------

[SGD]

Hi,

I have two prompts.

One is dropdown which shows '>=', '<=' & 'All' as display values and 'ge', 'le' & 'all' as use values respectivly.

Other one is text prompt which only allows numbers to be entered.

So here I want to User selects values from 1st prompt and then enter number in 2nd prompt. If User selects 'All' then is should display all the values irrespective of greater than or less than.

e.g. if User enters '>=' & '0' values then it should show values which are greater that '0' however if User selects 'All' then is should show all the values.

Any thoughts?

[SGD]

Hi,

It works fine if I modify this statement as below:

case
when (?p_axle_than? = 'ge')
then ([Axle Level View].[Axle Fact].[Number of Axles] >= ?p_axle?)
when (?p_axle_than?= 'le')
then ([Axle Level View].[Axle Fact].[Number of Axles] <= ?p_axle?)
when (?p_axle_than?= 'all')
then ([Axle Level View].[Axle Fact].[Number of Axles] = 0)
end

But I want to display all the values of [Number of Axles] query item.

[blom0344]

Code Select Expand


case
when (?p_axle_than? = 'ge')
then ([Axle Level View].[Axle Fact].[Number of Axles] >= ?p_axle?)
when (?p_axle_than?= 'le')
then ([Axle Level View].[Axle Fact].[Number of Axles] <= ?p_axle?)
when (?p_axle_than?= 'all')
then ( 1 = 1 )
end


[SGD]

Thanks a lot blom0344...it worked.  :)

Also, thanks to everyone who tried to help!!!