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

[Former Member]

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

[former_member184768 · Accepted solution]

Hi Veera,

Are you using SQL script (SELECT statement) or using CE functions in the script based view which has the given calculated attribute.

If the CASE is part of the SELECT statement, then it should be something like below:

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

Regards,

Ravi

[Former Member · Accepted solution]

Hi Ravi,

Thanks for the reply.

I am using CE functions in script-based calc view. Can you let me know if we can use CASE with CE functions. If yes, i need to know the syntax of the CASE statement.

Thanks

Veera

[former_member184768 · Accepted solution]

Hi Veera,

The CASE statement can be used in Calc views using CE_CALC function as follows:

CE_CALC('CASE ("B1", ''x1'', "B2", 0 )', integer) AS "CC"

This works as follows:

Check if value of B1 column = x1 (please not that x1 is not in double quotes but has two single quotes before and after), then  use B2 column value else use value 0. This column is named as CC with type INTEGER.

In your case, since you have multiple conditions to check, either it can go over nested CASE statements or can be implemented with the IF() function in CE_CALC.

I will try to post IF() function in Calc view shortly.

Regards,

Ravi

[former_member184768 · Accepted solution]

The syntax for IF statement is as follows:

CE_CALC('IF (("NAME" = ''ZRRAPC003_FI_COPA_SD'' AND "TYPE" = ''S'' AND "SIGN" = ''I'' AND "OPTION" = ''EQ'' AND "LOW" = ''11119995''), "SALES_SD", '''' )', varchar(20)) AS "CA_FM_SRC"

Regards,

Ravi

[former_member223074 · Accepted solution]

Hi Ravi,

I was using the CE script for my conditional calculation in CE_PROJECTION using CASE statement something like this.

var_out = CE_PROJECTION(:JOIN2, ["ProductId","PriceDate","Price","PriceNew", CE_CALC('CASE ("PriceDate", ''1/1/2010 0:00'', "PriceNew", 0 )', DECIMAL(10,2)) AS "FinalPrice"]);

Except "FinalPrice" that should be based on "PriceDate" value,  remaining all fields are available in my schema.

This throws me an error

"

Variable depends on an unassigned variable: 00: line 12 col 78 (at pos 889)nSet Schema DDL statement: set schema "SYSTEM"nType DDL: create type "_SYS_BIC"."cPricing/CA_CMDTY_PRC1/proc/tabletype/VAR_OUT" as table "

Further I want to calculate the "Revenue" too using the "FinalPrice"..

Would you please pass on any thoughts?

Thanks,

Anil

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