Solved

Execute Immediate not working

Posted on 2011-03-21
11
1,019 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
  • 3
11 Comments
 
LVL 18

Expert Comment

by:Dave Ford
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
Guide to Performance: Optimization & Monitoring

Nowadays, monitoring is a mixture of tools, systems, and codes—making it a very complex process. And with this complexity, comes variables for failure. Get DZone’s new Guide to Performance to learn how to proactively find these variables and solve them before a disruption occurs.

 
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:
Dave Ford 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
 

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:Dave Ford
Dave Ford 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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Date and Time Conversion from Numeric Fields 21 282
How to access tables of DB2 in Excel 8 255
DB2 iSeries Date comparison w/ sysibm.sysdummy1 18 118
find age for two dates 5 94
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 (it really isn't that hard to do) Recursive SQL is most often used to convert columns to rows or rows to columns.  A previous article described the process of converting rows to columns.  This article will build off of th…
Suggested Courses

738 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