Data Item with an IF or CASE expression

[Rosadocc]

Hi all,

I am creating a Data Item that uses either an IF or CASE function but seem to always get the same error when running the report:

Data source adapter error: com.ibm.db2.jcc.am.SqlDataException: Invalid character found in a character string argument of the function "DECFLOAT".. SQLCODE=-420, SQLSTATE=22018, DRIVER=3.65.77

I thought this would be pretty straight forward but can't avoid getting the error. Looking to create either an IF or CASE expression that groups numbers of ship days into a range. For this example it would be:
case when ([Number of Days]<=3)
then '1-3 Days'
when ([Number of Days]<=7)
then '4-7 Days'
else '0'
end

[BigChris]

I can't see anything wrong with your case statement, which makes me wonder if there's something amiss with the [Number of Days] calculation. Is that done in your query, or is it a field that's calculated in your database? If you can post the calculation that might shine a little more light on the issue.

[adam_mc]

Try removing the parentheses around your WHEN conditions - They are not needed on a CASE statement, However, they are needed on an IF statement.

Hope that helps.
Thanks,
Adam.

[Rosadocc]

I can't see anything wrong with your case statement, which makes me wonder if there's something amiss with the [Number of Days] calculation. Is that done in your query, or is it a field that's calculated in your database? If you can post the calculation that might shine a little more light on the issue.

[Number of Days] is a calculated field in my database. It is simply the difference in days between two date fields. However even when doing a custom calculation between the two date fields to create [Days between (Order Date),(Ship Date)] I still get the same error when running the report.

[Rosadocc]

Try removing the parentheses around your WHEN conditions - They are not needed on a CASE statement, However, they are needed on an IF statement.

Hope that helps.
Thanks,
Adam.

Thanks. Tried removing the parenthesis but unfortunately still getting the same error.

[bus_pass_man]

DB2 thinks that there's character data somewhere.

1.Just to confirm, you've identified that this expression is the source of your error and that, if you remove it from your report, the report executes without error.

2. If you create a report with just the expression in it, what happens?

3.Check the data type of number of days and confirm that it is a numeric data type.

4.What is the expression you used to create this [Days between (Order Date),(Ship Date)] Did you use _days_between?


What are the data types of order date and ship date?

5.

Your expression might have a flaw.   when ([Number of Days]<=7) ought to be something like when ([Number of Days]between 4 and 7 or

when ([Number of Days]>3 and  [Number of Days]<=7)  (the syntax would need checking but you get the idea)

[Rosadocc]

Thanks everyone for your help!

Not exactly sure what the issue ended up being but it seems to work for me now. I opened up the report in Cognos Workspace Advanced and it worked. I went back to running the report in Report Studio and now it also works.

Now I have a new question on using that new query expression field in a crosstab. It doesnt seem display property the crosstab only shows one aggregate labeled as "Ship Date Range" instead of separate buckets (i.e. "1-3 Days", "4-7 Days", etc). Perhaps I'll have to start a new post for this issue.

[dougp]

That's not to say the problem won't randomly return.  (...since it randomly stopped.)

Your error message states:

Invalid character found in a character string argument of the function "DECFLOAT"

Is DECFLOAT a user-defined function that is used to calculate [Number of Days] at runtime?  I suspect that's where the problem is.  I doubt this is a problem with your Cognos report.