Errow with IF statement in Scripted Calculation view

[Former Member]

Hi Team,

I am trying to use IF statement in Scripted Calculation view.

My scenario is:-

I need to create a restricted Key figure based on input date from user.

While i am activating my view system gives "Internal deployment of object failed;Repository: Encountered an error in repository runtime extension;Internal Error:Deploy Calculation View: SQL: transaction rolled back by an internal error: Syntax Error for calculated Attribute: line 29 col 9 (at pos 1501)" error.

Below is the code i have used in my CV.

CE_CALC('IF((to_int("BUDAT") >= to_int(2013-01-01),"AMOUNT",'''')', DECIMAL(13,2)) AS "DTR"

Here, "AMOUNT" is another calculated field created using CE_CALC.

Without above IF statement my CV runs perfectly fine. The moment i include IF statement it does not activate.

Kindly provide your inputs.

Thanks & Regards,

Anup

[lbreddemann · Accepted solution]

Hello Anup,

how is to_int(2013-01-01) going to work?

It is not a number, it is not enclosed in single quotation marks, so it cannot be a string - what is SQLScript supposed to do with that?

The thing is: BUDAT is a date data type encoded as string.

Your comparison value looks like a date data type, also encoded as a string.

So why not simply use the proper data type conversion for it then?

CE_CALC('IF((date("BUDAT") >= date(20130101),"AMOUNT",'''')', DECIMAL(13,2)) AS "DTR"

- Lars

[Former Member · Accepted solution]

Hi Lars,

I am extremely sorry about the confusion.

There was an extra parenthesis in the IF statement which was not allowing the CV to be activated. CV is now successfully activated.

But my data is not getting restricted correctly.

It is showing 0 against all the records.

Below is the sql used for calling the CV.

SELECT * FROM "_SYS_BIC"."A_123/CV_SCR_BSID_BSAD_ITEMS" ('PLACEHOLDER' = ('$$Currency$$', '''Local'''),'PLACEHOLDER' = ('$$Date_From$$','2014-01-01' ), 'PLACEHOLDER' = ('$$Date_To$$','2014-06-30' ));

Below is my updated CE_CALC function :-

CE_CALC('IF(date("BUDAT") >= date(:Date_From) and date("BUDAT") <= date(:Date_To),"AMOUNT",0)', DECIMAL(13,2)) AS "DTR"

Is there any issue while passing values here?

Regards,

Anup