Please remove me from this mailinglist

Best regards

Helmut Neureiter


-----Ursprüngliche Nachricht-----
Von: ib-support@yahoogroups.com [mailto:ib-support@yahoogroups.com]
Gesendet: Donnerstag, 13. Juni 2002 08:28
An: ib-support@yahoogroups.com
Betreff: [ib-support] Digest Number 837



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


------------------------------------------------------------------------

There are 16 messages in this issue.

Topics in this digest:

1. RE: Join Table with self
From: Helen Borrie <helebor@...>
2. selecting from joined views hangs !
From: Amrita Chaudhury <achaudhu@...>
3. Re: Re: Slow Connections
From: "Paul Schmidt" <paul@...>
4. Large db file question
From: "lfeliz" <lfeliz@...>
5. IBPerl question
From: "Mark Shapiro" <shapirom@...>
6. Re: Firebird Client
From: Aage Johansen <aagjohan@...>
7. Re: ODBC with BDE
From: "andre_peron" <andre_peron@...>
8. RE: Large db file question
From: "Leyne, Sean" <sleyne@...>
9. Frequent "Guardian Starting" messages - Normal??
From: "Phil Scadden" <p.scadden@...>
10. RE: Frequent "Guardian Starting" messages - Normal??
From: "Leyne, Sean" <sleyne@...>
11. RE: Frequent "Guardian Starting" messages - Normal??
From: "Phil Scadden" <p.scadden@...>
12. RE: Frequent "Guardian Starting" messages - Normal??
From: "Leyne, Sean" <sleyne@...>
13. Re: IBPerl question
From: Adam Clarke <Adam.Clarke@...>
14. RE: Frequent "Guardian Starting" messages - Normal??
From: "Phil Scadden" <p.scadden@...>
15. RE: Frequent "Guardian Starting" messages - Normal??
From: Helen Borrie <helebor@...>
16. Re: Re: unload and load
From: "Ben Johnson" <ben_johns@...>


________________________________________________________________________
________________________________________________________________________

Message: 1
Date: Thu, 13 Jun 2002 00:41:33 +1000
From: Helen Borrie <helebor@...>
Subject: RE: Join Table with self

At 04:04 PM 12-06-02 +0200, you wrote:

>Hi,
>
>All records forming a transaction are linked together via a field called
>transref.
>
>ATable
>----------
>
>transref amount actn accountid
> 001 30.00 'C' 1232345
> 001 10.00 'D' EBFS
> 001 20.00 'D' Bank1
>
>What I want is all records = 'Bank1' with the corresponding 'C' linked via
>
>the transref. Effectively what I want to do is a left join of a table on

its

>
>self for a select record value. I want the accountid1,
>
>accountid2 values in the same row(ie the association) done via SQL.
>
>The result I want from the above table is:-
>
>transref amount actn accountid1 accountid2
> 001 30.00 'C' Bank1 1232345
>
>I hope this explains my needs.

Much better. :))

select a.transref, b.amount, b.actn,
a.accountid as accountid1,
b.accountid as accountid2
from ATable a
left join ATable b
on a.transref = b.transref
where a.accountid = 'Bank1'
and b.actn = 'C'

This will give you one row for each occurrence of accountid = 'Bank1' in
the entire table. Those that have 'C' rows with matching transref will be
complete; those that don't will have nulls in all the b.columns.

heLen

All for Open and Open for All
Firebird Open SQL Database · http://firebirdsql.org ·
http://users.tpg.com.au/helebor/
_______________________________________________________



________________________________________________________________________
________________________________________________________________________

Message: 2
Date: Wed, 12 Jun 2002 10:58:29 -0500
From: Amrita Chaudhury <achaudhu@...>
Subject: selecting from joined views hangs !

Hi ,

I have three tables,
Table A has index date, hour , idx1 and idx2
Table B has index date, hour, idx1
Table C has index date, hour, idx1, and idx3

I have a stored proc that has to calculate certain values at date, hour,
idx1 level
using fields from thesee tables.

So, I created three views that summarized these tables at the common index
level and
am creating a join of the three in the stored proc to grab data for each
date, hour, idx1 level
and generate the calculated columns for each, like

for select view_a.date, view_a.hour, view_a.idx1, view_a.field1, ...,
view_b.field2, ...,
view_c.field2 from view_a inner join view_b inner join view_c

This select hangs when executed in the stored proc, or even from ibconsole.