Re: SQL select into with constant

insert into counts

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