Don’t use Query/400 — use Query Manager. If you have the STRSQL command, then you also have Query Manager (QM). Use the STRQM command to access QM.

Place your SQL statement in a QM query and execute it with the STRQMQRY command.

STRQMQRY provides a way to pass parameters into the query. You can pass a different date range into your query every time you run it.

Tom

Hi thx alot TOM .

iam very much thankful to u,by keeping this kind of forum and helping programers like this.

Actually iam the only AS400 programmer working in my company.so when ever i have doubt iam mailing u.

thanks once again

i wish u people a happy christmas and NEW YEAR

I tried to apply Group By (like SQL command) in Query/400 but couldn’t get succeed.
Can any one please confirm me is it possible to design the following SQL query (as Iiiiiiiiiiiii, mentioned below) in Query/400:

Which is a join of table A and Table B.
select store,sum(amt) from table A and table B where
A.fld1 = B.fld1
A.fld2 = B.fld2
A.fld3 = b.fld3
AND A.fld3 between date range
group by Store
order by store.

Make sure your syntax is exactly correct. Here a an example of of date selection totaling by store no (my routno). It’s tested and works.
If your database uses separate ccyymmdd fields like mine, this example will add some years back to your life. It also joins a header/detail so you can see how that works.
The final result: Total Units by Storno for the dates selected.
I don’t use QM but, as you can see, you must paramatize the dates in the between clause.

SELECT		
	THH.th_routno AS Storno, 	
	SUM(THD.th_units) 
FROM	rap002001.tichis AS THH		
LEFT OUTER JOIN rap002001.thsdtl AS THD ON		
	THD.thd_trancn = THH.th_trancn  AND  			
	THD.thd_tranyr = THH.th_tranyr  AND  			
	THD.thd_tranmo = THH.th_tranmo  AND  			
	THD.thd_tranda = THH.th_tranda  AND  			
	THD.thd_custno = THH.th_custno  AND			
	THD.thd_tickno = THH.th_tickno 			
WHERE				
	DATE(TRIM	(CHAR(THH.th_tranmo))  ||  '/'  || 		
	TRIM	(CHAR(THH.th_tranda))  ||  '/'  || 		
	TRIM (CHAR((THH.th_trancn  *  100)  +  THH.th_tranyr)))  BETWEEN '12/30/2010' AND '12/30/2010'
GROUP BY			
	THH.th_routno 
ORDER BY			
	THH.th_routno 
WITH	NC;

Hope this gets you started…
Gary

The SQL to get these values would look like this for tables named a and b

Select store,sum(amt) from A join B
On A.fld1 = B.fld1
and A.fld2 = B.fld2
and A.fld3 = b.fld3
Where A.fld3 between begDate and endDate
group by Store
order by store

Phil

I tried to apply Group By (like SQL command) in Query/400…

Why are you trying to do SQL with Query/400? Query/400 doesn’t do SQL.

Use Query Manager instead. QM uses SQL.

Tom

Yes, this can be done with Query 400

But it’s a little more complicated than that because of this requirement:

I need to run this query in a batch mode,just by changing the date range

Because of that, the Query/400 definition will be significantly more complex, and the actual query will need to be run by QM anyway.

If QM isn’t used, then a new file will need to be created and it will need to be updated before every new execution of the query. The new file either will hold the date-range values and be joined to the original file or it will be used as the dependent-value qualifier.

It’s far easier just to use QM from the beginning.

Tom

Note that QM queries can be created from SQL source whether the SQL Development Kit is installed or not. All AS/400s and later systems can create and run QM queries from SQL source members. Just enter the SQL source statement and run CRTQMQRY over it.

For this question, the basic creation is little more than a copy/paste into a source member. (Then comes the change to enable the date-range parameters, but that’ll be almost the same even for Query/400 except easier with QM.)

Tom

Hi Tom and Phil.

Thank you very much..

Create a driving qry, only accessing a small file with a series of dates in it, I used excel and put in all the dates up to year 2040 and then I uploaded my datefile to the AS400.

Now, write a small CL program that does the following:

RUNQRY QRY(yourlib/DATEQRY) RCDSLT(*YES)
The user is prompted to input the date range that is of interest. The result of the qry run is stored in a file. Now you have a file with a number of dates in it.

SBMJOB QRY(yourlib/HEAVYQRY)
HEAVYQRY must be changed so that the small date file created in the first step is joined to the file that you normally choose your date ranges from.

We are using this method and by doing so, we can submit a massive number of queries in a row, where all of them will be dependant of the date/dates in the file created when the run starts.

We are using this method…

As you know, that will work. But why do it when it’s easier to do it directly? There’s no need for a “date” file, and no need for a join. Especially if it’s a heavy query, why not use less resources and run faster?

Tom