Re: SQL select into with constant

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