SQL and RPG 400 (SQLRPG) Variable lengths help required.

15 pts.
Please could someone help me with the following, I am new to SQL and have been tasked with the following:- I am writing an RPG program where the user will be able to do a search by entering up to 15 characters to find part numbers. I know how to do this using SQL but I am struggling with this when I embed the SQL into my RPG program. i.e if the user wants to find all part numbers with 175 in, the SQL would be PNUM like '%175%' I am assuming I need to change the '%175%' to a variable name i.e SQL would be PNUM Like :PNVAR I have defined PNVAR as 15 Characters long because the user could put up to 15 Chars in the search field. When I create my String from the user input %175% and then move it into PNVAR the field contains the following:- '%175% ' which is incorrect. As far as I'm aware you cannot create a variable length field in RPG, but can you create one in SQL as however many characters the user enters this is the number of chars +%% that the PNVAR needs to be. Am I even on the right lines... who knows? Help!

Answer Wiki

Thanks. We'll let you know when a new response is added.

The answer is to build the complete SQL statment on the fly.

Here is a sample

begsr GetPageCount;

// get the page count for the file
// set the select statment
sqlStmt = ‘select count(*) from CUSTLOG where pagctl = ‘ +
qt + ‘1’ + qt;

c/exec sql
c+ PREPARE S1 FROM :sqlStmt


Here is another sample
// build a record for the output member
if first = ‘Y’;
stmt = ‘Update ‘ + %trim(libraryName) + ‘/’ +
%trim(fileName) + ‘ Set ‘;
stmt = %trim(stmt) + ‘,’;

c/exec sql
c+ execute immediate :STMT

Discuss This Question: 6  Replies

There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.
  • Cwc
    create a variable length field in RPG. It's done by specifying the VARYING keyword in the definition. Just specify 15 as the length, since that would be its maximum. In your calculations, you could use the %Len( ) built-in-function to get the actual number of characters that were entered in the field.
    4,290 pointsBadges:
  • AS400Kate
    Thanks... I have now sorted the problem with your help.
    15 pointsBadges:
  • TomLiotta
    Am I even on the right lines... who knows? That depends whether the column in the database file is variable length or not. If the file has a fixed-length column, then you're not quite on the right track yet, especially if the user may want to enter a value such as '%175' instead of '%175%'. Tom
    125,585 pointsBadges:
  • AS400rpgle
    How to print a PDF image which is stored in BLOB using SQL RPGLE?
    15 pointsBadges:
  • philpl1jb
    Hi AS400rpgle Please start a new question for this unrelated task. Phil
    54,090 pointsBadges:
  • philpl1jb
    Didn't see this question .. seems like it should have been solved something like this .. wrkVar = '''%' + %trim(PNVAR) + '%''' execsql Select * from myfile where PNUM Like :wrkVAR The two quotes leaves a single quote in actual field the third quote terminates the string so the field wkVar would contain '%123%' followed by a bunch of spaces which would be ignored Phil
    54,090 pointsBadges:

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

Thanks! We'll email you when relevant content is added and updated.


Share this item with your network: