Question Re: Syntax error in case statement used in script-based Calculation view in Technology Q&A

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

Former Member — Tue, 13 Nov 2012 07:49:09 GMT

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

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

Ravi_Channe — Tue, 13 Nov 2012 07:59:59 GMT

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

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

Former Member — Tue, 13 Nov 2012 10:31:14 GMT

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

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

henrique_pinto — Tue, 13 Nov 2012 14:04:46 GMT

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.

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

Ravi_Channe — Thu, 15 Nov 2012 14:54:20 GMT

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

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

Ravi_Channe — Thu, 15 Nov 2012 15:09:42 GMT

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

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

Former Member — Fri, 06 Sep 2013 04:50:36 GMT

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