RE: SQL select into with constant

Aw, crap! Dead simple, just like you said.

My only defense is that nothing in my experience
would lead me to expect that anything in SQL would
be that straight-forward.

Back to my hole.

Thanks!
lance




-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Raul A. Jager W.
Sent: Tuesday, March 08, 2011 2:46 PM
To: Midrange Systems Technical Discussion
Subject: Re: SQL select into with constant

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