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

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.

Please create a username to comment.