Subject | Re: [firebird-support] Covert not in to not exists |
---|---|

Author | Svein Erling Tysvaer |

Post date | 2006-08-03T11:47:28Z |

That's simple Guido, just move TO2PCID and BLPCID into the where clause!

select BLPCID,sum(BLGROSS_SALES),sum(BLRETURNS),sum(BLNETSALES),

sum(BLDELIVERY_QUANTITY),sum(BLBACKLOG) from BACKLOG

where BLCSGPID3=49 and

not exists(select * from TURNOVER_PC_SUBGROUP where

TO2CSGPID3=49 and TO2PCID = BLPCID)

and BLPCID is not null

group by BLPCID

I added the 'and BLOPCID is not null' because I think the handling of

NULL values may be slightly different between NOT IN and NOT EXISTS.

Set

Guido Klapperich wrote:

select BLPCID,sum(BLGROSS_SALES),sum(BLRETURNS),sum(BLNETSALES),

sum(BLDELIVERY_QUANTITY),sum(BLBACKLOG) from BACKLOG

where BLCSGPID3=49 and

not exists(select * from TURNOVER_PC_SUBGROUP where

TO2CSGPID3=49 and TO2PCID = BLPCID)

and BLPCID is not null

group by BLPCID

I added the 'and BLOPCID is not null' because I think the handling of

NULL values may be slightly different between NOT IN and NOT EXISTS.

Set

Guido Klapperich wrote:

> I have the following query

>

> select BLPCID,sum(BLGROSS_SALES),sum(BLRETURNS),sum(BLNETSALES),

> sum(BLDELIVERY_QUANTITY),sum(BLBACKLOG) from BACKLOG

> where BLCSGPID3=49 and

> BLPCID not in (select TO2PCID from TURNOVER_PC_SUBGROUP where

> TO2CSGPID3=49)

> group by BLPCID

>

> I have read in the malinglist, that FB 2.0 don't use indexes with 'not

> in' any more. Therefore I want to use 'not exists', but I'm not able to

> convert the query. Can anybody help me?

>

> Regards

>

> Guido