Skip to Content
avatar image
Former Member

SQL Script Calculation View: in CE_PROJECTION use locally declared variable as a filter

Hello,

I have the following code that is currently working:


BEGIN
DECLARE LV_TABLE NVARCHAR(4) := 'MAKT';
....
p_r1_rules = CE_PROJECTION (:t_rules,
["RULE_ID","TABLE_NAME","FIELD_TECH_NAME","MATERIAL_TYPE",
"RULE_SHORT_TEXT","RULE_LONG_TEXT","RULE_DIMENSION",
"FIELD_DESCRIPTION",
CE_CALC(' '':LV_TABLE'' ',NVARCHAR(4)) AS "ANALYSIS_TABLE"],
'"TABLE_NAME" = "ANALYSIS_TABLE" AND
  "FIELD_TECH_NAME" = ''MAKTG'' AND
  "RULE_SHORT_TEXT" = ''Mandatory''');


Is there a way to filter "TABLE_NAME" using LV_TABLE variable directly instead of having to create "ANALYSIS_TABLE" calculated column and filtering on it?

Something like:

'"TABLE_NAME" = LV_TABLE AND
  "FIELD_TECH_NAME" = ''MAKTG'' AND
  "RULE_SHORT_TEXT" = ''Mandatory'''


Thank you,
Victoria

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • Best Answer
    Jan 09, 2015 at 05:59 AM

    Hi Victoria,

    you can define your filter like following:

    ' "TABLE_NAME" = '':LV_TABLE'' AND "FIELD_TECH_NAME" = "MAKTG" AND "RULE_SHORT_TEXT" = "Mandatory" '

    This is ok as long as the content of LV_TABLE is not influenced by a user input; otherwise SQL Injections would be possible. Please check the note regarding this in the official documentation (http://help.sap.com/hana/SAP_HANA_SQL_Script_Reference_en.pdf, chapter 8.2.4).

    Best Regards,

    Florian

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member

      Thank you!

      The following code works:

      p_r1_rules = CE_PROJECTION (:t_rules,

      ["RULE_ID","TABLE_NAME","FIELD_TECH_NAME","MATERIAL_TYPE",

      "RULE_SHORT_TEXT","RULE_LONG_TEXT","RULE_DIMENSION",

      "FIELD_DESCRIPTION"],

      '"TABLE_NAME" = '':LV_TABLE'' AND

        "FIELD_TECH_NAME" = ''MAKTG'' AND

        "RULE_SHORT_TEXT" = ''Mandatory''');

      I tried it before but was receiving an error when trying to preview the data.  I am not receiving this error today.

      Thank you very much for your help,

      Victoria