Results 1 to 10 of 10
  1. #1
    Join Date
    Jan 2005
    Posts
    10

    Unanswered: using input variable in dynamic sql

    hi, i wanna do 'select into variable' in dynamic sql.
    but i got compile error
    plz help

    for example:
    set str = 'select count(*) into var where..... (var is a variable)
    execute immediate str

    how do I do that?

    Thanks in advance

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    What programming language (or facility) are you using. What is the datatype for the var (I believe it needs to be int or bigint).
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    Jan 2005
    Posts
    10
    I need to do it in the stored procedure. not from application program

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    I assume you are using an SQL stored procedure.

    Where is your "FROM" clause?

    Did you define the variable first?
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  5. #5
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by vcorn
    how do I do that?
    You can't. "SELECT INTO" is not a dynamic statement.
    SELECT INTO statement

    The SELECT INTO statement produces a result table consisting of at most one row, and assigns the values in that row to host variables. If the table is empty, the statement assigns +100 to SQLCODE and '02000' to SQLSTATE and does not assign values to the host variables. If more than one row satisfies the search condition, statement processing is terminated, and an error occurs (SQLSTATE 21000).
    Invocation

    This statement can be embedded only in an application program. It is an executable statement that cannot be dynamically prepared.

  6. #6
    Join Date
    Jan 2005
    Posts
    10
    So, can I actually use select into to produce a table with one row in the stored procedure instead of embedding it in application program?

    Thanks

  7. #7
    Join Date
    Jan 2005
    Posts
    191
    Not as such. However you can use
    DECLARE GLOBAL TEMPORARY TABLE mycount (myrowcount integer)
    SET str = 'INSERT INTO SESSION.mycount SELECT COUNT(*) FROM ... WHERE ...'
    EXECUTE IMMEDIATE str
    SELECT myrowcount INTO var FROM SESSION.mycount


    James Campbell

  8. #8
    Join Date
    Jan 2005
    Posts
    10
    I got exception when i try to run this:

    DECLARE RC CURSOR WITH RETURN FOR
    SELECT * FROM SESSION.TEMP;

    DECLARE GLOBAL TEMPORARY TABLE SESSION.TEMP(TOT INTEGER) ON COMMIT PRESERVE ROWS;
    INSERT INTO SESSION.TEMP SELECT COUNT(*) FROM IRNI.A_RT_SENTINEL_LOG;

    OPEN RC;

    Error message:
    Exception occurred while running:
    "A database manager error occurred.[IBM][CLI Driver][DB2] SQL0873N Objects encoded with different encoding schemes cannot be referenced in the same SQL statement. SQLSTATE=53090"

    Please help.
    Thanks

  9. #9
    Join Date
    Jan 2005
    Posts
    191
    As a wild guess, was IRNI.A_RT_SENTINEL_LOG created with CCSID UNICODE while the database was created using some other codeset? Or the other way around. Not a good thing to do - read all the restrictions.

    You might have to do something like:
    declare myint integer;

    DECLARE RC CURSOR WITH RETURN FOR
    SELECT * FROM SESSION.TEMP;

    DECLARE GLOBAL TEMPORARY TABLE SESSION.TEMP(TOT INTEGER) ON COMMIT PRESERVE ROWS;
    SELECT COUNT(*) into myint FROM IRNI.A_RT_SENTINEL_LOG;

    INSERT INTO SESSION.TEMP values (myint);

    OPEN RC;

    James Campbell

  10. #10
    Join Date
    Jan 2005
    Posts
    10

    Smile

    Thanks jacampbell,
    i try your suggestion, by inputing into single var myint and it works fine.

    Again,thx

Posting Permissions

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