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;

 

以上语法为何是错的呢？

 

对于第一种，必须使用如下方法指示的语法来操作：

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

 

 

 

对于第二种，可以用CE_CALC函数来操作：

CE_CALC('CASE("B1","X1",B2",0)',INTEGER) AS 'CC'

其意思是：当B1=X1时，则取B2的值，否则是0，当前返回值的类型是integer,命名为CC。

如果采用IF来改写CASE语句的话，请看如下：

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"

总结一下：

用CASE语句：

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

用IF语句：

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