cancel
Showing results for 
Search instead for 
Did you mean: 

using variables in merge query is throwing unsupported error

0 Kudos

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

lbreddemann
Active Contributor
0 Kudos

No code - no help!

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

0 Kudos

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.

Accepted Solutions (1)

Accepted Solutions (1)

lbreddemann
Active Contributor

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;

Answers (1)

Answers (1)

lbreddemann
Active Contributor
0 Kudos

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.

0 Kudos

Error: (dberror) [7]: feature not supported: "APIMGMT_ANALYTICS_2"."apimgmt-analytics-hdi.core-db.procedure::aggregate_Apiproxy_Time_start2": line 23 col 2 (at pos 1563): not supported expression here: line 14 col 59 (at pos 1183)

Tried using colon also again the same error.

the above code works when startTime and endTime are replace with timestamp constants like '2018-08-02 10:12:41.926'

The scenario of the above code is there are 2 tables. xyz::table1 and xyz::table2 . In table 1 there are 3 columns and in table 2 there are 4 columns.

we are doing the group by column 1 and column 2 and sum on column 3 and storing it in table 2 with start timestamp.

lbreddemann
Active Contributor
0 Kudos

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...