Home > IT Answers > AS/400 > AS/400:- I need to build a query to select records, to be displayed in subfile
svankipu
315 pts.
 AS/400:- I need to build a query to select records, to be displayed in subfile
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

Software/Hardware used:
ASKED: May 21, 2008  10:07 AM
UPDATED: May 22, 2008  3:59 PM
RELATED QUESTIONS
Answer Wiki:
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.
Last Wiki Answer Submitted:  May 21, 2008  3:25 pm  by  alessandro.panzetta   9,660 pts.
All Answer Wiki Contributors:  alessandro.panzetta   9,660 pts.
To see all answers submitted to the Answer Wiki: View Answer History.


RSS - Discussions Help
Discuss This Question:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _

 

Plese explain your Question

Regards
Reshma

ReshmaG
 445 pts.
 

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

mcl
 2,725 pts.
 

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

NarasimhaReddy
 225 pts.
 

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

Sloopy
 2,195 pts.
 

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.

Gilly400
 23,625 pts.