Midrange News for the IBM i Community


Posted by: Chris Proctor
Programmer Analyst
Columbia Sports Company
Portland, OR
SQL or stored procedure in a CL program???
has no ratings.
Published: 21 May 2012
Revised: 23 Jan 2013 - 2185 days ago
Last viewed on: 16 Jan 2019 (4940 views) 

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.

SQL or stored procedure in a CL program??? Published by: Chris Proctor on 21 May 2012 view comments(5)

Return to midrangenews.com home page.
Sort Ascend | Descend

COMMENTS

(Sign in to Post a Comment)
Posted by: bobcozzi
Site Admin ****
Chicagoland
Comment on: SQL or stored procedure in a CL program???
Posted: 6 years 7 months 28 days 5 hours 38 minutes ago
Edited: Mon, 21 May, 2012 at 20:03:43 (2432 days ago)

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.

Posted by: chrisp
Premium member *
Portland, OR
Comment on: SQL or stored procedure in a CL program???
Posted: 6 years 7 months 27 days 14 hours 31 minutes ago

Great idea, Bob! Thanks!

Posted by: chrisp
Premium member *
Portland, OR
Comment on: SQL or stored procedure in a CL program???
Posted: 6 years 7 months 26 days 12 hours 31 minutes ago

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.

Posted by: sarge
Premium member *
United States
Comment on: SQL or stored procedure in a CL program???
Posted: 6 years 7 months 26 days 11 hours 30 minutes ago

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

Posted by: DaleB
Premium member *
Reading, PA
Comment on: SQL or stored procedure in a CL program???
Posted: 6 years 7 months 25 days 18 hours 19 minutes ago

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