I appreciate the interest shown and I'm sorry I've wasted your time. I was
convinced I had installed on my computer Firebird version 2.1.3.

For CORE-2327 is referring to the Firebird Tracker:
http://tracker.firebirdsql.org/browse/CORE-2327. You're right and I should
have placed the link.

And on the design of the selects, I tried to minimize to see it was a bug in
Firebird with derived tables. The original select is much more complex.
Anyway I'll try to simplify it according to your ideas.

Thank you.
Ruben Marti.

----- Original Message -----
From: Leyne, Sean
To: firebird-support@yahoogroups.com
Sent: Tuesday, March 16, 2010 10:29 PM
Subject: RE: [firebird-support] About CORE-2327



Ruben,

FYI: A message with an un-informative subject as yours (what the H*** is
Core-2327???) is not likely to get much response.

> With Firebird 2.1.3
>
> With the select:

Before working on creating a Derived Table let's start by cleaning
up/simplifying the original SQL.

> select
> (select first 1
> P1.ARTICULO
> from GES_LIN_ROLLOS L1
> inner join EMP_LOTES P1 on
> P1.EMPRESA = L1.EMPRESA and
> P1.NUMERO = L1.PIEZA
> where
> L1.EMPRESA = R.EMPRESA and
> L1.ROLLO = R.ROLLO and
> P1.ARTICULO = 'ABCDEFGHI') as ARTICULO ,
> R.EMPRESA,
> R.ROLLO
> from GES_CAB_ROLLOS R

This above can be simplified to:

select
A.ARTICULO,
R.EMPRESA,
R.ROLLO
from GES_CAB_ROLLOS R

There is no need for the "(select first 1 ...)" since the criteria is
already established in the INNER JOIN

> where
> R.EMPRESA = 1 and
> exists (select 1 from GES_LIN_ROLLOS L3
> inner join EMP_LOTES P3 on
> P3.EMPRESA = L3.EMPRESA and
> P3.NUMERO = L3.PIEZA
> where
> L3.EMPRESA = R.EMPRESA and
> L3.ROLLO = R.ROLLO and
> P3.ARTICULO = 'ABCDEFGHI')

This can be simplified to:

where
R.EMPRESA = 1

Again the "AND EXISTS( ...)" is already encompassed/implied by the INNER
JOIN criteria

So, what you end up with is:

select
A.ARTICULO,
R.EMPRESA,
R.ROLLO
from
GES_CAB_ROLLOS R
inner join EMP_ARTICULOS A on
A.EMPRESA = R.EMPRESA and
A.ARTICULO = (
select
first 1 P2.ARTICULO
from GES_LIN_ROLLOS L2
inner join EMP_LOTES P2 on
P2.EMPRESA = L2.EMPRESA and
P2.NUMERO = L2.PIEZA
where
L2.EMPRESA = R.EMPRESA and
L2.ROLLO = R.ROLLO and
P2.ARTICULO = 'ABCDEFGHI'
)
where
R.EMPRESA = 1

At this point I don't think that a derived table is required.

Sean






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