Syntax error in case statement used in script-based Calculation view

[#1]

Hi,

I had joined a custom table and a Graphical calculation view using CE_JOIN function in a Script-based Calc View.

1) A IF statement metioned below was written to populate a calculated Attribute based on multiple conditions but was getting a syntax error at '='.

IF "NAME" = 'ZRRAPC003_FI_COPA_SD' AND "TYPE" = 'S' AND "SIGN" = 'I' AND "OPTION" = 'EQ' AND "LOW" = '11119995'

THEN 

"CA_FM_SRC" = 'SALES_SD' ELSE "CA_FM_SRC" = ' '

END;

2) I then tried a CASE statement on the same scenario and again got a syntax error at CASE .

CASE WHEN("NAME" = 'ZRRAPC003_FI_COPA_SD' AND "TYPE" = 'S' AND "SIGN"='I' AND "OPTION"= 'EQ' AND "LOW" = '111199995')

THEN

"CA_FM_SRC" = 'SALES_SD' ELSE "CA_FM_SRC"= ' '

END;

Please let me know where I have go wrong in Syntax of the above two statements.

Thanks

[henrique_pinto]

Hi Veera,

1) you don't have procedural CASEs in Standard (ANSI) SQL like you mentioned.

What you could do (as Anooj mentioned) is, within a SELECT (or any other) statement, something like:

SELECT  (CASE WHEN "NAME" = 'ZRRAPC003_FI_COPA_SD' AND "TYPE" = 'S' AND "SIGN"='I' AND "OPTION"= 'EQ' AND "LOW" = '111199995' THEN 'SALES_SD' ELSE ' ' END) AS "CA_FM_SRC"
FROM myTable ...

2) Within SQLScript procedures, there is a transactional IF clause.

Check page 35 of the HANA SQLScript guide.

IF <bool-expr1>
THEN
   {then-stmts1}
{ELSEIF <bool-expr2>
THEN
   {then-stmts2}}
{ELSE
   {else-stmts3}}
END IF

3) As for CE functions, I have not tested this in particular, but I suppose you could evaluate a CASE expression (as mentioned in point 1 above) within the CE_CALC() function. Please try this and let us know the results.

Best regards,

Henrique.