SAP Hana Database Error feature not supported cannot support non-constant types

[holger_blum]

Hi@all,

I get this error.

I create a table ( for test )

CREATE COLUMN TABLE CE19000_TEST

AS

(

SELECT

    BUKRS, VV001, VV002, VV003, VV008, VV009, VV012, VV021, VV047, VV061, VV062, VV093, VV201, VV202, VV203, ZZ_GROSS_SALES

FROM

    CE19000

WHERE

    PALEDGER = '02' AND BUKRS = '1111'

);

COMMIT;

Then I create a function

DROP FUNCTION sb_gross_sales;

CREATE FUNCTION sb_gross_sales(

  BUKRS NVARCHAR,

  VV001 DECIMAL,

  VV002 DECIMAL,

  VV003 DECIMAL,

  VV008 DECIMAL,

  VV009 DECIMAL,

  VV012 DECIMAL,

  VV021 DECIMAL,

  VV047 DECIMAL,

  VV061 DECIMAL,

  VV062 DECIMAL,

  VV093 DECIMAL,

  VV201 DECIMAL,

  VV202 DECIMAL,

  VV203 DECIMAL )

RETURNS GROSS_SALES Decimal(15,3)

LANGUAGE SQLSCRIPT READS SQL DATA AS

BEGIN

    GROSS_SALES := case

      WHEN :BUKRS = '2222' or :BUKRS = '3333' THEN (( :VV001 + :VV002 + :VV003 + :VV021 + :VV047 + :VV093 + :VV061 + :VV062 + :VV008 + :VV009 ) - :VV012) +  ( :VV201 + :VV202 + :VV203 )

      WHEN :BUKRS = '4711' THEN :VV001

      ELSE (( :VV001 + :VV002 + :VV003 + :VV021 + :VV047 + :VV093 + :VV061 + :VV062 + :VV008 + :VV009 ) - :VV012) END;

END;

And I try this

UPDATE CE19000_TEST

SET ZZ_GROSS_SALES = SB_GROSS_SALES( BUKRS, VV001, VV002, VV003, VV008, VV009, VV012, VV021, VV047, VV061, VV062, VV093, VV201, VV202, VV203 );

COMMIT;

and get the error "SAP Hana Database Error feature not supported cannot support non-constant types"

Target column is DECIMAL(15,3),

Return Value from function is also Decimal(15,3).

When I try this

SELECT

  SB_GROSS_SALES( BUKRS, VV001, VV002, VV003, VV008, VV009, VV012, VV021, VV047, VV061, VV062, VV093, VV201, VV202, VV203 )

FROM

  CE19000_TEST;

it works.

I have no idea what to do.

Holger

[holger_blum · Accepted solution]

I found a solution based on:

https://scn.sap.com/thread/3692839

UPDATE CE19000_TEST AS T1

  SET T1.ZZ_GROSS_SALES =

  ( SELECT

    SAS.SB_GROSS_SALES( T2.BUKRS, T2.VV001, T2.VV002, T2.VV003, T2.VV008, T2.VV009, T2.VV012, T2.VV021, T2.VV047, T2.VV061, T2.VV062, T2.VV093, T2.VV201, T2.VV202, T2.VV203 )

    FROM CE19000_TEST AS T2

    WHERE

    T1.MANDT    = T2.MANDT AND

    T1.PALEDGER = T2.PALEDGER AND

    T1.VRGAR    = T2.VRGAR AND

    T1.VERSI    = T2.VERSI AND

    T1.PERIO    = T2.PERIO AND

    T1.PAOBJNR  = T2.PAOBJNR AND

    T1.PASUBNR  = T2.PASUBNR AND

    T1.BELNR    = T2.BELNR AND

    T1.POSNR    = T2.POSNR )

    ;

    COMMIT;

First try:

SAP Hana Database Error: user defined function runtime error: exception: CompilationFailedException: No details

Program :

Error: Unexpected end of input at user-defined function

I add a primary key on this table:

ALTER TABLE SAS.CE19000_TEST ADD

PRIMARY KEY (

MANDT,

PALEDGER,

VRGAR,

VERSI,

PERIO,

PAOBJNR,

PASUBNR,

BELNR,

POSNR

);

and it works

Category	Timestamp	Duration	Message	Line	Position
Statement	10.09.2015 13:24:30	0:00:01.212	32625 rows affected	1	0

But is very slow, 34.322.448 records in 1:06:52.187 ....

[lbreddemann · Accepted solution]

It's not surprising that this UDF is slow, since you force a row by row processing.

HANA cannot know what you do inside of your function or how to rewrite it to set operations.

To have this update run faster you may put the update logic into a single statement instead of a function.

If absolute speed is key here, you may even use two separate update statements, one for each branch of your update logic.