Midrange News for the IBM i Community


Posted by: Chris Proctor
Programmer Analyst
Columbia Sports Company
Portland, OR
softcoding a library in select statement?
has no ratings.
Published: 31 Aug 2011
Revised: 19 Feb 2014 - 1219 days ago
Last viewed on: 22 Jun 2017 (5188 views) 

Using IBM i? Need to create Excel, CSV, HTML, JSON, PDF, SPOOL reports? Learn more about the fastest and least expensive tool for the job: SQL iQuery.

softcoding a library in select statement? Published by: Chris Proctor on 31 Aug 2011 view comments(14)

Good morning. I have a situation where I need to specify the library in the the SELECT statment, because it is pointing to a remote system and they may want it to access the test or the production file. I've coded a variable to contain the desired library name, but now I'm trying to figure out the best way to change the SQL statement.

 

Here's the current statement:

    exec sql select imitm 
      into :o_styitm
      from csuqadat/f4101lc 
      where imlitm = :o_$dsrp0;

 

Do I have to do something like the following?

        sqlstmt = 'select imitm into o_styitm from ' + %trim(libname) + '/f4101lc where imlitm = o_$dsrp0';
        exec sql :sqlstmt;

My problem with the above is that I will be excuting this code a ton of times to retrieve item information. Surprised Any suggestions would be appreciated.

 

Thanks!

Good morning. I have a situation where I need to specify the library in the the SELECT statment, because it is pointing to a remote system and they may want it to access the test or the production file. I've coded a variable to contain the desired library name, but now I'm trying to figure out the best way to change the SQL statement.

 

Here's the current statement:

    exec sql select imitm 
      into :o_styitm
      from csuqadat/f4101lc 
      where imlitm = :o_$dsrp0;

 

Do I have to do something like the following?

sqlstmt = 'select imitm into o_styitm from ' + %trim(libname) + '/f4101lc where imlitm = o_$dsrp0';

exec sql :sqlstmt;

 

 

 

My problem with the above is that I will be excuting this code a ton of times to retrieve item information. Surprised Any suggestions would be appreciated.

 

Thanks!

Return to midrangenews.com home page.
Sort Ascend | Descend

COMMENTS