RE: SQL select into with constant

["Lance Gillespie" <LGillespie@xxxxxxxx>]

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