I am trying to use a query calculation to combine data from several fields into one field with a case statement like this.
case
when [Ded Cd] = '*FI' then .0765
when [Ded Cd] = '*WC' then [Work Comp Rate]
when[Ded Title] LIKE 'MPS%R%DC%' then [Frng Rate for DC-EM]
else [Frng Rate]
end
however, I want the results to have the same format as the originating field. *WC should have 4 decimals, however it gets rounded up to 2 decimals with this case statement. How can I keep the 4 decimals which are necessary for the calculation that will be run on this new data item?
format? Or are you talking about data types?
A case statement should result in consistent data types. So, are you saying that when Ded Cd = *FI, the result is .08? What is Cognos actually doing here? If you inspect the "Generated SQL" are you seeing something like
CAST([columnname] AS DECIMAL(5,2))
What is the data type of each the various columns you are using in your expression?
The data type for *WC and Frng Rate is numeric in the tables where they originate. It sounds like I need to define each item as a cast as decimal for this case statement?
Using cast did not work for me when I used it on each data itemof the case statement. It does work when I rearrange the order of the case statement so that the field that has 4 decimals is listed first. Then when I define a value in the case statement, as in *FI = .0765, the values all are 4 decimal points.
case
when [Ded Cd] = '*WC' then [Work Comp Rate]
when[Ded Title] LIKE 'MPS%R%DC%' then [Frng Rate for DC-EM]
when [Ded Cd] = '*FI' then .0765
else[Frng Rate]
end
case
when [Ded Cd] = '*WC' then round ( to_number( [Work Comp Rate] ), 4)
when[Ded Title] LIKE 'MPS%R%DC%' then round ( to_number( [Frng Rate for DC-EM] ), 4)
when [Ded Cd] = '*FI' then .0765
else round ( to_number( [Frng Rate] ), 4)
end
If the data in the table is of 2 decimals then Cognos may not present that with 4 decimals but if the data has 4 or more decimals then the above would give you 4 decimals. I tested it against an Oracle database.
*WC is a column name? I thought it was a data value. And it looks like a character type, not a numeric type.
You didn't specify any data types in response to my previous question. "numeric" is meaningless alone. It requires a scale and precision.
If [FRNG Rate] is a NUMERIC(11,4), [Work Comp Rate] is a VARCHAR(7), [Frng Rate for DC-EM] is a INT, and .0765 is "Cognos please try to guess the data type", that can cause problems for your CASE statement.
dougp: I am working with a hosted instance of Cognos, to which I can only see the symbol # or abc to determine the data type, I do not have access to the sql, and don't know how to see the underlying details to find the data type. My apologies for not being able to provide more precise detail in my question. You were correct in that *WC was a value and not a data item.
Thank you cognostechie: Your solution worked and I appreciate the rewrite of my case statement to include round and to_number.
Appreciate the replies!