YA variation of "How do I do this with only ONE SQL statement?":
The client billing app that I maintain has Work Function Codes
(WFC) for the types of work that are performed. These have an expiry
date (validtil). My boss now wants a program for automatically
generating the next ones. I have a cut for the basic code. It takes
three SQL statements. Is it possible to do it in one?
goyear() is like gomonth() except that it works by years and it
makes sure that YY-02-28 becomes (YY+offset)-02-29 when YY-02-28 is at
the end of the month.
WFCs are uniquely specified by funccode-clcode-wccode-validtil.
The first cursor contains the WFCs that will be used to generate
the new year's WFCs. This code allows for a WFC to lapse so that no
longer used WFCs do not keep getting generated each year. (latestdt
is the last day of the yearlong period for which the new validtil
value can be. If it would not be in this range, then a new WFC is not
to be generated.)
***** Start of Included Code *****
latestdt={^2008.12.31}
select * from cwkf as out;
where;
clcode="BHU" and validtil=;
(;
select max(validtil) from cwkf;
where;
funccode=out.funccode and clcode=out.clcode and
wccode=out.wccode;
);
and goyear(validtil,1)>goyear(latestdt,-1) and;
goyear(validtil,1)<=latestdt;
into cursor togen readwrite
update togen set validtil=goyear(validtil,1)
insert into cwkf;
select * from togen
***** End of Included Code *****
I could change the insert to specify each individual column and
save having the update statement, but that is more trouble than it is
worth. (Suppose I add a column to cwkf. Then, I would have to update
this program, too.)
What I am hoping for is a way to insert in one statement.
Sincerely,
Gene Wirchenko
Computerese Irregular Verb Conjugation:
I have preferences.
You have biases.
He/She has prejudices.