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.
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
