cancel
Showing results for 
Search instead for 
Did you mean: 

Working with CASE statement in Calculated Attribute

Former Member
0 Kudos
5,951

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

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

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
Contributor
0 Kudos

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

Ravi_Channe
Active Contributor
0 Kudos

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
0 Kudos

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
Contributor
0 Kudos

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

Ravi_Channe
Active Contributor
0 Kudos

Please try with + operator. It works.

Regards,

Ravi

Ravi_Channe
Active Contributor
0 Kudos

Just for your info:

Definition:

Output:

Hope this helps.

Regards,

Ravi

former_member182114
Active Contributor
0 Kudos

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
0 Kudos

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
0 Kudos

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
Active Contributor
0 Kudos

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
0 Kudos

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
Active Contributor
0 Kudos

Hi Santosh,

Yes, that's it.

Regards, Fernando Da Ros

Former Member
0 Kudos

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
Explorer
0 Kudos

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
Active Contributor
0 Kudos

Have a look on this

Regards,

Krishna Tangudu

Answers (0)