Skip to Content

Archived discussions are read-only. Learn more about SAP Q&A

Dynamic SQL with lowercase table names

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)

Tags:
Former Member
replied

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

0 View this answer in context
Not what you were looking for? View more on this topic or Ask a question