If I understand what you're trying to do, then a stored procedure is exactly
what you're looking for. For what I know, Firebird doesn't support temporary
tables per se, but considering that a variable can hold a dataset for the
duration of the containing stored procedure... So if you make write out the
routine you're trying to accomplish as a stored procedure, you should be
able to do what you're trying.

R.

----- Original Message -----
From: Garrett Smith
To: firebird-support@yahoogroups.com
Sent: Friday, December 17, 2004 1:50 PM
Subject: RE: [firebird-support] Temporary tables


Storage Box wrote:
> Also, sometimes it's better to query more than once to get
> values+sub-values than it is to make a single monster of a
> joined/union query.

This is the design approach I'm taking (and have taken with other
dbmses), but lack of temp tables and/or subqueries is making it
difficult. Where do you put the intermediate results in Firebird? Is my
only option here a stored procedure?

-- Garrett

> _____
>
> From: Garrett Smith [mailto:garrett@...]
> Sent: Friday, December 17, 2004 12:32 PM
> To: firebird-support@yahoogroups.com
> Subject: RE: [firebird-support] Temporary tables
>
>
> In most cases, yes, we can limit the results before the union. As for
> paging, I think this has to occur after the union, as it will depend
> on sort order.
>
> Is a view with 1000+ columns utter stupidity? Or is it something
> Firebird can handle without a terrible memory/performance hit?
>
> At the end of the day, I suppose I could create a new view, run the
> query, then drop it. This seems painful though.
>
> OTOH, perhaps a store procedure is what I'm looking for. I could loop
> through all the tables involved, performing the appropriate subquery,
> adding to a result set, and then sort the result. The set returned by
> the procedure contains a single ID column.
>
> Storage Box wrote:
>> Is there any way your usage of the query could measure your results,
>> as in "paging" or selecting a range of values by date, getting the
>> top 100 records, etc.
>>
>> Depending on the size of the table(s), a UNION, JOIN, or any other
>> type of relationship can make for a large number of records, killing
>> performance.
>>
>> _____
>>
>> From: Garrett Smith [mailto:garrett@...]
>> Sent: Friday, December 17, 2004 12:13 PM
>> To: firebird-support@yahoogroups.com
>> Subject: RE: [firebird-support] Temporary tables
>>
>>
>> I actually want three columns after the combination (I'm
>> experimenting with unions now):
>>
>> name | foo_name | bar_name
>>
>> where 'name' is your 'foobar.name'. For foo records, foo_name
>> contains a corresponding value, otherwise NULL. Same pattern for
>> bar_name. There are no common IDs between tables.
>>
>> Looks like I could create a view that's a huge union, covering all
>> permutations, and thus avoid a temporary table. But I'm a tad nervous
>> about a) the size of the union SQL and b) the number of columns in
>> the view.
>>
>> -- Garrett
>>
>> Storage Box wrote:
>>> Sounds like you need a UNION - or - are you trying to treat both
>>> name columns as a single column with a single unified name?
>>>
>>> before "temp" table:
>>>
>>> foo.name | bar.name
>>>
>>> after combination:
>>>
>>> foobar.name
>>>
>>> _____
>>>
>>> From: Garrett Smith [mailto:garrett@...]
>>> Sent: Friday, December 17, 2004 11:46 AM
>>> To: firebird-support@yahoogroups.com
>>> Subject: RE: [firebird-support] Temporary tables
>>>
>>>
>>> Ah, sorry, correction...the problem with joins (and I think this is
>>> also a problem with the view solution) is that I want to select
>>> something like:
>>>
>>> select * from ... where name = 'Blah'
>>>
>>> Note there's no table qualifier. I.e. I want the thing I'm querying
>>> to have a name column that is a merging of foo.name and bar.name.
>>> Thus the temporary table.
>>>
>>>
>>>
>>> -- Garrett
>>>
>>> Garrett Smith wrote:
>>>> This could be the best approach -- I was just trying to keep the
>>>> SQL simple. But I'm probably past that point :)
>>>>
>>>> -- Garrett
>>>>
>>>> Adomas Urbanavicius wrote:
>>>>> Why not
>>>>> View or query
>>>>>
>>>>> select * from foo
>>>>> left join bar on
>>>>> foo.id = bar.id
>>>>> where foo.name = 'Baz'
>>>>> or foo.status = 1
>>>>> /*
>>>>> or any other :
>>>>> bar.status = 1
>>>>> bar.name = 'Blah'
>>>>> */
>>>>>
>>>>>
>>>>>
>>>>>>
>>>>>> I have several tables that I want to query as if their rows were
>>>>>> in a single table. My thinking was to use a temporary table and
>>>>>> execute multiple 'select into' queries to populate it and then
>>>>>> run a final select against it.
>>>>>>
>>>>>> E.g., given two tables:
>>>>>>
>>>>>> foo ( id integer, name varchar)
>>>>>> bar ( id integer, name varchar, status integer)
>>>>>>
>>>>>> I want a structure looks like:
>>>>>>
>>>>>> some_structure (
>>>>>> name varchar,
>>>>>> status integer,
>>>>>> foo_name varchar,
>>>>>> bar_name varchar,
>>>>>> bar_status integer)
>>>>>>
>>>>>> so I can run queries like:
>>>>>>
>>>>>> select id from some_structure where name = 'Baz' or status = 1;
>>>>>> select id from some_structure where foo_name = 'Baz' or
>>>>>> bar_status = 1;
>>>>>>
>>>>>> Rows from table foo would NULL values for status. The underlying
>>>>>> applications lets users select objects (represented by id) by
>>>>>> specifying property name values. We validate input to ensure that
>>>>>> an unqualified properties (e.g. 'name', as opposed to
>>>>>> 'bar_name') have compatible types.
>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>
>>>>>
>>>>>
>>>>> ------------------------ Yahoo! Groups Sponsor
>>>>> --------------------~--> $4.98 domain names from Yahoo!. Register
>>>>> anything. http://us.click.yahoo.com/Q7_YsB/neXJAA/yQLSAA/67folB/TM
>>>>>
>>>>
>>>
>>
>
--------------------------------------------------------------------~->
>>>>>
>>>>>
>>>>> Yahoo! Groups Links
>>>>>
>>>>>
>>>>>
>>>>
>>>>
>>>>
>>>> ------------------------ Yahoo! Groups Sponsor
>>>> --------------------~--> $4.98 domain names from Yahoo!. Register
>>>> anything. http://us.click.yahoo.com/Q7_YsB/neXJAA/yQLSAA/67folB/TM
>>>>
>>>
>>
>
--------------------------------------------------------------------~->
>>>>
>>>>
>>>> Yahoo! Groups Links
>>>>
>>>>
>>>>
>>>
>>>
>>>
>>> Yahoo! Groups Sponsor
>>>
>>> ADVERTISEMENT
>>>
>>>
>>
>
<http://us.ard.yahoo.com/SIG=129j18rva/M=298184.5639630.6699735.3001176/
>> D=gr
>>>
>>
>
oups/S=1705115386:HM/EXP=1103395007/A=2495208/R=0/SIG=11egg01lg/*http://
>> www.
>>> netflix.com/Default?mqso=60188914> click here
>>>
>>>
>>
>
<http://us.adserver.yahoo.com/l?M=298184.5639630.6699735.3001176/D=group
>> s/S=
>>>> HM/A=2495208/rand=815381014>
>>>
>>> _____
>>>
>>> Yahoo! Groups Links
>>>
>>>
>>> * To visit your group on the web, go to:
>>> http://groups.yahoo.com/group/firebird-support/
>>>
>>>
>>> * 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]
>>>
>>>
>>>
>>> ------------------------ Yahoo! Groups Sponsor
>>> --------------------~--> Make a clean sweep of pop-up ads. Yahoo!
>>> Companion Toolbar. Now with Pop-Up Blocker. Get it for free!
>>> http://us.click.yahoo.com/L5YrjA/eSIIAA/yQLSAA/67folB/TM
>>>
>>
>
--------------------------------------------------------------------~->
>>>
>>>
>>> Yahoo! Groups Links
>>>
>>>
>>>
>>
>>
>>
>> Yahoo! Groups Sponsor
>>
>> ADVERTISEMENT
>>
>>
>
<http://us.ard.yahoo.com/SIG=129p5ej0e/M=298184.5639630.6699735.3001176/
> D=gr
>>
>
oups/S=1705115386:HM/EXP=1103396625/A=2495202/R=0/SIG=11evjk50a/*http://
> www.
>> netflix.com/Default?mqso=60188913> click here
>>
>>
>
<http://us.adserver.yahoo.com/l?M=298184.5639630.6699735.3001176/D=group
> s/S=
>>> HM/A=2495202/rand=469013377>
>>
>> _____
>>
>> Yahoo! Groups Links
>>
>>
>> * To visit your group on the web, go to:
>> http://groups.yahoo.com/group/firebird-support/
>>
>>
>> * 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]
>>
>>
>>
>> ------------------------ Yahoo! Groups Sponsor
>> --------------------~--> Make a clean sweep of pop-up ads. Yahoo!
>> Companion Toolbar. Now with Pop-Up Blocker. Get it for free!
>> http://us.click.yahoo.com/L5YrjA/eSIIAA/yQLSAA/67folB/TM
>>
>
--------------------------------------------------------------------~->
>>
>>
>> Yahoo! Groups Links
>>
>>
>>
>
>
>
> Yahoo! Groups Sponsor
>
> ADVERTISEMENT
>
>
<http://us.ard.yahoo.com/SIG=1298eg03m/M=294855.5468653.6549235.3001176/
D=gr
>
oups/S=1705115386:HM/EXP=1103397773/A=2455396/R=0/SIG=119u9qmi7/*http://
smal
> lbusiness.yahoo.com/domains/> click here
>
>
<http://us.adserver.yahoo.com/l?M=294855.5468653.6549235.3001176/D=group
s/S=
>> HM/A=2455396/rand=353799052>
>
> _____
>
> Yahoo! Groups Links
>
>
> * To visit your group on the web, go to:
> http://groups.yahoo.com/group/firebird-support/
>
>
> * 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]
>
>
>
> ------------------------ Yahoo! Groups Sponsor
> --------------------~--> Make a clean sweep of pop-up ads. Yahoo!
> Companion Toolbar. Now with Pop-Up Blocker. Get it for free!
> http://us.click.yahoo.com/L5YrjA/eSIIAA/yQLSAA/67folB/TM
>
--------------------------------------------------------------------~->
>
>
> Yahoo! Groups Links
>
>
>


Yahoo! Groups Sponsor
ADVERTISEMENT






Yahoo! Groups Links

To visit your group on the web, go to:
http://groups.yahoo.com/group/firebird-support/

To unsubscribe from this group, send an email to:
firebird-support-unsubscribe@yahoogroups.com

Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service.