Results 1 to 6 of 6

Thread: Dynamic SQL

  1. #1
    Join Date
    May 2008
    Posts
    39

    Unanswered: Dynamic SQL

    --=======================================

    SET SCOUNT_STMT = 'SELECT COUNT(*) INTO '''|| Char(SCOUNT) || ''' FROM '||SCHEMA_NAME||'.RTBL RTBL,'||SCHEMA_NAME||'.ETL_LINEAGE A
    WHERE A.DM_LINEAGE_CREATE_TS = '''|| CAST(RUN_DT AS CHAR(26) ) ||''' AND
    A.PROCESS_NM = '''||PROCESS_NAME||'''';

    --PREPARE SCOUNT_STMT1 FROM SCOUNT_STMT;
    EXECUTE IMMEDIATE SCOUNT_STMT;
    SET SOURCE_COUNT = SCOUNT;

    Hi

    I try to execute the above statement.There are around 12 rows in my table.But I get the below err.
    SQL0198N The statement string of the PREPARE or EXECUTE IMMEDIATE statement
    is blank or empty. SQLSTATE=42617


    Statement processed with ERROR.
    Please help
    Thanks

  2. #2
    Join Date
    Dec 2005
    Posts
    273
    correct me if I'm wrong, but

    SELECT INTO is not valid for dynamic sql. You have to use a cursor.


    by the way:
    There are two tables in your query ( RTBL and ETL_LINEAGE ), but there is no join condition.

  3. #3
    Join Date
    May 2008
    Posts
    39
    Am passing that as parameter,those records are fecthed from ETL lineage tbl...handled in script.

    Can you pls give more idea on handling it cursor(count into)

  4. #4
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  5. #5
    Join Date
    May 2008
    Posts
    39
    Still am facing same issue after replacing with cursor

  6. #6
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    You should have a look at the final statement that's being generated. Pass it as an OUT parameter back from your stored procedure. Maybe you have a schema name that requires delimiters, maybe something else.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •