Skip to Content
-2

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

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?

Add comment
10|10000 characters needed characters exceeded

  • Follow
  • Get RSS Feed

3 Answers

  • Best Answer
    Jan 30 at 04:51 PM
    -1

    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');
    
    Add comment
    10|10000 characters needed characters exceeded

  • Jan 30 at 04:21 PM

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

    Add comment
    10|10000 characters needed characters exceeded

  • Jan 31 at 05:27 AM

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

    Add comment
    10|10000 characters needed characters exceeded