Re: SQL select into with constant

[Jason Abreu <jason.abreu@xxxxxxxxxxxxxxxxxxxx>]

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