Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2014
    Posts
    294

    Unanswered: QUOTES around concatanation

    Hi,

    My procedure is FAILING due to the quotes . I tried a lot, but unable to place the quotes around the run time value.

    this is my statement,

    SET STMT =('SELECT CARD FROM SYSIBM.SYSTABLES WHERE NAME = ' concat TB_NAME );

    where TB_NAME is run time value.
    this is the output which I can see

    db2 => CALL WHILE_META();
    SQL0206N "ORG" is not valid in the context where it is used. SQLSTATE=42703

    SELECT CARD FROM SYSIBM.SYSTABLES WHERE NAME = ORG

    I need to place quotes around ORG as 'ORG'. kindly help me how to place quotes on my SET statement.

  2. #2
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    this question has been discussed many times in the forum..
    many newbies, that have never used DB2, and maybe can not read the english doc, do not know that you have to code triple quote..
    and we stupid dba still answer these questions...
    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

  3. #3
    Join Date
    Jul 2013
    Location
    Moscow, Russia
    Posts
    666
    Provided Answers: 55
    Hi,

    try this:

    Code:
    set serveroutput on@
    
    begin
      declare l_card int;
      declare l_stmt varchar(128);
    
      set l_stmt = 'set ? = (select card from sysibm.systables where name=? and creator=?)';
      prepare s_stmt from l_stmt;
    
      for c1 as 
      select name, creator from sysibm.systables where creator='SYSIBM'
      do
        -- static
        select card into l_card
        from sysibm.systables
        where name=c1.name and creator=c1.creator;
        call dbms_output.put_line(l_card || ' (s) ' || c1.name);
    
        -- dynamic with parameters
        execute s_stmt into l_card using c1.name, c1.creator;
        call dbms_output.put_line(l_card || ' (d1) ' || c1.name);
    
        -- dynamic w/o parameters
        set l_stmt = 'set ? = (select card from sysibm.systables where name=''' || c1.name || ''' and creator=''' || c1.creator ||''')';
        prepare s_stmt2 from l_stmt;
        execute s_stmt2 into l_card;
        call dbms_output.put_line(l_card || ' (d2) ' || c1.name);
      end for;
     
    end@
    BTW, I would not recommend you to use the tables in SYSIBM schema.
    Use the correspondent views in the SYSCAT schema instead.
    For example, SYSIBM.SYSTABLES -> SYSCAT.TABLES
    Regards,
    Mark.

  4. #4
    Join Date
    Jul 2014
    Posts
    294
    Thanks Mark.

Tags for this Thread

Posting Permissions

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