Working with CASE statement in Calculated Attribute

[Former Member]

Hi All

  I am trying to apply the same logic which I used for IF statement for CASE also,I am creating calculated attribute for Age group using IF it is working,but

  the CASE functionality is not working.I followed modelling guide SPS4 to define below logic.

   case("AGE" >=30 AND<=35,'30-35', >=35 AND <=40,'35-40', >=40 AND <=45,'40-45','50-55' )

Is it that we can't write conditions in CASE , or I am using wrong syntax.Also can we use SQL predefined functions while creating calculated attribute or we can only use which are there in the window ,because there I could not see any functions for Concatenate ,which is very basic need in String functions.Request your Ideas.

Thanks

Santosh Varada

[Former Member · Accepted solution]

Hi All

  Sorry My usage was wrong.as t he same logic in IF can't be used in CASE,I believe we can't do the complex logic with CASE such as > and <, I used the below syntax for CASE,it is working fine.

case("AGE_GROUP",'30-35','A','35-40','B','40-45','C','45-50','D','50-55','E','F')

but I looking for answer for my second question.

can we use SQL predefined functions while creating calculated attribute or we can only use which are there in the window ,because there I could not see any functions for Concatenate ,which is very basic need in String functions.

Thanks

Santosh Varada

[former_member182277 · Accepted solution]

Hello Santosh,

you cna use the concatenate function in HANA.

please find the below example.

SELECT concat("CARRID","MEALTYPE") FROM SFLIGHT.SMEAL

Hope it is helpful.

regards,

neha

[former_member184768 · Accepted solution]

Hi Santosh,

Some SQL functions do work in calculated attributes even though they are not available in the list of functions in the window. Some people tried it earlier and it had worked for them.

Not sure if it was a bug in that revision and still supported, but I'd say worth a try.

Regards,

Ravi

[Former Member · Accepted solution]

Hi Ravi

I tried below two functions ,Expression Editor says it is Valid Expression but when I Activated the attribute view it is throwing error saying Logical Index Error,Invalid Attribute type.

string(CONCAT( "NAME_FIRST" , "NAME_LAST" ))

LEFT( "NAME_FIRST", 5)

@Neha

I want to use the function in Calculated attribute , I don't think we can use select statements in Expression Editor while defining Calculated attribute.Correct me if I am wrong.

Thanks

Santosh Varada

[former_member182277 · Accepted solution]

Hello Santosh,

In one of the SQL guide, its written that we can use

CONCAT("str1","str2")

But in my version i am also geetign the same error when i trying for calclated atribute.

Dont know why? i tried all option..

Might be its a bug..

Regards,

neha

[former_member184768 · Accepted solution]

Please try with + operator. It works.

Regards,

Ravi

[former_member184768 · Accepted solution]

Just for your info:

Definition:

Output:

Hope this helps.

Regards,

Ravi

[former_member182114 · Accepted solution]

Hi guys,

Expression Editor is for Calc.Engine só you can't use the same as you use on SQLScript.

On SQL Script the same logic applies to what is allowed inside an CE_CALC that you can't use SQL Script.

Different functions with similar use:

SQL:

left, rigth, substr, concat...

variable IN ('1','2')...

CE:

leftstr, rigthstr, midstr, +...

instr(variable,'1','2')...

More details on Page 26: https://help.sap.com/hana/hana_dev_sqlscript_en.pdf

Regards, Fernando Da Ros

---------------------------------

Hi Santosh,

You can use IF(intarg,arg2,arg3), didn't tested but should be: if("AGE" >=30 AND "AGE" <= 35,'30-35', if("AGE" >=36 AND <=40,'36-40', if("AGE" >=41 and "AGE" <=45,'41-45','51-55' ) ) )

the left, should be LEFTSTR( "NAME_FIRST", 5)

Try it.

Regards, Fernando Da Ros

Message was edited by: Fernando Ros

[Former Member · Accepted solution]

Hi Ravi

  Thanks for that, it is working for me also is there any document on this ,or we need to use trial and error methods to find by ourselves .

Thanks

Santosh Varada

[Former Member · Accepted solution]

Hi Fernando

I have  used IF  statement for creating calculated attribute  before , it is working , I wanted to use some SQL built in functions because the functions provided in Expression Editor are very limited ,  besides SQL Script functions[CE], we have other built in functions these can be found in SQL reference manual, which we use as part of simple select statements ,at least if we can use those functions here we can reduce some effort,but it seems all the functions are not working. for example As mentioned by Ravi above  I tried '+' operator for concatenation it is working , there is concat function for the same , but it is not working.

Request your Ideas

Thanks

Santosh Varada

[former_member182114 · Accepted solution]

Hi Santosh,

Hmm, maybe me or you aren't understanding...

What I understood from your posting is that you trying to use builtin functions on Expression Editor, but the Expression Editor don't use SQL builtin functions but TREX builtin functions, that you can take a look on page 26: https://help.sap.com/hana/hana_dev_sqlscript_en.pdf

The same you can use for CE_CALC you can use on Expression Editor, also it's listed there on the functions provided. BTW: There's no concat there

Is not it?

Regards, Fernando Da Ros

[Former Member · Accepted solution]

Hi Fernando

I got your point now. I observed the functions these functions are exactly which are available in Expression Editor,in that section they mentioned the grammar how to use these functions, you mean to say that we need to use  the grammar mentioned to define any calculated attribute,rather than using SQL built in functions correct me if I am wrong.

Thanks

Santosh Varada

[former_member182114 · Accepted solution]

Hi Santosh,

Yes, that's it.

Regards, Fernando Da Ros

[Former Member · Accepted solution]

Hi Fernando

  Thanks for quick reply,but it will be good if the built in functions are also allowed , as it will add to the rich library.

Now I need to concentrate on the  Grammar ,thanks for the pointer.

Thanks

Santosh

[anup_singh5 · Accepted solution]

Hi ,

I get below error while using CASE statement as in below format , same as in above example

Statement :

case("AGE",'0 - 50', 'A' ,'50 - 100', 'B' , 'D' )

Validation Error :

SAP DB Tech JDBC :[2048]: Column store error: [6970] Evaluator : type error in expression evaluator;string[here]case(int "AGE",string'0-50',string'A', string'50-100',string'B',string'D')

When i remove single quote for '0 - 50' , validation passes successfully , but i don't get expected results.

Please let me know , if i need to mention in any other format.

Thanks & Regards,

Anup Singh

[former_member182302 · Accepted solution]

Have a look on this

Regards,

Krishna Tangudu