IN comparison is not supported inside function, alternative ?

[holger_blum]

Hello,

I try to rebuild my Oracle functions inside HANA ( 1.00.85 ).

I have now a problem with my function:

CREATE FUNCTION SB_TEST(

  BUKRS NVARCHAR,

  VV001 DECIMAL,

  VV002 DECIMAL,

  VV003 DECIMAL )

RETURNS TEST Decimal(15,3)

LANGUAGE SQLSCRIPT READS SQL DATA AS

BEGIN

    test := case when :BUKRS in ('1010','1020') then 1

                 when :BUKRS in ('1600') then 2

                 else 0

            end;

END;

Error: feature not supported: IN comparison is not supported

Is there an alternative for IN comparison inside functions ?

Some of my functions uses more than 20 IN expressions.

Holger

[lbreddemann · Accepted solution]

If IN is not available, then OR is typically the next option to use.

[holger_blum · Accepted solution]

Hi Lars,

it works.

Original from Oracle:

CASE WHEN KSTAR IN

(

'0040000100','0040000150','0040000500','0040000550','0040010100','0040010150','0040030100','0040030150','0040300100',

'0040300150','0040300500','0040300550','0040500100','0040500150','0040500450','0040500500','0041000100','0041000150',

'0041000500','0041000550','0041250100','0041250150','0041250500','0041250550','0041260100','0041260150','0041260500',

'0041260550','0042000100','0042000150','0041999999','0023003000','0023003003','0023003030','0023003050','0023003100',

'0023003125','0023003126','0028003000','0028003003','0028003030','0028003050','0028003100','0028003125','0028003126'

)

THEN VV045

ELSE 0

END

New in HANA:

CASE WHEN :KSTAR = '0040000100' or :KSTAR = '0040000150' or :KSTAR = '0040000500' or :KSTAR = '0040000550' or :KSTAR = '0040010100'

                  or :KSTAR = '0040010150' or :KSTAR = '0040030100' or :KSTAR = '0040030150' or :KSTAR = '0040300100' or :KSTAR = '0040300150'

                  or :KSTAR = '0040300500' or :KSTAR = '0040300550' or :KSTAR = '0040500100' or :KSTAR = '0040500150' or :KSTAR = '0040500450'

                  or :KSTAR = '0040500500' or :KSTAR = '0041000100' or :KSTAR = '0041000150' or :KSTAR = '0041000500' or :KSTAR = '0041000550'

                  or :KSTAR = '0041250100' or :KSTAR = '0041250150' or :KSTAR = '0041250500' or :KSTAR = '0041250550' or :KSTAR = '0041260100'

                  or :KSTAR = '0041260150' or :KSTAR = '0041260500' or :KSTAR = '0041260550' or :KSTAR = '0042000100' or :KSTAR = '0042000150'

                  or :KSTAR = '0041999999' or :KSTAR = '0023003000' or :KSTAR = '0023003003' or :KSTAR = '0023003030' or :KSTAR = '0023003050'

                  or :KSTAR = '0023003100' or :KSTAR = '0023003125' or :KSTAR = '0023003126' or :KSTAR = '0028003000' or :KSTAR = '0028003003'

                  or :KSTAR = '0028003030' or :KSTAR = '0028003050' or :KSTAR = '0028003100' or :KSTAR = '0028003125' or :KSTAR = '0028003126' THEN :VV045

                  ELSE 0

                  END

Holger

PS.: Could I ask you about SAP HANA training like HA100 HA150 ...... ? per eMail or phone ?

[lbreddemann · Accepted solution]

Just ask here in the forum .

[stefan_koehler · Accepted solution]

Hi Lars,

> If IN is not available, then OR is typically the next option to use.

Sorry, i can not resist.

It looks like SAP has not copied all the functions from other database vendors to camouflage the source (SAP: Ehemaliger Mitarbeiter erhebt schwere Vorwürfe gegen SAP - SPIEGEL ONLINE)

... as always - you know ... just kidding of course

Regards from the from the other side

Stefan

[former_member219812 · Accepted solution]

hahaha... good one!...