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

Author | Mon Mariola |

Post date | 2010-03-17T02:06:15Z |

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.

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]