HANA SQL WHERE Clause using variable as fieldname

[former_member472726]

I am trying to build a SQL query on a table where I use a variable for the table fieldname in teh where clause e.g,

var1 VARCHAR(10) ;

var1 := '"FIELD1"';

select * from TABLE1 where :var1 = 'X' ;

It's not recognising var1 as FIELD1 from TABLE1, can anyone help?

[former_member472726]

FYI, I managed to solve this. For anyone looking at this post for an answer, I used an APPLY_FILTER

example..

--lv_whereclause = ':ZZBLKRSNT = ''X''';

lt_result = APPLY_FILTER(ZTEST_TABLE, :lv_whereclause);

[Yogananda]

derekjones

This is not possible in SQL. You cannot use variables in the WHERE clause. You will need to use dynamic SQL (i.e. using EXECUTE IMMEDIATE) to achieve this.

[former_member472726]

Thanks Michael above has suggested this, but I cannot get it working in my SQl Calc view..

/********* Begin Procedure Script ************/

ZZBLKRSNT varchar(16);

BEGIN

ZZBLKRSNT := '"ZZBLK_RSN_01"';

EXECUTE IMMEDIATE 'LT_ROOT = SELECT "MANDT", "VBELN", "ERDAT" from "SAPEED"."VBAK" WHERE ' || :ZZBLKRSNT || ' = ''X'' ';

LT_FINAL =

SELECT * from :LT_ROOT ;

var_out = SELECT * from :LT_FINAL;

END

/********* End Procedure Script ************/

It fails at SELECT * from :LT_ROOT as it cannot find LT_ROOT.

Any assistance/advice would be most appreciated

[Cocquerel]

You should use an EXECUTE IMMEDIATE statement like this

DO BEGIN
   var1 VARCHAR(10) ;
   var1 := '"FIELD1"';
   EXECUTE IMMEDIATE 'select * from TABLE1 where ' || :var1 || ' = ''X'' ;';
END

[former_member472726]

Thanks Michael

I've tried your suggestion, but it's not working on my actual code. (the above was just a simple example). I'm using the selection to then generate a table in a HANA SQL Calc View, it's where it then tries to select the above results it cannot find it.

Here's a simplified version of my actual code..

/********* Begin Procedure Script ************/

ZZBLKRSNT varchar(16);

BEGIN

ZZBLKRSNT := '"ZZBLK_RSN_01"';

EXECUTE IMMEDIATE 'LT_ROOT = SELECT "MANDT", "VBELN", "ERDAT" from "SAPEED"."VBAK" WHERE ' || :ZZBLKRSNT || ' = ''X'' ';

LT_FINAL =

SELECT * from :LT_ROOT ;

var_out = SELECT * from :LT_FINAL;

END

/********* End Procedure Script ************/

It fails at SELECT * from :LT_ROOT as it cannot find LT_ROOT.

Any assistance/advice would be most appreciated

Thank you again

Derek

[Cocquerel]

try the following

EXEC 'SELECT "MANDT", "VBELN", "ERDAT" from "SAPEED"."VBAK" WHERE ' || :ZZBLKRSNT || ' = ''X'' ' INTO LT_ROOT;