Skip to Content

using variables in merge query is throwing unsupported error

HI,

I am writing a merge query in the HANA procedure. In full stack web IDE. normal merge command is working but when i try to use variables in it then it is throwing "not supported expression here".

Does any one knows how to solve it. And if this feature is in development then by when it will get released. or this is a bug. or variables with merge generally don't work. or HDI xs advanced problem.

Please Help

Thanks and Regards

Rakshit Chopra

Add comment
10|10000 characters needed characters exceeded

  • No code - no help!

    Show us your code and what exactly doesn't work and we might be able to help.

  • i am not able to edit the question so posting the code here

    create PROCEDURE "xyz::abc" ( 
    	IN startTime Timestamp,
    	IN endTime Timestamp
    )
       LANGUAGE SQLSCRIPT
       SQL SECURITY INVOKER AS
    BEGIN	
    		MERGE INTO "xyz::table1" t1 
    		USING 
    			( select 
    				"column_1",
    				"colume_2",
    				sum("colume_3") as "sum_column_3" 
    			from "xyz::table2" 
    			where "created_time" >= startTime and "created_time" < endTime
    			group by "column_1","column_1") t2 
    		ON t1."column_1" = t2."column_1" and t1."created_time" = startTime
    		WHEN MATCHED THEN UPDATE SET 
    			t1."column_1" = t2."column_1",
    			t1."column_2" = t2."column_2",
    			t1."sum_column_3" = t2."sum_column_3"
    		WHEN NOT MATCHED THEN INSERT 
    			("column_1",
    			"column_2",
    			"sum_column_3",
    			"created_time"
    	   ) VALUES(
    				t2."column_1",
    				t2."column_2",
    				t2."sum_column_3", 
    				startTime
    				);
    END;
    

    here if i replace the startTime and endTime variables with constant values or current_timestamp and add_seconds(current_timestamp,3600) it works fine.

  • Get RSS Feed

2 Answers

  • Best Answer
    Aug 15, 2018 at 01:28 AM

    I invested the time and effort of building a reproducible example myself and found that this indeed seems to be a limitation with the current (HANA 2.00.33) implementation of the MERGE INTO command.

    This becomes apparent, when one tries to execute the MERGE command with placeholders:

    SAP DBTech JDBC: [467]: cannot use parameter variable: STARTTIME: line 8 col 41 (at pos 229)

    The error message for the same problem in the SQLScript embedded SQL looks a bit different:

    SAP DBTech JDBC: [7]: feature not supported: "DEVDUDE"."merge_t1_from_t2": line 8 col 9 (at pos 167): 
    not supported expression here: __TYPED_LONGDATE__(:1): line 12 col 34 (at pos 542) 
    

    This was, in fact, what brought me to think about a workaround and shows how important it is to provide all necessary information (like actual error message and a reproducible example) when asking questions or opening support incidents.

    The workaround is to use the bind_as_value() command modifier in SQL Script:

    create or replace PROCEDURE "merge_t1_from_t2" ( 
        IN startTime Timestamp,
        IN endTime Timestamp
    )
       LANGUAGE SQLSCRIPT
       SQL SECURITY INVOKER AS
    BEGIN   
            MERGE INTO t1 
    	        USING 
    	            ( select 
    	                 col1,
    	                 col2,
    	                sum(col3) as sum_col3 
    	            from t2 
    	            where "created_time" >= :startTime and "created_time" < :endTime
    	            group by col1, col2) t2_b 
    	        ON  t1.col1 = t2_b.col1 
    	        and t1."created_time" = bind_as_value(:startTime)
          
            WHEN MATCHED THEN UPDATE SET 
                t1.col1 = t2_b.col1,
                t1.col2 = t2_b.col2,
                t1.sum_col3 = t2_b.sum_col3
            
            WHEN NOT MATCHED THEN INSERT 
                     (col1, col2, sum_col3, "created_time")  
              VALUES( t2_b.col1, t2_b.col2,  t2_b.sum_col3, bind_as_value(:startTime));
    END;
    Add comment
    10|10000 characters needed characters exceeded

  • Aug 01, 2018 at 11:30 PM

    The code seems to be missing the colons (:) when the variables are referenced.

    create PROCEDURE "xyz::abc" ( 
    	IN startTime Timestamp,
    	IN endTime Timestamp
    )
       LANGUAGE SQLSCRIPT
       SQL SECURITY INVOKER AS
    BEGIN	
    		MERGE INTO "xyz::table1" t1 
    		USING 
    			( select 
    				"column_1",
    				"column_2",
    				sum("column_3") as "sum_column_3" 
    			from "xyz::table2" 
    			where "created_time" >= :startTime 
                              and "created_time" < :endTime
    			group by "column_1", "column_1") t2 
    		ON  t1."column_1" = t2."column_1" 
                    and t1."created_time" = :startTime
    		
                    WHEN MATCHED THEN UPDATE SET 
    			t1."column_1" = t2."column_1",
    			t1."column_2" = t2."column_2",
    			t1."sum_column_3" = t2."sum_column_3"
    		
                    WHEN NOT MATCHED THEN INSERT 
    			("column_1",
    			"column_2",
    			"sum_column_3",
    			"created_time"
    	                 ) VALUES(
    				t2."column_1",
    				t2."column_2",
    				t2."sum_column_3", 
    				:startTime
    				);
    END;
    

    As the question lacks a reproducible example (no DDL for the table, no test data) and important information (what HANA revision), this answer only covers the obvious fault. There might very well be more issues with this code.

    Add comment
    10|10000 characters needed characters exceeded

    • If you want anyone to reproduce the issue provide DDL code to set up the tables correctly.

      Descriptions, stories, tales and experience reports of your development is sadly not something that executes on HANA...