× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



Spot-on. I use this all the time for rolling control records from one year to the next.

I also like to use functions to increment particular values.

insert into years
select
char(int(controlyear) + 1)
where controlyear = '1999';


Jason Abreu
Abreu Innovations, Inc.
jason.abreu@xxxxxxxxxxxxxxxxxxxx
http://www.abreuinnovations.com/

On 3/8/2011 5:46 PM, Raul A. Jager W. wrote:
Yes, there is an easy way: just resaet the year interval as a constant,
in the right order.

insert into counts
select count(distinct(empno)), sum(gross), sum(fica) ,
'1999-2000'
from
payhist where ck4ymd>= 19990701 and ck4ymd<= 20000630


Lance Gillespie wrote:
I have a table created thus:

CREATE TABLE QTEMP/COUNTS (
EMPCOUNT DEC ( 4, 0) NOT NULL WITH DEFAULT,
GROSSTOT DEC ( 12, 2) NOT NULL WITH DEFAULT,
FICATOT DEC (12, 2) NOT NULL WITH DEFAULT)


and I updated it with fiscal year totals from pay history thus:

insert into counts
select count(distinct(empno)), sum(gross), sum(fica) from
payhist where ck4ymd>= 19990701 and ck4ymd<= 20000630

insert into counts
select count(distinct(empno)), sum(gross), sum(fica) from
payhist where ck4ymd>= 20000701 and ck4ymd<= 20010630

insert into counts
select count(distinct(empno)), sum(gross), sum(fica) from
payhist where ck4ymd>= 20010701 and ck4ymd<= 20020630

etc. with one 'insert into' for each fiscal year.

This worked fine and I was happy.

Then I thought 'Gee, having the fiscal year in there would be nice'
so I dropped the table and created it again with a 9 char fiscal year
field that I want to be in the form yyyy-yyyy thus:

CREATE TABLE QTEMP/COUNTS (
EMPCOUNT DEC ( 4, 0) NOT NULL WITH DEFAULT,
GROSSTOT DEC ( 12, 2) NOT NULL WITH DEFAULT,
FICATOT DEC (12, 2) NOT NULL WITH DEFAULT,
FISCAL CHAR (9 ) NOT NULL WITH DEFAULT)


figuring that I would just select '1999-2000' from sysibm/sysdummy1
for the first insert, '2000-2001' for the next, etc. along with my other

data using some sort of join. I figured wrong. Every combination of
join and union I could come up with failed.

Now I am unhappy.

The project is over - I just typed the fiscal year in - but I wonder if
there is a way to do this. Can one do an 'insert into select' and have
one of the fields be a constant for every record added? If so, how?

Thanks,
lance




As an Amazon Associate we earn from qualifying purchases.

This thread ...

Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.