CE_CALC IF condition with OR not working

[gokul_jayaraman2]

Hi All,

I have the below statement in CE_PROJECTION, The OR condition is not working.

It works whenever FUNCCUR is YEN, When the FUNCCUR is NTD its not multiplying by 100. Can anyone please let me know if i can use OR condition inside CE_CALC like how written below, If not please correct me .

CE_CALC('IF(("FUNCCUR" = ''YEN'' OR "FUNCCUR" = ''NTD''), ("GLTSL07" * 100.00),"GLTSL07")', decimal(17,2)) AS "GLTSL07_2",

I can use the same with case but just want to learn the mistake in the above code.

Thanks,

Gokul

[pfefferf]

OR can be used in that way in a CE_CALC function. Have you checked that the FUNCCUR has really a value "NTD" and the value does not contain e.g. trailing whitespaces which lets the if condition fail?

Regards,
Florian

PS: As you have mentioned, you should solve your requirement w/o using CE functions, cause they are deprecated already for quite some time.

[lbreddemann · Accepted solution]

Looks like a quotation mark mistake.

When you use the double-quotation marks (" ") you're referring to column names, not to values. To specify values, use single quotation marks (' '). For specifying single quotes within single quotes, use the escape mechanism by providing single quote multiple times in a row:

 '  ''<- these are single quotation marks -> '' '

[gokul_jayaraman2 · Accepted solution]

Hi Lars,

Thanks a lot for your reply.

I have removed a single quote before YEN and NTD, now i am getting an error .

SAP DBTech JDBC: [257]: sql syntax error: incorrect syntax near "YEN": line 180 col 26 (at pos 15391)

CE_CALC('IF(("RTCUR" = 'YEN' OR "FUNCCUR" = 'NTD'), ("GLTSL07" * 100.00),"GLTSL07")', decimal(17,2)) AS "GLTSL07_2",

If i add the quotes back its not giving error.

Also just want to add in the below code

"FUNCCUR" - This is double quotes to represent field name

''YEN'' - Two single quotes to denote value - If i change to 1 single quote it gives error.

CE_CALC('IF(("FUNCCUR" = ''YEN'' OR "FUNCCUR" = ''NTD''), ("GLTSL07" * 100.00),"GLTSL07")', decimal(17,2)) AS "GLTSL07_2",

The issue with the above code is, any condition after OR is not working, which means when i pass the record with YEN currency it multiplies by 100, but when i pass a record with NTD its not multiplying with 100. I guess the OR condition is not working. If i switch the YEN and NTD, the alternate condition works fine. Can you please correct if the Syntax is correct .

Thanks,

Gokul