SQL or stored procedure in a CL program???

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 - 1585 days ago
Last viewed on: 27 May 2017 (3620 views)

Main URL:

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

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

Sort Ascend | Descend

COMMENTS

(Sign in to Post a Comment)

Posted by: bobcozzi

Site Admin ****

Chicagoland

www.cozTools.com/qryfile

Comment on: SQL or stored procedure in a CL program???
Posted: 5 years 5 days 23 hours 58 minutes ago
Edited: Mon, 21 May, 2012 at 20:03:43 (1832 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: 5 years 5 days 8 hours 52 minutes ago

Great idea, Bob! Thanks!

Posted by: chrisp

Premium member *

Portland, OR

Comment on: SQL or stored procedure in a CL program???
Posted: 5 years 4 days 6 hours 52 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: 5 years 4 days 5 hours 51 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: 5 years 3 days 12 hours 39 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