Loop through list of tables, error 260 when name passed through

[gregdelima]

With the following procedure:

CREATE PROCEDURE "GDELIMA"."ZPSAEXPORT"
LANGUAGE SQLSCRIPT AS
BEGIN
	declare pos int = 1;
	declare i int = 1;
	declare dat varchar(8) = '20991231';
	declare counter int;
	
	CREATE LOCAL TEMPORARY COLUMN TABLE #ZPSA_TEMP as(
			SELECT "TABLE_NAME", ROW_NUMBER() OVER (Order by "TABLE_NAME") as ROW_NUM
			FROM "SYS"."TABLES"
			WHERE "SCHEMA_NAME" = 'SAPBIW'
			AND "TABLE_NAME" IN('/BIC/AZSDOA62600','/BIC/AZSDOA65400','/BIC/AZSDOA65500','/BIC/AZSDOA65900','/BIC/AZSDOA66300','/BIC/AZSDOA66400','/BIC/AZSDOA66500','/BIC/AZSDOA66700','/BIC/AZSDOA66800','/BIC/AZSDOA66900','/BIC/AZSDOA67100','/BIC/AZSDOA67200','/BIC/AZSDOA67300','/BIC/AZSDOA67500','/BIC/AZSDOA67600','/BIC/AZSDOA67700','/BIC/AZSDOA67900','/BIC/AZSDOA68000','/BIC/AZSDOA68100','/BIC/AZSDOA68100','/BIC/AZSDOA68800','/BIC/AZSDOA68900')
		);


	select max("ROW_NUM") as n into counter from #ZPSA_TEMP;
	--Limited to 2 for testing, replace 2 with :counter when ready
	FOR i in 1..2 DO	--:counter 
		SELECT * from(
			SELECT '"SAPBIW".'||'"'||"TABLE_NAME"||'"' as TABNAME from #ZPSA_TEMP WHERE "ROW_NUM"= :pos  --;
					  )
		WHERE "VAL_END">=:dat;
		pos=:pos+1;
	END FOR;
	
	DROP TABLE #ZPSA_TEMP;
END;


call ZPSAEXPORT();

When pass-through the table name, the procedure generates

SAP DBTech JDBC: [260]: invalid column name: VAL_END: line 25 col 9 (at pos 1129)

To, me this is telling me it's not referencing the actual table. How can I get around this?

[gregdelima · Accepted solution]

I was able to resolve this by adding in:

FOR i in 1..:counter DO
    SELECT '"SAPBIW".'||'"'||"TABLE_NAME"||'"' as TABNAME into w_table from #ZPSA_TEMP WHERE "ROW_NUM"= :pos;
    EXECUTE IMMEDIATE 'select * from '||:w_table|| 'where "VAL_END">='||:dat ;
    pos=:pos+1;
END FOR;

Full script:

DROP PROCEDURE "GDELIMA"."ZPSAEXPORT";
CREATE PROCEDURE "GDELIMA"."ZPSAEXPORT" (in dat varchar(8))
LANGUAGE SQLSCRIPT AS
BEGIN
	declare pos int = 1;
	declare i int = 1;
	--declare dat varchar(8) = '20991231';
	declare counter int;
	declare w_table varchar(30);
	
	CREATE LOCAL TEMPORARY COLUMN TABLE #ZPSA_TEMP as(
			SELECT "TABLE_NAME", ROW_NUMBER() OVER (Order by "TABLE_NAME") as ROW_NUM
			FROM "SYS"."TABLES"
			WHERE "SCHEMA_NAME" = 'SAPBIW'
			AND "TABLE_NAME" IN('/BIC/AZSDOA62600','/BIC/AZSDOA65400','/BIC/AZSDOA65500','/BIC/AZSDOA65900','/BIC/AZSDOA66300','/BIC/AZSDOA66400','/BIC/AZSDOA66500','/BIC/AZSDOA66700','/BIC/AZSDOA66800','/BIC/AZSDOA66900','/BIC/AZSDOA67100','/BIC/AZSDOA67200','/BIC/AZSDOA67300','/BIC/AZSDOA67500','/BIC/AZSDOA67600','/BIC/AZSDOA67700','/BIC/AZSDOA67900','/BIC/AZSDOA68000','/BIC/AZSDOA68100','/BIC/AZSDOA68100','/BIC/AZSDOA68800','/BIC/AZSDOA68900')
			);
	select max("ROW_NUM") as n into counter from #ZPSA_TEMP;
	FOR i in 1..:counter DO
	    SELECT '"SAPBIW".'||'"'||"TABLE_NAME"||'"' as TABNAME into w_table from #ZPSA_TEMP WHERE "ROW_NUM"= :pos;
	    EXECUTE IMMEDIATE 'select * from '||:w_table|| 'where "VAL_END">='||:dat ;
		pos=:pos+1;
	END FOR;
	
	DROP TABLE #ZPSA_TEMP;
END;

call ZPSAEXPORT('20991231');

[lbreddemann]

Florian has already pointed out that it is not quite clear what this procedure should be doing.

I've got some suspicion on that, but what I rather want to discuss is that the solution presented is too complicated and inefficient.

The whole thing apparently looks up a bunch of BW tables from the database catalog and then runs a SELECT * on each of the tables found with some date condition.

To do this there is no need to use temporary tables and manual counter keeping. One could just write the following:

do begin
declare dat varchar(8) = '20991231';
declare cursor export_tabs for
         select table_name
          from "TABLES"
          where 
              schema_name = 'SAPBIW'
          and table_name in 
            ( '/BIC/AZSDOA62600', '/BIC/AZSDOA65400'
            , '/BIC/AZSDOA65500', '/BIC/AZSDOA65900'
            , '/BIC/AZSDOA66300', '/BIC/AZSDOA66400'
            , '/BIC/AZSDOA66500', '/BIC/AZSDOA66700'
            , '/BIC/AZSDOA66800', '/BIC/AZSDOA66900'
            , '/BIC/AZSDOA67100', '/BIC/AZSDOA67200'
            , '/BIC/AZSDOA67300', '/BIC/AZSDOA67500'
            , '/BIC/AZSDOA67600', '/BIC/AZSDOA67700'
            , '/BIC/AZSDOA67900', '/BIC/AZSDOA68000'
            , '/BIC/AZSDOA68100', '/BIC/AZSDOA68100'
            , '/BIC/AZSDOA68800', '/BIC/AZSDOA68900');

    for cur_tab as export_tabs do     
        EXECUTE IMMEDIATE   'select * from '
                          ||'"SAPBIW"."'|| cur_tab.table_name ||'" '
                          ||' where "VAL_END">= ''''||:dat ||'''
                           ;
    end for;
end;

By using the cursor and the FOR LOOP no intermediate table variables or temporary tables are required and SQL Script handles the whole looping over action.

The filter condition for VAL_END needs to be enclosed in single quotes since this is a character column - otherwise, the statement would depend on implicit type conversion which is generally not a good thing.
Also, when using the database catalog tables, avoid specifying the SYS schema. Instead, use the PUBLIC schema (or simply leave the schema declaration out).

[pfefferf]

Please can you explain what you wanna reach? I assume you wanna do a dynamic select on all tables you store in the temporary table, correct?

But you are not doing that. You just creating the schema/table name combination for all tables within the temporary table. So basically you just receive strings. On that result set which just has a TABLENAME column, you try to apply a where-clause on field VAL_END (which is not part of the result set on which you apply the restricted select).