Re: Cool but ugly SQL

On 6/16/06, Hauser, Birgitta <Birgitta.Hauser@xxxxxxxxxxx> wrote:

This will not work...

>>With T1 as (Select...)
>>Insert into yourtable
>>Select * from T1

WITH is a part of the Select- and not of the Insert-Statement.

The correct syntax must be:
Insert into YourTable
   With T1 as (Select ....)
   Select .... from T1 ...

O.k.  I think I finally got it.  Here's what worked for me:

INSERT into SRCHASH001
With TempFile as (Select shh_lib, shh_file, shh_member,
       max(char(shh_LCDate) || char(shh_LCTime)) as datetime
    from SRCMBRHASH group by shh_lib, shh_file, shh_member)
  select * from SRCMBRHASH a join TempFile b on
              a.shh_lib    = b.shh_lib
          and a.shh_file   = b.shh_file
          and a.shh_member = b.shh_member
          and char(a.shh_LCDate) || char(a.shh_LCTime) = b.datetime
   where    a.shh_Lib  =    'DOC1SRCE'
        and a.shh_File LIKE 'Q%'
   Order by a.shh_Lib, a.shh_File, a.shh_Member,
              a.shh_LCDate desc, a.shh_LCTime desc

I didn't want to use TempFile as the source of the data being inserted.
Apparently, all of the previous syntax errors with my attempt to start the
SQL statement off with the INSERT was caused by parentheses that were not
needed.  (I had parentheses surrounding the main select).

Moral of the story:  Parentheses are not always your friend.

Thanks to all who chimed in!

- Dan