Using IBM i? Need to create Excel, CSV, HTML, JSON, PDF, SPOOL reports? Learn more about the fastest and least expensive tool for the job: SQL iQuery.
Good afternoon! I have been given the task of taking a CL program that creates a copy of large files (snapshot) then calls a rmtcmd on a server to get the files from the iSeries.
They're complaining because it's taking longer and longer as the files grow and now want to limit it to 2 years being extracted. I'm hoping to somehow call SQL or a stored procedure that I can pass the date range to extract, and have it do an insert into the output file for pick up.
Is that possible, and if not, any suggestions?
Thanks!
Chris
I'd create a view with a WHERE clause that was something like:
WHERE theDate > CURRENT_DATE - 2 YEARS
And then copy the data via the VIEW itself.
Great idea, Bob! Thanks!
Is there a way to have a variable accessed within a view? Now the user has come back and said that they want it by retail year, which means I have to retrieve the start date from a file and use that for the record selection.
if this is not a huge file, you could recreate the view based on the retail year. but, then again, i imagine there will be many, many records, since this appears to have detail-level data for retail operations.
alternatively, you could create a view for each year, provided they only want 2-5 years of data available, then direct your CL to that view based on their selection criteria.
-sarge
Variable in a view? Don't think so. How about a join to the date file? (Old Query/400 trick.) Remember, the join's ON conditions do not have to be equality. You can join on <>, >, >=, <, <=; even BETWEEN is valid (all joins are not created equal...).
SELECT *
FROM a
JOIN datefile d ON a.theDate BETWEEN d.StartDate AND d.EndDate