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

ashraf817

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

Gianni Ceresa

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 < something else, etc.

In your case you are just defining one part of the filter, a value. Now add the operator and the other part of the filter expression and it should be good.

In your case, after the END, you should add the condition you expect, for example that your CASE WHEN should be > 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.

Christian Berg -Oracle

"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.

ashraf817

@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

Christian Berg -Oracle

Good news! Can you please mark the appropriate responses in this thread as correct answer so that other forum users can benefit from it as well?

Thank you!

BalagurunathanBagavathy-Oracle

@ashraf817

I tried creating a similar calculation using the formula below and it worked fine.

case
when "HCM - Workforce Core"."Age Band"."Name" = '21 - 29' then "HCM - Workforce Core"."Facts - Workforce Headcount"."Headcount"
when "HCM - Workforce Core"."Age Band"."Name" = '30 - 39' then "HCM - Workforce Core"."Facts - Workforce Headcount"."Headcount"
else 0
end

Can you your copy the formula and paste it in a notepad and see if there is anything wrong?

ashraf817

@BalagurunathanBagavathy-Oracle

It looks okay

CASE
WHEN XSA('weblogic'.'BI_Daily Visits')."V_BI_DIALY_VISITS"."Service Type" = 'Free Consultation' THEN XSA('weblogic'.'BI_Daily Visits')."V_BI_DIALY_VISITS"."Service Count"
WHEN XSA('weblogic'.'BI_Daily Visits')."V_BI_DIALY_VISITS"."Service Type" = 'Paid Consultation' THEN XSA('weblogic'.'BI_Daily Visits')."V_BI_DIALY_VISITS"."Service Count"
ELSE 0

——
I also tried to create another simple Expression Filter and it gave me the same error

TOPN(XSA('weblogic'.'BI_Daily Visits')."V_BI_DIALY_VISITS"."Service Description", 10)

error:
Invalid Expression. A general error has occurred. Message returned from OBIS [ecid: ts:2024-08-07T14:54:40.304+03:00]. Near &lt;/*&gt;: Syntax error (HY000) . (HY000)

SteveF-Oracle

The TOPN error is with a less than (<), greater than (>) symbols; however, we cannot see your screen.
Please provide a screenshot of the expression filter and error.

Also, it is best to ask one question per thread, so that the correct answer can be attributed and the thread closed for future readers/searchers.

ashraf817

@SteveF-Oracle

apologies,

here's a screenshot

BalagurunathanBagavathy-Oracle

@ashraf817

The keyword END is missing at the end of the CASE WHEN formula. Please check.

ashraf817

@BalagurunathanBagavathy-Oracle

even with keyword END the outcome remains the same

ashraf817

@Gianni Ceresa Thank you very much for your thorough response,

Yes, i want column "Service Count" to only show values for those 2 service types or show 0 instead.

I did a CASE WHEN calculation as you advised but when i drag it to visualization i get query error:

Error during query processing (SQLExecDirectW).
State: HY000. Code: 10058. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred.
(HY000)
State: HY000. Code: 43275. [nQSError: 43275] Message returned from OBIS [ecid: ts:2024-08-20T13:10:52.900+03:00].
(HY000)
State: HY000. Code: 43119. [nQSError: 43119] Query Failed:
(HY000)
State: HY000. Code: 17001. [nQSError: 17001] Oracle Error code: 1722, message: ORA-01722: invalid number
at OCI call OCIStmtFetch.
(HY000)
State: HY000. Code: 17012. [nQSError: 17012] Bulk fetch failed. (HY000)
SQL Issued: SET VARIABLE ENABLE_DIMENSIONALITY = 1; SELECT
0 s_0,
XSA('weblogic'.'BI_Daily Visits')."V_BI_DIALY_VISITS"."Doctor" s_1,
XSA('weblogic'.'BI_Daily Visits')."V_BI_DIALY_VISITS"."Month" s_2,
XSA('weblogic'.'BI_Daily Visits')."V_BI_DIALY_VISITS"."Quarter of Year" s_3,
XSA('weblogic'.'BI_Daily Visits')."V_BI_DIALY_VISITS"."Year" s_4,
CASE
WHEN XSA('weblogic'.'BI_Daily Visits')."V_BI_DIALY_VISITS"."Service Type" IN ('Free Visit','Paid Consultation') THEN XSA('weblogic'.'BI_Daily Visits')."V_BI_DIALY_VISITS"."Service Count"
ELSE 0
END s_5,
REPORT_AGGREGATE(CASE
WHEN XSA('weblogic'.'BI_Daily Visits')."V_BI_DIALY_VISITS"."Service Type" IN ('Free Visit','Paid Consultation') THEN XSA('weblogic'.'BI_Daily Visits')."V_BI_DIALY_VISITS"."Service Count"
ELSE 0
END BY XSA('weblogic'.'BI_Daily Visits')."V_BI_DIALY_VISITS"."Doctor") s_6,
REPORT_AGGREGATE(CASE
WHEN XSA('weblogic'.'BI_Daily Visits')."V_BI_DIALY_VISITS"."Service Type" IN ('Free Visit','Paid Consultation') THEN XSA('weblogic'.'BI_Daily Visits')."V_BI_DIALY_VISITS"."Service Count"
ELSE 0
END BY XSA('weblogic'.'BI_Daily Visits')."V_BI_DIALY_VISITS"."Doctor",XSA('weblogic'.'BI_Daily Visits')."V_BI_DIALY_VISITS"."Year",XSA('weblogic'.'BI_Daily Visits')."V_BI_DIALY_VISITS"."Quarter of Year") s_7,
REPORT_AGGREGATE(XSA('weblogic'.'BI_Daily Visits')."V_BI_DIALY_VISITS"."Service Count" BY XSA('weblogic'.'BI_Daily Visits')."V_BI_DIALY_VISITS"."Doctor") s_8,
REPORT_AGGREGATE(XSA('weblogic'.'BI_Daily Visits')."V_BI_DIALY_VISITS"."Service Count" BY XSA('weblogic'.'BI_Daily Visits')."V_BI_DIALY_VISITS"."Doctor",XSA('weblogic'.'BI_Daily Visits')."V_BI_DIALY_VISITS"."Year",XSA('weblogic'.'BI_Daily Visits')."V_BI_DIALY_VISITS"."Quarter of Year") s_9,
REPORT_SUM(XSA('weblogic'.'BI_Daily Visits')."V_BI_DIALY_VISITS"."Cash" BY XSA('weblogic'.'BI_Daily Visits')."V_BI_DIALY_VISITS"."Doctor") s_10,
REPORT_SUM(XSA('weblogic'.'BI_Daily Visits')."V_BI_DIALY_VISITS"."Cash" BY XSA('weblogic'.'BI_Daily Visits')."V_BI_DIALY_VISITS"."Doctor",XSA('weblogic'.'BI_Daily Visits')."V_BI_DIALY_VISITS"."Year",XSA('weblogic'.'BI_Daily Visits')."V_BI_DIALY_VISITS"."Quarter of Year") s_11,
REPORT_SUM(XSA('weblogic'.'BI_Daily Visits')."V_BI_DIALY_VISITS"."Credit" BY XSA('weblogic'.'BI_Daily Visits')."V_BI_DIALY_VISITS"."Doctor") s_12,
REPORT_SUM(XSA('weblogic'.'BI_Daily Visits')."V_BI_DIALY_VISITS"."Credit" BY XSA('weblogic'.'BI_Daily Visits')."V_BI_DIALY_VISITS"."Doctor",XSA('weblogic'.'BI_Daily Visits')."V_BI_DIALY_VISITS"."Year",XSA('weblogic'.'BI_Daily Visits')."V_BI_DIALY_VISITS"."Quarter of Year") s_13,
REPORT_SUM(XSA('weblogic'.'BI_Daily Visits')."V_BI_DIALY_VISITS"."Total" BY XSA('weblogic'.'BI_Daily Visits')."V_BI_DIALY_VISITS"."Doctor") s_14,
REPORT_SUM(XSA('weblogic'.'BI_Daily Visits')."V_BI_DIALY_VISITS"."Total" BY XSA('weblogic'.'BI_Daily Visits')."V_BI_DIALY_VISITS"."Doctor",XSA('weblogic'.'BI_Daily Visits')."V_BI_DIALY_VISITS"."Year",XSA('weblogic'.'BI_Daily Visits')."V_BI_DIALY_VISITS"."Quarter of Year") s_15,
XSA('weblogic'.'BI_Daily Visits')."V_BI_DIALY_VISITS"."Cash" s_16,
XSA('weblogic'.'BI_Daily Visits')."V_BI_DIALY_VISITS"."Credit" s_17,
XSA('weblogic'.'BI_Daily Visits')."V_BI_DIALY_VISITS"."Service Count" s_18,
XSA('weblogic'.'BI_Daily Visits')."V_BI_DIALY_VISITS"."Total" s_19
FROM XSA('weblogic'.'BI_Daily Visits')
WHERE
((XSA('weblogic'.'BI_Daily Visits')."V_BI_DIALY_VISITS"."Clinic" = 'general') AND (XSA('weblogic'.'BI_Daily Visits')."V_BI_DIALY_VISITS"."Doctor" IN (' nazmul haque md', 'abu zaid', 'moyasar')))
FETCH FIRST 5000001 ROWS ONLY

The calculation is

appreciate your help