AS400 – Queries

335 pts.
Tags:
AS/400 queries
AS/400 Query
AS/400 tables
WRKQRY
Hi I have one query 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. I need to run this query in a batch mode,just by changing the date range.how can i run this query in WRKQRY Tool. iam doing now in STRSQL which runs intercativley for an hour. So i wanted to run in batch and saev the result sets in one file. Thanks

Answer Wiki

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

Yes, this can be done with Query 400

Select sort fields
STORE

Select report summary functions
sum amount column

Define report breaks
STORE

Select output type and output form
Summary
Phil

Discuss This Question: 12  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
  • TomLiotta
    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
    125,585 pointsBadges:
    report
  • iiiiiiiiiiiii
    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
    335 pointsBadges:
    report
  • sudheer794
    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.
    315 pointsBadges:
    report
  • DoneThat
    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
    830 pointsBadges:
    report
  • philpl1jb
    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
    50,860 pointsBadges:
    report
  • TomLiotta
    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
    125,585 pointsBadges:
    report
  • TomLiotta
    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
    125,585 pointsBadges:
    report
  • TomLiotta
    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
    125,585 pointsBadges:
    report
  • iiiiiiiiiiiii
    [...] 4. Join TomLiotta, Philpl1jb, DoneThat, and Sudheer794 in helping a member with AS/400 queries. [...]
    0 pointsBadges:
    report
  • sudheer794
    Hi Tom and Phil. Thank you very much..
    315 pointsBadges:
    report
  • Kepn
    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.
    20 pointsBadges:
    report
  • TomLiotta
    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
    125,585 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