AS/400:- I need to build a query to select records, to be displayed in subfile

560 pts.
Tags:
AS/400
Subfile
The requirement is basically:- 1) I have a screen with some "FROM" and "TO" fields(see below:) -------------------------------------------------------------------------------------------------------------- Select: From To ------------------------------ ----------- ----------- Fax Referenece Number: XXXXXX XXXXXX Order date XX/XX/XX XX/XX/XX Contract number PKXXXXX PKXXXXX Item XXXXXXXXX XXXXXXXXX Warehouse XXX XXX Vendor XXXXXX XXXXXX F3 - Exit Press enter ---------------------------------------------------------------------------------------------------------------- all records satisfying the above range, have to be queried & displayed in a subfile for further process. (The user can enter all fields, some fields or only one field etc.,(but atleast one field is mandatory)). Thanks in advance, Gopy
ASKED: May 21, 2008  10:07 AM
UPDATED: May 22, 2008  3:59 PM

Answer Wiki

Thanks. We'll let you know when a new response is added.

One way would be to build a dynamic QMQRY using SETVAR to determine your selection criteria. Since you may have more than 1 selection criteria, I would build 7 different SQL with 1 to 7 selection criteria:

SQL 1:

Select * from MYTABLE where &F1 = &F2

SQL 2:

Select * from MYTABLE where &F1 = &F2 OR &F3 between &F4 and &F5

and so on…

Then, write a CL program passing parameters and based on the number of parameters passed, execute the proper QMQRY passing parameters.

Dont forget to add additionnal bracket whenever passing data content variable.

Put the result of your QMQRY into a temporary file in QTEMP for example and access that file by the RRN using a SETLL int your RPG to read the information and put it onto your subfile.

This way, you will have a full flexible procedure no matter how many selection criteria are selected.

Have fun !

_______________________
DanTheMan

Hello,
if you are talking about a SQL query here is a code example:
<pre>
SELECT * FROM MYTABLE WHERE
FaxRefereneceNumber LIKE ‘%XXXXXX XXXXXX%’
OR Orderdate BETWEEN #XX/XX/XX# AND #XX/XX/XX#
OR Contractnumber LIKE ‘%PKXXXXX PKXXXXX%’
OR Item LIKE ‘%XXXXXXXXX XXXXXXXXX%’
OR Warehouse LIKE ‘%XXX XXX%’
OR Vendor LIKE ‘%XXXXXX XXXXXX%’
</pre>

I hope this helps.

Discuss This Question: 5  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

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
  • ReshmaG
    Plese explain your Question Regards Reshma
    455 pointsBadges:
    report
  • mcl
    Have been doing something like this for a while for various applications using a combination of RPG and CL programs- 1. Have a prompt program that presents the user with a selection screen and does any necessary validation of input. Query criteria are passed out of the prompt program as parameters to the query program. 2. The query program (generally a CL) builds a statement to use in an OPNQRYF. How you do that is up to you. You won't need the "SELECT * FROM MYTABLE" syntax, but you will need something like "FIELD1 *EQ value *OR FIELD2 *EQ value2 *AND FIELD3 *EQ value3" etc.. 3. The OPNQRYF is set up like: OPNQRYF FILE((*LIBL/MYTABLE)) OPTION(*ALL) + QRYSLT(&QRYSRT) KEYFLD(*FILE) OVRDBF FILE(MYTABLE) TOFILE(MYTABLE) SHARE(*YES) CALL PGM(MYPROG) CLOF OPNID(MYTABLE) 4. &QRYSRT is the query statement you built from the initial prompts. MYPROG is a program that will display the data in a subfile. A couple of points to note - If you call MYPROG by itself (no query) it should display all of the data in MYTABLE. So, if you already have a subfile program like that you can easily set up your query using the method above. If MYPROG calls a subprogram that allows the user to edit the data in MYTABLE, and the edit changes data in a filed used as part of the query, the record may no longer be part of the query subset. Regards Mike
    2,740 pointsBadges:
    report
  • NarasimhaReddy
    It can be done using only one query thru QMQRY Write Query with Varibles for all conditions. In program before executing Load the values with values entered, If no value is entered for any field, then you can store Lowest Values for the from field, and highest possible values into To field. Ex: If it is Numeric field with length 5 then 00000 into from value & 99999 into TO field. If selection values are entered, then the value entered can be moved into FROm & TO fields. I hope this helps. Reddy
    225 pointsBadges:
    report
  • Sloopy
    I would do it Reddy's way, but using embedded SQL, which is far faster than QMQRY or OPNQRYF and works with fetches within the RPG loop that fills the subfile. We do, in fact, do that sort of thing a lot here. Sloopy
    2,195 pointsBadges:
    report
  • Gilly400
    Hi, I would probably also opt for the embedded SQL approach, you could define a cursor with a select something like this:-
    SELECT * FROM mytable
    WHERE
    (FaxreferenceNumber >= :FromFaxreferenceNumber OR :FromFaxreferenceNumber = ' ') AND
    (FaxreferenceNumber <= :ToFaxreferenceNumber OR :ToFaxreferenceNumber = ' ') AND
    (OrderDate >= :FromOrderDate OR :FromOrderDate = 0) AND
    (OrderDate <= :ToOrderDate OR :ToOrderDate = 0) AND
    etc. Regards, Martin Gilbert.
    23,730 pointsBadges:
    report

Forgot Password

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

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

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

Thanks! We'll email you when relevant content is added and updated.

Following