Re: SQL select into with constant

[CRPence <CRPbottle@xxxxxxxxx>]

  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?