Using SQL to search within a DB2 field on IBM i
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 first published in October 2008