Thanks and will have a look at the SQL logic, but I know that the results
are right and that there is a difference

between doing a left join in the first join and an inner join in the first
join. This is when running it on an Interbase

database.

I need this SQL to get data to populate 2 listboxes, one with all the unique
tables in the database and the

other one showing all the fields for that particular table plus all the
indexes. As one field can be in more than one index

the fields won't be unique, but the combination table + field will be.



RBS



_____

From: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] On Behalf Of Svein Erling Tysvaer
Sent: 28 September 2008 18:44
To: firebird-support@yahoogroups.com
Subject: Re: [firebird-support] No current record for fetch operation



Hi!

I agree that Firebird (at least 1.5) poorly handles situations where
LEFT JOIN is written before INNER JOIN. However, I find your particular
SQL a bit contradictory:

When doing INNER JOIN RDB$TYPES T ON (F.RDB$FIELD_TYPE = T.RDB$TYPE),
you basically say that there must be a record in both F and T, whereas
you in the line before has said that F may not exist. I think that makes
the LEFT JOIN actually be an INNER JOIN and in general I'd say it is
wrong to use the right table of a LEFT JOIN in an INNER JOIN (*).

That being said, the error message you report doesn't sound like a
sensible error message for the situation and I think Firebird ought to
be able to execute your query anyway (and I don't know whether this is
what causes the error message or not). Though I don't know what causes
the error, if it has to do with the dialect or something else.

If your third line had contained a reference to RF and not F (thereby
making the whole statement more sensible), I'd say it could be an error
worth looking more into (note, I haven't checked any bug tracker, so it
may already be a known issue).

Set

(*) I sometimes do similar things myself, but then when I know a record
must exist in one of several tables:

SELECT D.* FROM A
LEFT JOIN B on A.A = B.A
LEFT JOIN C on A.A = C.A
INNER JOIN D on D.B = coalesce(B.B, C.B)

RB Smissaert wrote:

> Using FB 1.5 on a converted IB 5.6 dialect 1 database.
> I connect with the ODBC driver from IBPhoenix and this is with ADO in VB6.
>
> When I run this SQL:
>
> SELECT
> R.RDB$RELATION_ID,
> RF.RDB$RELATION_NAME,
> RF.RDB$FIELD_NAME,
> T.RDB$TYPE_NAME,
> RF.RDB$FIELD_POSITION,
> F.RDB$FIELD_LENGTH,
> RF.RDB$SYSTEM_FLAG
> FROM
> RDB$RELATION_FIELDS RF
> LEFT JOIN RDB$FIELDS F ON (RF.RDB$FIELD_SOURCE = F.RDB$FIELD_NAME)
> INNER JOIN RDB$TYPES T ON (F.RDB$FIELD_TYPE = T.RDB$TYPE)
> INNER JOIN RDB$RELATIONS R ON (RF.RDB$RELATION_NAME = R.RDB$RELATION_NAME)

> WHERE
> T.RDB$FIELD_NAME = 'RDB$FIELD_TYPE'
>
> I get the error message:
>
> [ODBC Firebird driver][Firebird] No current record for fetch operation
>
> And I understand that this is caused by the left join before the 2 inner
> joins. Indeed if I change the left join to an inner join it runs fine.
> There is no such problem with IB 5.6.
>
> It is not a major problem as I can do multiple selects in the application
> and join in SQLite, but I just wonder if this has been fixed in FB 2.0 or
> 2.1 or has this to do with the database dialect?
>
>
> RBS

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