SQL Scripting

[Former Member]

Hi,

how i can use more than one CASE Statement in one calculation view? Every time i get syntay errors

BEGIN

     var_out = SELECT

               "_BIC_ZC2VERTST",

               "_BIC_ZC2MASTAT",

               "_BIC_ZC2MITARB",

               "_BIC_ZK2KOPF",

               "_BIC_ZC2ORGTGS",

             (case

                 when ("_BIC_ZC2MASTAT" = '01'  AND ("_BIC_ZC2VERTST" = '01' OR "_BIC_ZC2VERTST" = '02'))

                     then "_BIC_ZK2KOPF"

                         ELSE NULL END) as "ZK2GBKOPF"

                          /*First CASE Statement works alone*/

              (case

                 when ("_BIC_ZC2MASTAT" = '01'  AND ("_BIC_ZC2VERTST" = '01' OR "_BIC_ZC2VERTST" = '02'))

                     then "_BIC_ZK2KOPF"

                         ELSE NULL END) as "ZK2GBKOPF"       

from  "sap.bw::ZDRPE_GESVIEW";

END

Thank you so much!

[lbreddemann · Accepted solution]

The problem is not the calculation view or procedure, but that your statement violates SQL syntax.

Not sure what you want to achieve by copying the same case clause into the selection list a second time, but

a) there would need to be a comma between both case clauses

and

b) you can assign only one expression to a projection column - you'd need to rename the output column of the second case.

- Lars

[Former Member · Accepted solution]

Thank you so much.. yes you are right the example makes no scense.. here it is in the right form. The mistake was because of the comma (a). It works now!

BEGIN

     var_out = SELECT

               "_BIC_ZC2VERTST",

               "_BIC_ZC2MASTAT",

               "_BIC_ZC2MITARB",

               "_BIC_ZK2KOPF",

               "_BIC_ZC2ORGTGS",

               "_BIC_ZK2FTE",

             (case

                 when ("_BIC_ZC2MASTAT" = '01'  AND ("_BIC_ZC2VERTST" = '01' OR "_BIC_ZC2VERTST" = '02'))

                     then "_BIC_ZK2KOPF"

                         ELSE NULL END) as "ZK2GBKOPF",

              (case                  

                 when ("_BIC_ZC2MASTAT" = '01'  AND ("_BIC_ZC2VERTST" = '01' OR   "_BIC_ZC2VERTST" = '02'))

                     then "_BIC_ZK2FTE"

                         ELSE NULL END)  as "ZK2GBFTE"       

from  "sap.bw::ZDRPE_GESVIEW";

END

I did not find any syntax help in the internet, do you have an idea where i can get good information about sql script syntax?

Thanks again!

[lbreddemann · Accepted solution]

How about trying the official SAP documentation? SAP HANA SQLScript Reference - SAP Library

It's even built into SAP HANA Studio; just press F1...

- Lars

[Former Member · Accepted solution]

Yes i know this option... okay thank you i will try and learn, learn,.... 🙂

Thanks

[Former Member]

Other Problem:

case
	when ("_BIC_ZC2MASTAT" = '01'  AND ("_BIC_ZC2VERTST" = '01' OR "_BIC_ZC2VERTST" = '02')
	AND  ("_BIC_ZC2ALTTLZ" = '01' OR "_BIC_ZC2ALTTLZ" = '03' OR "_BIC_ZC2ALTTLZ" = '04' OR "_BIC_ZC2ALTTLZ" = '05')
	AND  ("_BIC_ZC2VERTVH" = '01' OR "_BIC_ZC2VERTVH" = '02' OR "_BIC_ZC2VERTVH" = '03' OR "_BIC_ZC2VERTVH" = '04'))
	then "ZK2PBFTE" = "_BIC_ZK2FTE"	ELSE NULL END)  as "ZK2GBFTE",

Case Statement works but i have problems when i want to assign a value to the measure.

I think again a Syntax Error but can not find help.

"ZK2PBFTE" = "_BIC_ZK2FTE"

Any Ideas how i can do this ? I want to assign the ZK2PBFTE which i have created in Hana Studio the value og _BIC_ZK2FTE.

Thanks agian for help.

[lbreddemann]

You can't assign in the case statement.

Instead by using the AS <column name> you declare the name of the output column.

As for the THEN ... part of the CASE statement, you simply put the value/expression after the THEN that should be the result of the case evaluation. No need for a =-sign there.

Maybe a last hint: when you want to compare if a column's value is equal to one of several possible values, then using OR is a bit clumsy to read.

Instead use the IN predicate (IN Predicate - SAP HANA SQL and System Views Reference - SAP Library)

So the code could look similar to this then:

case       

   when ("_BIC_ZC2MASTAT" = '01' 

    AND ("_BIC_ZC2VERTST"  in ( '01', '02')


    AND ("_BIC_ZC2ALTTLZ" in ( '01', '03', '04','05'))

    AND ("_BIC_ZC2VERTVH" in ('01', '02', '03','04'))


then

        "_BIC_ZK2FTE"

else

        NULL

end  as "ZK2GBFTE"

Ok, any more help in this thread and I start to charge you my daily rate 🙂

- Lars

[Former Member]

Thats very nice!!! Thank you !

Ahhh and sorry... i get it know.. I will stop 😄

In German we have a nice adage:

"Good things come to those who wait."

[lbreddemann]

Being a German myself I am familiar with that one...

Enjoy your new HANA SQL and BW on HANA skills!

- Lars