Solved

Execute Immediate not working

Posted on 2011-03-21
11
1,003 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
The New “Normal” in Modern Enterprise Operations

DevOps for the modern enterprise offers many benefits — increased agility, productivity, and more, but digital transformation isn’t easy, especially if you’re not addressing the right issues. Register for the webinar to dive into the “new normal” for enterprise modern ops.

 
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
 

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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone 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

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…
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

821 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