Hi,
I am using Cognos RS 8.4.1 and getting following error when using 'CASE WHEN' statement.
QuoteUDA-SQL-0358 Line 54: Syntax error near "and".
Case when statement:
Quotecase
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?
QuoteI am applying above statement as a filter which validates without any issue; even report validaes without any error message.
Try this?
?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
try:
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
Hi Shirish Dawane,
Are you trying to cascade something out from this case statement??
Thanks,
Hitesh
--------------------------------------------------------
hthakkar@infocepts.com|www.infocepts.com
--------------------------------------------------------
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?
Hi,
It works fine if I modify this statement as below:
Quotecase
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.
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
Thanks a lot blom0344...it worked. :)
Also, thanks to everyone who tried to help!!!