Hmmm. So I can use "insert into" with a select coming from a view defined as a union, even though I
can't do it directly. Here I'll need two views, as my union actually changes based on export rules
(whether or not to include people updated since last update) ... and that wouldn't work too well if
my view were more variable than that (it'd be nice to create temporary views for cases where you
need a named statement for this sort of reason, but don't really need it to stick around forever.)
I prefer to store the result in a temp table before joining (indexed) just because I want to avoid
Firebird doing the "distinct" (whether through 'distinct' or through 'union') work several times,
once for each join. Thanks!

-Philip

---------- Original Message -----------
From: Helen Borrie <helebor@...>
To: firebird-support@yahoogroups.com
Sent: Tue, 26 Oct 2004 08:45:22 +1000
Subject: Re: [firebird-support] insert into select ... union ...

> At 01:09 PM 25/10/2004 -0700, you wrote:
>
> >insert into temp_hl7_export_people (person_id)
> >select temp_hl7_export_tasks.person_id from temp_hl7_export_tasks
> >union
> >select temp_hl7_export_pharm.person_id from temp_hl7_export_pharm
> >union
> >select people.id from people where person_id != 0 and last_update_stamp >
> >people.last_ndw_export_stamp
> >
> >I get 'token unknown: union' at the first one.
>
> Oh, OK, because you can't use anything but a straight SELECT statement for
> this...but what I was suggesting was just to use the unioned set directly,
> i.e. why use a temporary table?
>
> >I can run any chunk of this just fine. I'm using
> >this to then join back against various tables during an export (multiple
> >parallel datasets,) so I'd
> >really rather the temp_hl7_export_people table be entirely unique before I
> >start doing that, to
> >avoid working the engine harder than needed. (The two first temp tables
> >have their own logic
> >applied ahead of time.) The field types match, number of fields match ...
> >thus my question about
> >unions during 'insert into'.
>
> Well, a way to do that would be to create a view of the union and do your
> insert from that. And, again, unless you specify UNION ALL, you won't get
> any duplicates.
>
> But you really don't need the temporary table at all, since every row in
> the view is already a distinct person_id, every time you select from it,
> ready to be used on the left side of the join
>
> ./heLen
>
> ------------------------ Yahoo! Groups Sponsor --------------------~-->
> $9.95 domain names from Yahoo!. Register anything.
> http://us.click.yahoo.com/J8kdrA/y20IAA/yQLSAA/67folB/TM
> --------------------------------------------------------------------~->
>
> Yahoo! Groups Links
>
>
>

------- End of Original Message -------