Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1115
  • Last Modified:

Execute Immediate not working


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
pvsbandi
Asked:
pvsbandi
  • 5
  • 3
  • 3
2 Solutions
 
Dave FordSoftware Developer / Database AdministratorCommented:

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
 
pvsbandiAuthor Commented:
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
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

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

 
momi_sabagCommented:
the easiest way will probably be to insert it into a table
0
 
Dave FordSoftware Developer / Database AdministratorCommented:

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
 
pvsbandiAuthor Commented:
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
 
pvsbandiAuthor Commented:
declare c1 cursor for s1;--

The above line should be there on top
0
 
Dave FordSoftware Developer / Database AdministratorCommented:

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

HTH,
DaveSlash
0
 
pvsbandiAuthor Commented:
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
 
momi_sabagCommented:
what happens when you execute this procedure
0
 
pvsbandiAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 5
  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now