case when statement error?

[Danielbryanuk]

Hi all

I am using the following case when statement in a calculation for a dynamic average, but am not getting the expected results. I'm convinced there's something wrong with my logic and expression syntax, but for the life of me can't see it!! Any ideas anyone?

Case

when [FTE].[Time].[Year] = ?pID? and substr([FTE].[Time].[Quarter];5;1) = 1
then [FTE].[FTE count].[VALUE] / ?pMonth?

when [FTE].[Time].[Year] = ?pID? and substr([FTE].[Time].[Quarter];5;1) = 2
then [FTE].[FTE count].[VALUE] / (?pMonth? - 3)

when [FTE].[Time].[Year] = ?pID? and substr([FTE].[Time].[Quarter];5;1) = 3
then [FTE].[FTE count].[VALUE] / (?pMonth? - 6)

when [FTE].[Time].[Year] = ?pID? and substr([FTE].[Time].[Quarter];5;1) = 4
then [FTE].[FTE count].[VALUE] / (?pMonth? - 9)

Else 0

End

Thanks for any help you can offer.

Dan

 

[blom0344]

substring yields a string , not a numerical:

= ('1') instead of = (1)

Ties Blom

 

[Danielbryanuk]

You know sometimes when it's right infront of you....

Thanks Ties!

Just one more question - can you nest functions in the when statement, such as sum(fact/3)? Is there an "immediate window" in Cognos as there is when using the VBA editor in MS Excel for example?

 

[blom0344]

AFAIK no. Cognos uses set-based logic underneath. You can call a stored procedure (but only if the UOW is handled on the database), but unlike VBA there is no programming engine. Using aggregates in the when clause?
I do not think SQL can handle such a thing..

Ties Blom

 

[Danielbryanuk]

I'm still struggling, this time with a case when in the detail filter:

"Case

When ?pFIMLevel? = 'Code'
And ?pSelection? = 'Selection'
Then
Case
when [AC].[FIM Code] is null
then [GO].[FIM Code] like ?pSearch?
else [AC].[FIM Code] like ?pSearch?
end

When ?pFIMLevel? = 'Name'
And ?pSelection? = 'Selection'
Then
Case
when [AC].[FIM Name] is null
then [GO].[FIM Name] like ?pSearch?
else [AC].[FIM Name] like ?pSearch?
end

Else 0
End"

I get the error message as follows:

"QE-DEF-0260 Parsing error before or near position: 138 of: "Case When ?pFIMLevel? = 'Code' And ?pSelection? = 'Selection' Then Case when [AC].[FIM Code] is null then [GO].[FIM Code] like"


Any ideas on what the problem is?

Thanks
Dan

 

[blom0344]

'like' evaluates an expression and returns a boolean. The outcome of then/else has to be another case or an explicit value. So,instead of an logical operator you need to designate another expression / value I guess..

Ties Blom

 

[Danielbryanuk]

Sorry for the dumb question then, but does that mean I can't replace "like" with "in"?

Can I use as the filter:

"?pSearch? in

Case

When ?pFIMLevel? = 'Code'
And ?pSelection? = 'Selection'
Then
Case
when [AC].[FIM Code] is null
then [GO].[FIM Code]
else [AC].[FIM Code]
end

When ?pFIMLevel? = 'Name'
And ?pSelection? = 'Selection'
Then
Case
when [AC].[FIM Name] is null
then [GO].[FIM Name]
else [AC].[FIM Name]
end

Else 0
End"

 

[blom0344]

How about:

?pSearch? =
Case 

When     ?pFIMLevel? = 'Code' 
And    ?pSelection? = 'Selection' 
Then coalesce([AC].[FIM Code],[GO].[FIM Code])

When     ?pFIMLevel? = 'Name' 
And    ?pSelection? = 'Selection' 
Then coalesce([AC].[FIM Code],[GO].[FIM Code])

Else 0 
End

Ties Blom

 

[Danielbryanuk]

Almost there!! Now I have the error messages:

"Incompatible data types in case statement"
"RQP-DEF-0149 The query specification is incorrect. RQP-DEF-0457 Referenced Query 'final' is not defined or its query items contain unresolved references"

I have checked the parameters against the members [FIM Name] and [FIM Code], and there are no differences in data type; Properties of members are as follows...

Data type Character Length 16
Usage Attribute
Display type Value
Prompt type Generated Prompt

The parameters are all set to default, with default text selections, so are not based upon queries.

Can this error message mean something else? Is the display type the same as the data type?

Thanks for your help, again, Ties.

Dan

 

[blom0344]

your else returns a numerical (0). Did you try to change this to a string value? (or simply null)

Ties Blom

 

[Danielbryanuk]

And that's how you do it!!!


THANKS!!!

 

[blom0344]

You're welcome..

Ties Blom