Hello, I'm trying to filter Service Count By 'Free Consultation' and 'Paid Consultation' from Service Type by this Expression Filter CASE WHEN Service Type = 'Free Consultation' THEN Service Count WHEN Service Type = 'Paid Consultation' THEN Service Count ELSE 0 END but I get an error: Invalid Expression. A general error has occurred. Message returned from OBIS [ecid: ts:2024-08-07T12:54:28.819+03:00]. Near </*>: Syntax error (HY000) . (HY000) any insight on how i can do this? Thanks

Hi, You are creating a filter based on an expression, but your expression isn't a filter at all, just a value. A filter should be a condition: something = something else, something 0 : CASE WHEN Service Type IN ('Free Consultation', 'Paid Consultation') THEN Service Count ELSE 0 END > 0 The error message isn't the most intuitive if you read after "Invalid Expression", but it starts by telling you that you have an invalid expression (because it isn't a filter condition at all). But you maybe don't need that CASE WHEN at all… I'm trying to filter Service Count By 'Free Consultation' and 'Paid Consultation' from Service Type If what you want is your visualization to only show data for Service Type IN ('Free Consultation', 'Paid Consultation'), then your filter should be just this: Service Type IN ('Free Consultation', 'Paid Consultation') An expression filter will filter the whole visualization, if you want your column "Service Count" to only show values for those 2 service types or show 0 instead, then you need to create a calculation with your CASE WHEN (a calculation is a single value, not like a filter, therefore your CASE WHEN alone will work). And then you use your calculation instead of your Service Count column in the visualization.

"Oracle Error code: 1722, message: ORA-01722: invalid number" That looks like you're trying to do arithmetic on something that's not a number in terms of data type. Are you sure that your Service Count is an actual number which can be aggregated? Also you have many other columns in that Workbook. Can you just try to make the formula work on its own first before doing all the other stuff at the same time? RCA (root cause analysis) will become much easier.

@Christian Berg -Oracle Service Count is a number but Service Type is a string. I have them both as measure with count aggregation. i tracked the root cause and it was the calculation. i also tried to analyze the issue in calculation but it is so simple and i couldn't find it thank you for your assistance

Register to the Data Intelligence Summit London Register today to the Oracle Analytics Data Intelligence Summit in London on March 19th
Oracle Analytics was named as a Leader Oracle Analytics was named as a Leader in the 2024 Gartner® Magic Quadrant for Analytics and BI Platforms
New Oracle Analytics Data Visualization Challenge Register today to the Oracle Analytics Data Visualization Challenge and showcase your Data Visuzalization chops
Oracle Analytics Product Satisfaction Survey Share your experience and satisfaction with Oracle Analytics Product with our Product Satisfaction Survey

Oracle Analytics Cloud and Server

Case (IF) Errors with Invalid Expression, Syntax Error, ORA-01722: invalid number

Accepted answer

172

Views

Comments

Oracle Analytics Cloud and Server

Categories

Case (IF) Errors with Invalid Expression, Syntax Error, ORA-01722: invalid number