Q

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 first 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.

0 comments

Oldest 

Forgot Password?

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

Your password has been sent to:

-ADS BY GOOGLE

SearchEnterpriseLinux

SearchDataCenter

Close