Get started Bring yourself up to speed with our introductory content.

Using SQL to search within a DB2 field on IBM i

By taking advantage of the Row_Number function introduced in IBM i's V5R4, you can process a range of values within a field.

I am looking for an example of an RPGIV program that uses SQL to display within a subfile the results of a user query. I have searched the Web, and I find things that are almost but not quite what i am looking for. My program currently accepts a user input and displays a logical file positioned at the start of the field, and SQL would give us the ability to search within the field, not just at the start of the field -- but I need a jump start.
Some RPG SQL programmers are taking advantage of the Row_Number function introduced in V5R4 to process a range of values similar to the following SQL statement:
WITH numbering AS ( 

SELECT ROW_NUMBER() OVER (ORDER BY workdept,lastname) AS rowno, lastname, salary, workdept FROM emp)
SELECT rowno, lastname, salary FROM numbering
WHERE rowno BETWEEN :HV1 AND :HV2 AND workdept='123'
ORDER BY lastname
This was last published in October 2008

Dig Deeper on iSeries SQL commands and statements

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.