Re: SQL select into with constant

["Raul A. Jager W." <raul@xxxxxxxxxx>]

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
>  
>