|
Hi Matt and Dan, 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 ... Be aware the fields selected in the SELECT-statement must match the fields in the table to insert. If not all fields are inserted, you have to specify the fields that will be filled in the Insert statement. Insert into YourTable (Field1, Field2, Field23, Field47) With T1 as (Select ....) Select a, b, c, d from T1 ... Mit freundlichen Grüßen i.A. Birgitta Hauser LUNZER + PARTNER GMBH Consulting | Software | Service Carl-Zeiss-Straße 1 63755 Alzenau Tel: + 49 6023 951-255 Fax: + 49 6023 951-111 Internet. www.lp-gmbh.com www.rpg-schulung.de -------------------------------------------------------- Handelsregister: Aschaffenburg HRB 4720 Gerichtsstand und Erfüllungsort Alzenau Ust-IdNr.: DE132093146 Geschäftsführer: Rudolf Gerbert --------------------------------------------------------- -----Ursprüngliche Nachricht----- Von: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-bounces@xxxxxxxxxxxx] Im Auftrag von Tyler, Matt Gesendet: Donnerstag, 15. Juni 2006 22:46 An: Midrange Systems Technical Discussion Betreff: RE: Cool but ugly SQL Dan, This will work... With T1 as (Select...) Insert into yourtable Select * from T1 Thanks, Matt -----Original Message----- From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Dan Sent: Thursday, June 15, 2006 2:11 PM To: Midrange Systems Technical Discussion Subject: Re: Cool but ugly SQL On 6/15/06, Fisher, Don <dfisher@xxxxxxxxxxxxx> wrote:
Try this: 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 etc... Hope that helps.
Absolutely. Wow. I would have never found that on my own. Is this considered a subquery? OK, this is almost complete now. I figured it would be easy enough to tack on the INSERT INTO after I got the SELECT figured out. I first tried placing it between the "With ()" and "Select * from SRCMBRHASH" stuff, but got: Keyword INSERT not expected. Valid tokens: ( SELECT. So I tried placing the INSERT INTO at the very beginning, before the "With ()", but got: Token TEMPFILE was not valid. Valid tokens: ) ,. Many, MANY thanks! - Dan
As an Amazon Associate we earn from qualifying purchases.
This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].
Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.