Re: SQL select into with constant

Instead of inserting constant values, and thus repeating inserts each with different constants, let the SQL calculate from the existing "year" data, the desired value that will represent the "Fiscal Year" string:

insert into qtemp/counts
select
count(distinct(empno))
, sum(gross)
, sum(fica)
, left(digits(dec( ck4ymd-0701 ,8)),4)
concat '-' concat
left(digits(dec( ck4ymd-0701 +10000 ,8)),4)
from
payhist
where ck4ymd>= 19990701
group by
left(digits(dec( ck4ymd-0701 ,8)),4)
concat '-' concat
left(digits(dec( ck4ymd-0701 +10000 ,8)),4)

Although a bit ugly, especially for numeric subtraction versus valid date calculations, to me that expression [even having to be repeated in the GROUP BY] is much nicer than coding multiple INSERT each with the literals repeated for the data and selection.

Regards, Chuck

On 3/8/11 2:40 PM, 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?