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