Dynamic SQL with lowercase table names

[former_member194135]

Greetings,

I'm looking to build a dynamic SQL EXEC statement that will update a table in the SAP<SID> namespace. My first step is to get the namespace of the table and I can do that by:

SELECT SCHEMA_NAME INTO var_insert_results_schema_name FROM "SYS"."M_TABLES" WHERE TABLE_NAME = <yourtablename>;

Then I build my insert statement using the schema name but at the end of my insert statement I have a WHERE <fieldname> != ''';

Note the three single quotes - the last one ends the string.  With this I get an unterminated quoted string literal error. Is there something special I have to do?

My other concern is lowercase table names (or the package name) have to be enclosed in double quotes.  Has anyone run into issues with the double quotes in the EXEC statement.

Thanks for the help!

Steve

Here is the insert statement I'm trying to dynamically change based on the system ID

insert into "SAP_DQM"."sap.dqm.data::BATCH_MATCH_RESULTS" select * from :output_data_gac where GROUPID != '';

Below I'm just trying to get the double quote issue before I bring in the single quote issue..

EXEC 'insert into """' || :var_insert_results_schema_name ||
'"""."""sap.dqm.data::BATCH_MATCH_RESULTS"""
select * from :output_data_gac';

Could not execute 'call
SAP_DQM."sap.dqm.procedures/SP_SQLW_BATCH_MATCH"( 'But000' , 'ADRC',
'X')' in 1.944 seconds .

SAP DBTech JDBC: [259]: invalid table name:  [259]
SAP_DQM.sap.dqm.procedures/SP_SQLW_BATCH_MATCH: line 41 col 1 (at pos 2028):
invalid table name exception: invalid table name:  Could not find table/view
"sap.dqm.data::BATCH_MATCH_RESULTS" in schema "SAP_DQM": line 1 col 27 (at pos 26)

[lbreddemann · Accepted solution]

HI Steve,

I think you overdid the double quotes a bit...

This works:

create schema test_a;
create schema test_b;
create column table test_a."funny_table" (id integer, name varchar(30));
create column table test_b."funny_table" (id integer, name varchar(30));
create procedure InsertFun (IN schema_name varchar (20))
language SQLSCRIPT
AS
begin
   EXEC 'insert into "' || :schema_name || '"."funny_table" 
         (select ''1'', ''FUN'' from dummy)';
end;
select  * from test_a."funny_table";
--  0
select  * from test_b."funny_table";
--  0
call insertfun ('TEST_A');
select  * from test_a."funny_table";
-- 
-- ID  NAME
-- 1   FUN 
call insertfun ('TEST_B');
select  * from test_b."funny_table";
-- ID  NAME
--1   FUN 

Your example should work like this:

EXEC 'insert into "' || :var_insert_results_schema_name || '"."sap.dqm.data::BATCH_MATCH_RESULTS"
      select * from :output_data_gac';

- Lars

[former_member194135 · Accepted solution]

Thanks Lars...

One additional thing we figured out was you can't select from a local table in the EXEC statement.

Per the docs which I missed:

One cannot use SQLScript variables in the SQL statement (but when constructing the SQL

statement string).

So we copied the SQLScript variable results to a HANA table then used the HANA table in the EXEC insert to write it to the ABAP table.

One too many steps but it works and it's only being done once, not repeatedly.

Thanks again.