In that case make it a selectable procedure that contains the union but with
a param


CREATE PROCEDURE my_proc( D_TO TIMESTAMP)
returns (myid integer,
mydate timestamp)
BEGIN

FOR select tb1.id,tb1.date1 from tb1
where date1 > :d_to
union
select tb2.id2,tb2.date2 from tb2
where date2 > :d_to
INTO :myid, :mydate
DO
BEGIN
SUSPEND;
END

end


call as select * from my_proc('2000.01.01')

NB syntax not checked

--
Nick
-----We Solve your Computer Problems---
UNIX, C, Panther, Ingres, Interbase, Firebird - Available Shortly

-----Original Message-----
From: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] On Behalf Of Adomas Urbanavicius
Sent: 31 March 2006 13:13
To: firebird-support@yahoogroups.com
Subject: Re: [firebird-support] simple sql join ?


Well, in fact I need to perform "dummy" union with joins; but I cant use
union, because this
select is incapsulated into view, and so, first of all it will union
tables, ant only then will do outer filtering (and, I suppose, without
indices)
Maybe I am wrong ?

sample :
TB1 (ID1,DATE1),TB2(ID2,DATE2). ID,DATE1/2 - indexed.
this would be what I need :

select tb1.id,tb1.date1 from tb1
where date1 > '2000.01.01'
union
select tb2.id2,tb2.date2 from tb2
where date2 > '2000.01.01'.

date1/2 is indexed field, so results are instant.

But, because of some reason, we have to incapsulate this selection into
view :
create view V_A1
(
ID,
DATE0
)
AS
select tb1.id,tb1.date1 from tb1
union
select tb2.id2,tb2.date2 from tb2.

And now,
select * V_A1 where date0 > '2000.01.01'
is very, very slow....I looks, like FB decides first of all make union
and then filter it. I definitely know this does not happen with joins :).
That is why I need make "union" :) via joins :).
Adomas





Martijn Tonies wrote:

>
>
>>Yes, and reads from tables : count(tb1) * count(tb2) . no good.
>>
>>
>
>Well, given that you want the FULL results from BOTH tables,
>what do you expect?
>
>What is it that you're trying to do?
>
>>From the sample result, I'd say you want all rows in T1 and
>all rows in T2 and have NULLs for places where there's no
>existing match.
>
>Martijn Tonies
>Database Workbench - development tool for Firebird and more!
>Upscene Productions
>http://www.upscene.com
>My thoughts:
>http://blog.upscene.com/martijn/
>Database development questions? Check the forum!
>http://www.databasedevelopmentforum.com
>
>
>
>>Nick wrote:
>>
>>
>>
>>>select * from table1 full join table2 on id1 = id2
>>>
>>>Hi,
>>>
>>>I need simply to join 2 tables with join, where result is :
>>> TB1 (ID1) - ID - index
>>> 1
>>> 2
>>> 3
>>> TB2(ID2) - ID - index
>>> 4
>>> 5
>>> 6
>>>select .... :
>>> TB1.ID1 TB2.ID2
>>> 1 NULL
>>> 2 NULL
>>> 3 NULL
>>> NULL 4
>>> NULL 5
>>> NULL 6
>>>Any way to do it ?
>>>Conditions :
>>>NO SP, NO Union. (in real conditions and union and Sp is to slow
>>>because of size of tables (>2.5mill records.))
>>>
>>>
>
>
>
>++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
>Visit http://firebird.sourceforge.net and click the Resources item
>on the main (top) menu. Try Knowledgebase and FAQ links !
>
>Also search the knowledgebases at http://www.ibphoenix.com
>
>++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
>Yahoo! Groups Links
>
>
>
>
>
>
>
>
>
>

--

Pagarbiai / Best Regards,
Adomas Urbanavicius
+37068543336



++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Visit http://firebird.sourceforge.net and click the Resources item
on the main (top) menu. Try Knowledgebase and FAQ links !

Also search the knowledgebases at http://www.ibphoenix.com

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++





SPONSORED LINKS
Technical
<http://groups.yahoo.com/gads?t=ms&k=Technical+support&w1=Technical+support&
w2=Computer+technical+support&w3=Compaq+computer+technical+support&w4=Compaq
+technical+support&w5=Hewlett+packard+technical+support&w6=Microsoft+technic
al+support&c=6&s=196&.sig=-XIO8GxY6hqd3NaD5WSEyw> support Computer
<http://groups.yahoo.com/gads?t=ms&k=Computer+technical+support&w1=Technical
+support&w2=Computer+technical+support&w3=Compaq+computer+technical+support&
w4=Compaq+technical+support&w5=Hewlett+packard+technical+support&w6=Microsof
t+technical+support&c=6&s=196&.sig=B29J78SYXnNTjjMFBMznqA> technical support
Compaq
<http://groups.yahoo.com/gads?t=ms&k=Compaq+computer+technical+support&w1=Te
chnical+support&w2=Computer+technical+support&w3=Compaq+computer+technical+s
upport&w4=Compaq+technical+support&w5=Hewlett+packard+technical+support&w6=M
icrosoft+technical+support&c=6&s=196&.sig=7_je1A94xs82CFXUjEqA6g> computer
technical support
Compaq
<http://groups.yahoo.com/gads?t=ms&k=Compaq+technical+support&w1=Technical+s
upport&w2=Computer+technical+support&w3=Compaq+computer+technical+support&w4
=Compaq+technical+support&w5=Hewlett+packard+technical+support&w6=Microsoft+
technical+support&c=6&s=196&.sig=2zMAuRCo5cJrVBr1Bxa3_w> technical support
Hewlett
<http://groups.yahoo.com/gads?t=ms&k=Hewlett+packard+technical+support&w1=Te
chnical+support&w2=Computer+technical+support&w3=Compaq+computer+technical+s
upport&w4=Compaq+technical+support&w5=Hewlett+packard+technical+support&w6=M
icrosoft+technical+support&c=6&s=196&.sig=_ytYU7aXb57AVaeUfmvLcA> packard
technical support Microsoft
<http://groups.yahoo.com/gads?t=ms&k=Microsoft+technical+support&w1=Technica
l+support&w2=Computer+technical+support&w3=Compaq+computer+technical+support
&w4=Compaq+technical+support&w5=Hewlett+packard+technical+support&w6=Microso
ft+technical+support&c=6&s=196&.sig=4hRo6NXYavRAbTkaYec5Lw> technical
support

_____

YAHOO! GROUPS LINKS



* Visit your group "firebird-support
<http://groups.yahoo.com/group/firebird-support> " on the web.


* To unsubscribe from this group, send an email to:
firebird-support-unsubscribe@yahoogroups.com
<mailto:firebird-support-unsubscribe@yahoogroups.com?subject=Unsubscribe>


* Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service
<http://docs.yahoo.com/info/terms/> .


_____




[Non-text portions of this message have been removed]