One of the SAP B1 queries I need to run requires the drop a temporary global table only if that table already exists. In Microsoft SQL this could be solved by using the function IF EXISTS.
After some research, I found out that HANA SQL Script does not implement this functionality. We thought of a workaround solution that searched the table name in the system view M_TABLES. (SYS.M_TABLES).
The solution was tested in Hana Studio and it works. But when we try to run the same query in SAP B1, we get this error:
"[SAP AG][LIBODBCHDB DLL][HDBODBC] General error; 328 invalid name of function or procedure:tmp_qry_sp_ad16f578-d019-49a1-9080-b5fbeof304d4: line1 col 30 'Blanket Agreement'(OOAT)(at pos 29.."
The snippet of code that introduces the error (I tried and SYS.M_TABLES but it did not work):
declare table_count integer;
selectcount(*) into table_count from M_TABLES where table_name like'TmpCarmen';
if table_count > 0
Can someone help me with a suggestion or a resolution?