0

I am working on a DB2 SQL code.

I need to get the count of records from a list of tables. The table details will be fetched from a cursor with select. To get the count of records I have been trying with a SELECT INTO statement. Since the table names will be varying, I am using a dynamic SQL code. I am sharing the piece of code that I have been trying. I am not quite sure of the syntax while using DB2 SELECT INTO and Dynamic SQL combination. I am getting the following error with the below attempt.

Can anyone tell me why this is so? If possible, appreciate if you could share a working code of DB2 select into and dynamic sql.

SQL0104N An unexpected token "statmnt2" was found following "SET statmnt2 := 'set ? = (SELECT COD_TIPO_ARQU FROM '||indbn". Expected tokens may include: "".

DECLARE
indbnm                  VARCHAR(30);
intblnm                 VARCHAR(30);
v_errorText             varchar2(50);
statmnt2                VARCHAR(1000);
VAR_COD_TIPO_ARQU       CHAR(1);
stmt1                   STATEMENT;
statmnt2                VARCCHAR2(100);

BEGIN

indbnm := "db2inst5";
intblnm:= "rules";

SET statmnt2 := 'set ? = (SELECT COD_TIPO_ARQU FROM '||indbnm||'.'||intblnm||' FETCH FIRST 1 ROWS ONLY)';
PREPARE stmt1 FROM statmnt2;
EXECUTE stmt1 into VAR_COD_TIPO_ARQU ;


DBMS_OUTPUT.PUT_LINE(VAR_COD_TIPO_ARQU);
    EXCEPTION
    WHEN OTHERS THEN
            v_errorText :=SUBSTR(SQLERRM,1, 1024);
            DBMS_OUTPUT.PUT_LINE('FAILED WITH MESSAGE: '||v_errorText);

END;
Mel
  • 5,837
  • 10
  • 37
  • 42
Krish
  • 1
  • 1
  • Is the typo "VARCCHAR2" in your code or in the sample above? – data_henrik Jan 26 '16 at 11:24
  • Hi @data_henrik you are correct. It was in my code. I correct it, but still am facing the same error. Error message "SQL0104N An unexpected token "statmnt2" was found following "SET statmnt2 := 'set ? = (SELECT COD_TIPO_ARQU FROM '||indbn". Expected tokens may include: ""." – Krish Jan 26 '16 at 11:40
  • From my research in the web I got some inputs that I need to use PREPARE with an OUTPUT clause, and then the EXECUTE with an INTO statement. I haven't got the proper syntax of it yet, although I spent quite some time looking for it. I will be happy if anyone could share a successfully working example DB2 code which uses SELECT INTO with Dynamic SQL for a variable tablename. – Krish Jan 26 '16 at 11:45
  • You are mixing up SQL PL and PL/SQL syntax in the same procedure, which is not supported. Choose one. – mustaccio Jan 26 '16 at 13:30
  • I haven't worked much on DB2. Could you please share a sample DB2 PLSQL code which uses SELECT INTO clause along with DYNAMIC SQL. I just need the right syntax which works. I am trying to get count of records from a set of tables (which will vary) for each set of conditions. For condition X, the tables to check might be ,say 2, and for Condition Y, the list will be a different set of tables. There are about 11 conditions. – Krish Jan 26 '16 at 14:12

0 Answers0