Solved

Execute Immediate not working

Posted on 2011-03-21
11
968 Views
Last Modified: 2012-05-11

Hi I'm on DB2 UDB 8.2.
I'm trying to display from the code, using Execute Immediate. But it is throwing this error
SQL0084N  An EXECUTE IMMEDIATE statement contains a SELECT or VALUES statement.
Please help.

 
CREATE PROCEDURE CHESSIE.SIMPLE_test (IN varTABNAME VARCHAR(50),
                                IN varcolname varchar(50),
                                IN varKEY_ID INTEGER,
                                IN varADR_TYPE_CD VARCHAR(5),
                                IN tofrom varchar(2),
                                OUT outtxt varchar(1500))

    LANGUAGE SQL
BEGIN

declare f_final varchar(1500) default '';--
declare lb_po_box varchar(1);--

declare stmt varchar(3000);--

declare  t_ADDRESS_ID           INTEGER;   --
declare  t_ADR_FORMAT_CD        VARCHAR(15)   default '';--
declare  t_EMP_SCHOOL_NM        VARCHAR(50)   default '';--
declare  t_ADR_STREET_NO        VARCHAR(10)    default '';--
declare  t_ADR_BOX_NO            VARCHAR(10)   default '';--
declare  t_ADR_PRE_DIR_CD       VARCHAR(100)  default '';--
declare  t_ADR_STREET_NM        VARCHAR(50)   default '';--
declare  t_ADR_STREET_SX_CD VARCHAR(100)  default '';--
declare  t_ADR_POST_DIR_CD      VARCHAR(100)  default '';--
declare  t_ADR_UNIT_TYPE_CD     VARCHAR(100)  default '';--
declare  t_ADR_UNIT_NO_TX       VARCHAR(5)    default '';--
declare  t_ADR_CITY_NM          VARCHAR(50)   default '';   --
declare  t_ADR_COUNTY_CD        VARCHAR(5)    default '';--
declare  t_ADR_STATE_CD         VARCHAR(100)  default '';   --
declare  t_ADR_ZIP5_NO          VARCHAR(10)    default '';   --
declare  t_ADR_ZIP4_NO          VARCHAR(10)    default ''; --
declare  t_ADR_DIRECTION_TX     VARCHAR(500)  default '';    --
declare  t_ADR_FOREIGN_TX       VARCHAR(500)  default '';   --
declare  t_ADR_FOREIGN_STTX VARCHAR(50)   default '';   --
declare  t_ADR_COUNTRY_TX       VARCHAR(50)   default '';   --
declare  t_ADR_POSTAL_CD_TX   VARCHAR(10)   default '';--

----
declare f_adr_street_NM VARCHAR(50)          default '';--
declare f_ADR_STREET_NO VARCHAR(10)           default '';--
declare f_ADR_PRE_DIR_CD VARCHAR(100)        default '';--
declare f_ADR_STREET_SUFFIX_CD VARCHAR(100)  default '';--
declare f_ADR_FORMAT_CD     VARCHAR(10)      default '';--
declare f_ADR_UNIT_TYPE_CD varchar(100)      default '';--
declare f_ADR_UNIT_NO_TX      varchar(5)     default '';--
declare f_ADR_CITY_NM          VARCHAR(50)   default '';   --
declare f_ADR_COUNTY_CD        VARCHAR(5)    default '';--
declare f_ADR_STATE_CD         VARCHAR(100)  default '';   --
declare f_ADR_ZIP5_NO          VARCHAR(10)    default '';   --
declare f_ADR_ZIP4_NO          VARCHAR(10)    default ''; --
declare f_ADR_DIRECTION_TX     VARCHAR(500)  default '';  --
declare f_ADR_FOREIGN_TX       VARCHAR(500)  default '';   --
declare f_ADR_FOREIGN_STATE_TX VARCHAR(50)   default '';   --
declare f_ADR_COUNTRY_TX       VARCHAR(50)   default '';   --
declare f_ADR_POSTAL_CODE_TX   VARCHAR(10)   default '';--

    set stmt = ' SELECT    ' ||
         ' COALESCE(CHAR(ADR_FORMAT_CD) ,'''')          as  t_ADR_FORMAT_CD, ' ||
         ' COALESCE(CHAR(ADR_STREET_TX) ,'''')          as  t_ADR_STREET_NO,  ' ||
         ' COALESCE(CHAR(ADR_BOX_NO)    ,'''' )         as  t_ADR_BOX_NO,   ' ||
         ' COALESCE(F_PDESC(ADR_PRE_DIR_CD,69),'''')    as  t_ADR_PRE_DIR_CD,  ' ||
         ' COALESCE(ADR_STREET_NM,'''')                 as  t_ADR_STREET_NM,   ' ||
         ' COALESCE(F_PDESC(ADR_STREET_SUFFIX_CD,212),'''') AS t_ADR_STREET_SX_CD, ' ||
         ' COALESCE(F_PDESC(ADR_POST_DIR_CD,69) ,'''')  as   t_ADR_POST_DIR_CD,  ' ||
         ' COALESCE(F_PDESC(ADR_UNIT_TYPE_CD,250),'''') as  t_ADR_UNIT_TYPE_CD,  ' ||
         ' COALESCE(ADR_UNIT_NO_TX ,'''')               as  t_ADR_UNIT_NO_TX,   ' ||
         ' COALESCE(ADR_CITY_NM ,'''')                  as  t_ADR_CITY_NM,   '  ||
         ' COALESCE(ADR_COUNTY_CD ,'''')                as  t_ADR_COUNTY_CD, '  ||
         ' COALESCE(F_PDESC(ADR_STATE_CD,211) ,'''')    as  t_ADR_STATE_CD,  '  ||
         ' COALESCE(CHAR(integer(ADR_ZIP5_NO)) ,'''')            as  t_ADR_ZIP5_NO,   '  ||
         ' COALESCE(CHAR(integer(ADR_ZIP4_NO)) ,'''')            as  t_ADR_ZIP4_NO   '  ;--

            set stmt = stmt || ' FROM ' || vartabname  ||
            ' WHERE ' || varcolname || ' = ' ||  char(varKEY_ID) || ' and DELETE_SW = ''N'' ';--
            EXECUTE IMMEDIATE STMT;
        END 

Open in new window

0
Comment
Question by:pvsbandi
  • 5
  • 3
  • 3
11 Comments
 
LVL 18

Expert Comment

by:daveslash
ID: 35181976

It appears that you intend to select * bunch of stuff from a table, but you're doing anything with it. You'll need to either use "SELECT ... INTO" or possibly an "INSERT ... SELECT".

HTH,
DaveSlash
0
 
LVL 37

Expert Comment

by:momi_sabag
ID: 35182170
0
 

Author Comment

by:pvsbandi
ID: 35182644
Thank you! Basically, i have this SQL which has a problem when there is a null value for the variable ADR_ZIP4_NO. I want to see what the variable stmt returns.

   This result is actually passed onto another procedure, where further formatting is done.
    So, how can i output the variable stmt? Please advise.
0
 
LVL 37

Expert Comment

by:momi_sabag
ID: 35182693
the easiest way will probably be to insert it into a table
0
 
LVL 18

Accepted Solution

by:
daveslash earned 500 total points
ID: 35182733

I agree with Momi ... especially since my earlier suggestion of using "SELECT ... INTO" won't really work. Host variables are not valid in dynamic SQL.

-- DaveSlash
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:pvsbandi
ID: 35182880
Pardon my ignorance, but how do i dump the values into the temp table? My trial is added.

declare c1 cursor for s1;--
declare global temporary table ABC(  t_ADR_FORMAT_CD        VARCHAR(15),
  t_EMP_SCHOOL_NM        VARCHAR(50),
t_ADR_STREET_NO        VARCHAR(10),
  t_ADR_BOX_NO            VARCHAR(10),
 t_ADR_PRE_DIR_CD       VARCHAR(100),
 t_ADR_STREET_NM        VARCHAR(50),
 t_ADR_STREET_SX_CD VARCHAR(100) ,
  t_ADR_POST_DIR_CD      VARCHAR(100) ,
 t_ADR_UNIT_TYPE_CD     VARCHAR(100),
 t_ADR_UNIT_NO_TX       VARCHAR(5),
 t_ADR_CITY_NM          VARCHAR(50),
 t_ADR_COUNTY_CD        VARCHAR(5) ,
  t_ADR_STATE_CD         VARCHAR(100),
 t_ADR_ZIP5_NO          VARCHAR(10) ,
  t_ADR_ZIP4_NO          VARCHAR(10) ,
  t_ADR_DIRECTION_TX     VARCHAR(500),
  t_ADR_FOREIGN_TX       VARCHAR(500),
  t_ADR_FOREIGN_STTX VARCHAR(50),
  t_ADR_COUNTRY_TX       VARCHAR(50),
  t_ADR_POSTAL_CD_TX   VARCHAR(10) )
  with replace
  on commit preserve rows;  

  set stmt = ' SELECT    ' ||
         ' COALESCE(CHAR(ADR_FORMAT_CD) ,'''')          as  t_ADR_FORMAT_CD, ' ||
         ' COALESCE(CHAR(ADR_STREET_TX) ,'''')          as  t_ADR_STREET_NO,  ' ||
         ' COALESCE(CHAR(ADR_BOX_NO)    ,'''' )         as  t_ADR_BOX_NO,   ' ||
         ' COALESCE(F_PDESC(ADR_PRE_DIR_CD,69),'''')    as  t_ADR_PRE_DIR_CD,  ' ||
         ' COALESCE(ADR_STREET_NM,'''')                 as  t_ADR_STREET_NM,   ' ||
         ' COALESCE(F_PDESC(ADR_STREET_SUFFIX_CD,212),'''') AS t_ADR_STREET_SX_CD, ' ||
         ' COALESCE(F_PDESC(ADR_POST_DIR_CD,69) ,'''')  as   t_ADR_POST_DIR_CD,  ' ||
         ' COALESCE(F_PDESC(ADR_UNIT_TYPE_CD,250),'''') as  t_ADR_UNIT_TYPE_CD,  ' ||
         ' COALESCE(ADR_UNIT_NO_TX ,'''')               as  t_ADR_UNIT_NO_TX,   ' ||
         ' COALESCE(ADR_CITY_NM ,'''')                  as  t_ADR_CITY_NM,   '  ||
         ' COALESCE(ADR_COUNTY_CD ,'''')                as  t_ADR_COUNTY_CD, '  ||
         ' COALESCE(F_PDESC(ADR_STATE_CD,211) ,'''')    as  t_ADR_STATE_CD,  '  ||
         ' COALESCE(CHAR(integer(ADR_ZIP5_NO)) ,'''')            as  t_ADR_ZIP5_NO,   '  ||
         ' COALESCE(CHAR(integer(ADR_ZIP4_NO)) ,'''')            as  t_ADR_ZIP4_NO   '  ;--

           set stmt = stmt || ' FROM ' || vartabname  ||
            ' WHERE ' || varcolname || ' = ' ||  char(varKEY_ID) || ' and DELETE_SW = ''N'' ';--
      
            
    prepare s1 from stmt;--
open c1;--
fetch c1 into t_ADR_FORMAT_CD,t_ADR_STREET_NO,t_ADR_BOX_NO,t_ADR_PRE_DIR_CD,t_ADR_STREET_NM,
              t_ADR_STREET_SX_CD,t_ADR_POST_DIR_CD,t_ADR_UNIT_TYPE_CD,t_ADR_UNIT_NO_TX,
              t_ADR_CITY_NM,t_ADR_COUNTY_CD,t_ADR_STATE_CD,t_ADR_ZIP5_NO,t_ADR_ZIP4_NO,
              t_ADR_DIRECTION_TX,t_ADR_FOREIGN_TX,t_ADR_FOREIGN_STTX,t_ADR_COUNTRY_TX,
              t_ADR_POSTAL_CD_TX ;--

                                                        

Open in new window

0
 

Author Comment

by:pvsbandi
ID: 35182896
declare c1 cursor for s1;--

The above line should be there on top
0
 
LVL 18

Assisted Solution

by:daveslash
daveslash earned 500 total points
ID: 35182913

You'd simply change your SELECT statement into an "INSERT ... SELECT" statement.

HTH,
DaveSlash
0
 

Author Comment

by:pvsbandi
ID: 35182989
I'm missing something. Can you please help? My revised code.
CHESSIE.SIMPLE_test (IN varTABNAME VARCHAR(50),
                                IN varcolname varchar(50),
                                IN varKEY_ID INTEGER,
                                IN varADR_TYPE_CD VARCHAR(5),
                                IN tofrom varchar(2),
                                OUT outtxt varchar(1500))

    LANGUAGE SQL
-- Jan 6 2009: Recent Changes, to get client's current and incident address
--             there is no table on any of these names 'TB_CLIENT_ADDRESSES_CUR,TB_CLIENT_ADDRESSES_INC,TB_STAFF_LOC'
--             these names have been used to get current,incident and location addresses for client and staff
BEGIN

declare f_final varchar(1500) default '';--
declare lb_po_box varchar(1);--

declare stmt varchar(3000);--

declare  t_ADDRESS_ID           INTEGER;   --
declare  t_ADR_FORMAT_CD        VARCHAR(15)   default '';--
declare  t_EMP_SCHOOL_NM        VARCHAR(50)   default '';--
declare  t_ADR_STREET_NO        VARCHAR(10)    default '';--
declare  t_ADR_BOX_NO            VARCHAR(10)   default '';--
declare  t_ADR_PRE_DIR_CD       VARCHAR(100)  default '';--
declare  t_ADR_STREET_NM        VARCHAR(50)   default '';--
declare  t_ADR_STREET_SX_CD VARCHAR(100)  default '';--
declare  t_ADR_POST_DIR_CD      VARCHAR(100)  default '';--
declare  t_ADR_UNIT_TYPE_CD     VARCHAR(100)  default '';--
declare  t_ADR_UNIT_NO_TX       VARCHAR(5)    default '';--
declare  t_ADR_CITY_NM          VARCHAR(50)   default '';   --
declare  t_ADR_COUNTY_CD        VARCHAR(5)    default '';--
declare  t_ADR_STATE_CD         VARCHAR(100)  default '';   --
declare  t_ADR_ZIP5_NO          VARCHAR(10)    default '';   --
declare  t_ADR_ZIP4_NO          VARCHAR(10)    default ''; --
declare  t_ADR_DIRECTION_TX     VARCHAR(500)  default '';    --
declare  t_ADR_FOREIGN_TX       VARCHAR(500)  default '';   --
declare  t_ADR_FOREIGN_STTX VARCHAR(50)   default '';   --
declare  t_ADR_COUNTRY_TX       VARCHAR(50)   default '';   --
declare  t_ADR_POSTAL_CD_TX   VARCHAR(10)   default '';--

----
declare f_adr_street_NM VARCHAR(50)          default '';--
declare f_ADR_STREET_NO VARCHAR(10)           default '';--
declare f_ADR_PRE_DIR_CD VARCHAR(100)        default '';--
declare f_ADR_STREET_SUFFIX_CD VARCHAR(100)  default '';--
declare f_ADR_FORMAT_CD     VARCHAR(10)      default '';--
declare f_ADR_UNIT_TYPE_CD varchar(100)      default '';--
declare f_ADR_UNIT_NO_TX      varchar(5)     default '';--
declare f_ADR_CITY_NM          VARCHAR(50)   default '';   --
declare f_ADR_COUNTY_CD        VARCHAR(5)    default '';--
declare f_ADR_STATE_CD         VARCHAR(100)  default '';   --
declare f_ADR_ZIP5_NO          VARCHAR(10)    default '';   --
declare f_ADR_ZIP4_NO          VARCHAR(10)    default ''; --
declare f_ADR_DIRECTION_TX     VARCHAR(500)  default '';  --
declare f_ADR_FOREIGN_TX       VARCHAR(500)  default '';   --
declare f_ADR_FOREIGN_STATE_TX VARCHAR(50)   default '';   --
declare f_ADR_COUNTRY_TX       VARCHAR(50)   default '';   --
declare f_ADR_POSTAL_CODE_TX   VARCHAR(10)   default '';--


declare c1 cursor for s1;--
declare global temporary table ABC(  t_ADR_FORMAT_CD        VARCHAR(15),
  t_EMP_SCHOOL_NM        VARCHAR(50),
t_ADR_STREET_NO        VARCHAR(10),
  t_ADR_BOX_NO            VARCHAR(10),
 t_ADR_PRE_DIR_CD       VARCHAR(100),
 t_ADR_STREET_NM        VARCHAR(50),
 t_ADR_STREET_SX_CD VARCHAR(100) ,
  t_ADR_POST_DIR_CD      VARCHAR(100) ,
 t_ADR_UNIT_TYPE_CD     VARCHAR(100),
 t_ADR_UNIT_NO_TX       VARCHAR(5),
 t_ADR_CITY_NM          VARCHAR(50),
 t_ADR_COUNTY_CD        VARCHAR(5) ,
  t_ADR_STATE_CD         VARCHAR(100),
 t_ADR_ZIP5_NO          VARCHAR(10) ,
  t_ADR_ZIP4_NO          VARCHAR(10) ,
  t_ADR_DIRECTION_TX     VARCHAR(500),
  t_ADR_FOREIGN_TX       VARCHAR(500),
  t_ADR_FOREIGN_STTX VARCHAR(50),
  t_ADR_COUNTRY_TX       VARCHAR(50),
  t_ADR_POSTAL_CD_TX   VARCHAR(10) )
  with replace
  on commit preserve rows;

  set stmt = 'INSERT INTO ' || SESSION.ABC|| ' SELECT    ' ||
         ' COALESCE(CHAR(ADR_FORMAT_CD) ,'''')          as  t_ADR_FORMAT_CD, ' ||
         ' COALESCE(CHAR(ADR_STREET_TX) ,'''')          as  t_ADR_STREET_NO,  ' ||
         ' COALESCE(CHAR(ADR_BOX_NO)    ,'''' )         as  t_ADR_BOX_NO,   ' ||
         ' COALESCE(F_PDESC(ADR_PRE_DIR_CD,69),'''')    as  t_ADR_PRE_DIR_CD,  ' ||
         ' COALESCE(ADR_STREET_NM,'''')                 as  t_ADR_STREET_NM,   ' ||
         ' COALESCE(F_PDESC(ADR_STREET_SUFFIX_CD,212),'''') AS t_ADR_STREET_SX_CD, ' ||
         ' COALESCE(F_PDESC(ADR_POST_DIR_CD,69) ,'''')  as   t_ADR_POST_DIR_CD,  ' ||
         ' COALESCE(F_PDESC(ADR_UNIT_TYPE_CD,250),'''') as  t_ADR_UNIT_TYPE_CD,  ' ||
         ' COALESCE(ADR_UNIT_NO_TX ,'''')               as  t_ADR_UNIT_NO_TX,   ' ||
         ' COALESCE(ADR_CITY_NM ,'''')                  as  t_ADR_CITY_NM,   '  ||
         ' COALESCE(ADR_COUNTY_CD ,'''')                as  t_ADR_COUNTY_CD, '  ||
         ' COALESCE(F_PDESC(ADR_STATE_CD,211) ,'''')    as  t_ADR_STATE_CD,  '  ||
         ' COALESCE(CHAR(integer(ADR_ZIP5_NO)) ,'''')            as  t_ADR_ZIP5_NO,   '  ||
         ' COALESCE(CHAR(integer(ADR_ZIP4_NO)) ,'''')            as  t_ADR_ZIP4_NO   '  ;--

           set stmt = stmt || ' FROM ' || vartabname  ||
            ' WHERE ' || varcolname || ' = ' ||  char(varKEY_ID) || ' and DELETE_SW = ''N'' ';--

      SELECT * FROM SESSION.ABC;
        END                              

Open in new window

0
 
LVL 37

Expert Comment

by:momi_sabag
ID: 35183065
what happens when you execute this procedure
0
 

Author Comment

by:pvsbandi
ID: 35183090
I have corrected somethings in my earlier SQL ( Code added). But says, session.abc is not valid in the context.

CREATE PROCEDURE CHESSIE.SIMPLE_test (IN varTABNAME VARCHAR(50),
                                IN varcolname varchar(50),
                                IN varKEY_ID INTEGER,
                                IN varADR_TYPE_CD VARCHAR(5),
                                IN tofrom varchar(2),
                                OUT outtxt varchar(1500))

    LANGUAGE SQL
-- Jan 6 2009: Recent Changes, to get client's current and incident address
--             there is no table on any of these names 'TB_CLIENT_ADDRESSES_CUR,TB_CLIENT_ADDRESSES_INC,TB_STAFF_LOC'
--             these names have been used to get current,incident and location addresses for client and staff
BEGIN

declare f_final varchar(1500) default '';--
declare lb_po_box varchar(1);--

declare stmt varchar(3000);--

declare  t_ADDRESS_ID           INTEGER;   --
declare  t_ADR_FORMAT_CD        VARCHAR(15)   default '';--
declare  t_EMP_SCHOOL_NM        VARCHAR(50)   default '';--
declare  t_ADR_STREET_NO        VARCHAR(10)    default '';--
declare  t_ADR_BOX_NO            VARCHAR(10)   default '';--
declare  t_ADR_PRE_DIR_CD       VARCHAR(100)  default '';--
declare  t_ADR_STREET_NM        VARCHAR(50)   default '';--
declare  t_ADR_STREET_SX_CD VARCHAR(100)  default '';--
declare  t_ADR_POST_DIR_CD      VARCHAR(100)  default '';--
declare  t_ADR_UNIT_TYPE_CD     VARCHAR(100)  default '';--
declare  t_ADR_UNIT_NO_TX       VARCHAR(5)    default '';--
declare  t_ADR_CITY_NM          VARCHAR(50)   default '';   --
declare  t_ADR_COUNTY_CD        VARCHAR(5)    default '';--
declare  t_ADR_STATE_CD         VARCHAR(100)  default '';   --
declare  t_ADR_ZIP5_NO          VARCHAR(10)    default '';   --
declare  t_ADR_ZIP4_NO          VARCHAR(10)    default ''; --
declare  t_ADR_DIRECTION_TX     VARCHAR(500)  default '';    --
declare  t_ADR_FOREIGN_TX       VARCHAR(500)  default '';   --
declare  t_ADR_FOREIGN_STTX VARCHAR(50)   default '';   --
declare  t_ADR_COUNTRY_TX       VARCHAR(50)   default '';   --
declare  t_ADR_POSTAL_CD_TX   VARCHAR(10)   default '';--

----
declare f_adr_street_NM VARCHAR(50)          default '';--
declare f_ADR_STREET_NO VARCHAR(10)           default '';--
declare f_ADR_PRE_DIR_CD VARCHAR(100)        default '';--
declare f_ADR_STREET_SUFFIX_CD VARCHAR(100)  default '';--
declare f_ADR_FORMAT_CD     VARCHAR(10)      default '';--
declare f_ADR_UNIT_TYPE_CD varchar(100)      default '';--
declare f_ADR_UNIT_NO_TX      varchar(5)     default '';--
declare f_ADR_CITY_NM          VARCHAR(50)   default '';   --
declare f_ADR_COUNTY_CD        VARCHAR(5)    default '';--
declare f_ADR_STATE_CD         VARCHAR(100)  default '';   --
declare f_ADR_ZIP5_NO          VARCHAR(10)    default '';   --
declare f_ADR_ZIP4_NO          VARCHAR(10)    default ''; --
declare f_ADR_DIRECTION_TX     VARCHAR(500)  default '';  --
declare f_ADR_FOREIGN_TX       VARCHAR(500)  default '';   --
declare f_ADR_FOREIGN_STATE_TX VARCHAR(50)   default '';   --
declare f_ADR_COUNTRY_TX       VARCHAR(50)   default '';   --
declare f_ADR_POSTAL_CODE_TX   VARCHAR(10)   default '';--


declare c1 cursor for s1;--
declare global temporary table ABC(  t_ADR_FORMAT_CD        VARCHAR(15),
t_ADR_STREET_NO        VARCHAR(10),
  t_ADR_BOX_NO            VARCHAR(10),
 t_ADR_PRE_DIR_CD       VARCHAR(100),
 t_ADR_STREET_NM        VARCHAR(50),
 t_ADR_STREET_SX_CD VARCHAR(100) ,
  t_ADR_POST_DIR_CD      VARCHAR(100) ,
 t_ADR_UNIT_TYPE_CD     VARCHAR(100),
 t_ADR_UNIT_NO_TX       VARCHAR(5),
 t_ADR_CITY_NM          VARCHAR(50),
 t_ADR_COUNTY_CD        VARCHAR(5) ,
  t_ADR_STATE_CD         VARCHAR(100),
 t_ADR_ZIP5_NO          VARCHAR(10) ,
  t_ADR_ZIP4_NO          VARCHAR(10)
 )
  with replace
  on commit preserve rows;

  set stmt = 'INSERT INTO ' || SESSION.ABC|| ' SELECT    ' ||
         ' COALESCE(CHAR(ADR_FORMAT_CD) ,'''')          as  t_ADR_FORMAT_CD, ' ||
         ' COALESCE(CHAR(ADR_STREET_TX) ,'''')          as  t_ADR_STREET_NO,  ' ||
         ' COALESCE(CHAR(ADR_BOX_NO)    ,'''' )         as  t_ADR_BOX_NO,   ' ||
         ' COALESCE(F_PDESC(ADR_PRE_DIR_CD,69),'''')    as  t_ADR_PRE_DIR_CD,  ' ||
         ' COALESCE(ADR_STREET_NM,'''')                 as  t_ADR_STREET_NM,   ' ||
         ' COALESCE(F_PDESC(ADR_STREET_SUFFIX_CD,212),'''') AS t_ADR_STREET_SX_CD, ' ||
         ' COALESCE(F_PDESC(ADR_POST_DIR_CD,69) ,'''')  as   t_ADR_POST_DIR_CD,  ' ||
         ' COALESCE(F_PDESC(ADR_UNIT_TYPE_CD,250),'''') as  t_ADR_UNIT_TYPE_CD,  ' ||
         ' COALESCE(ADR_UNIT_NO_TX ,'''')               as  t_ADR_UNIT_NO_TX,   ' ||
         ' COALESCE(ADR_CITY_NM ,'''')                  as  t_ADR_CITY_NM,   '  ||
         ' COALESCE(ADR_COUNTY_CD ,'''')                as  t_ADR_COUNTY_CD, '  ||
         ' COALESCE(F_PDESC(ADR_STATE_CD,211) ,'''')    as  t_ADR_STATE_CD,  '  ||
         ' COALESCE(CHAR(integer(ADR_ZIP5_NO)) ,'''')            as  t_ADR_ZIP5_NO,   '  ||
         ' COALESCE(CHAR(integer(ADR_ZIP4_NO)) ,'''')            as  t_ADR_ZIP4_NO   '
         || ' FROM ' || vartabname  ||
            ' WHERE ' || varcolname || ' = ' ||  char(varKEY_ID) || ' and DELETE_SW = ''N'' ';--

      execute immediate stmt;
        END 

Open in new window

0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

November 2009 Recently, a question came up in the DB2 forum regarding the date format in DB2 UDB for AS/400.  Apparently in UDB LUW (Linux/Unix/Windows), the date format is a system-wide setting, and is not controlled at the session level.  I'm n…
Recursive SQL in UDB/LUW (you can use 'recursive' and 'SQL' in the same sentence) A growing number of database queries lend themselves to recursive solutions.  It's not always easy to spot when recursion is called for, especially for people una…
This video discusses moving either the default database or any database to a new volume.
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now