Skip to Content
avatar image
Former Member

CE_CALC IF condition with OR not working

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

Add comment
10|10000 characters needed characters exceeded

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

  • Get RSS Feed

1 Answer

  • Best Answer
    Oct 03, 2017 at 05:24 AM

    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 -> '' '
    Add comment
    10|10000 characters needed characters exceeded

    • Former Member

      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