Subject | RE: [firebird-support] About CORE-2327 |
---|---|

Author | Leyne, Sean |

Post date | 2010-03-16T21:29:01Z |

Ruben,

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

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

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

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.3Before working on creating a Derived Table let's start by cleaning up/simplifying the original SQL.

>

> With the select:

> selectThis above can be simplified to:

> (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

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

> whereThis can be simplified to:

> 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')

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