Ask the Expert

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

There are Comments. Add yours.

TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
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
Sort by: OldestNewest

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: