Where not exists - How to SQL?

[#1]

Hello,

I have a bizzare scenario.

I have a table (and many other rows i dont wanna mention)

T1:

D_Key

D_Key as Fod_Key_1

from

Transaktioner

where type = 'f'

concatenate (t1)

Load

D_Key

D_Key as Fod_Key_1

from Transaktioner

where type = 'd'

and not exists(Fod_Key_1,D_Key)

drop field FOD_KEY_1;

I simply cannot figure out how to repliacte this logic into SQL.

T1 consists of 100.000 rows

And the concatenated table consists of 6.000.000 rows.

As logic i would think when these two are concatenated there would be no less than 5.900.000 rows. However there is like only 5.600.000 rows when my scripts end?

[Anonymous]

hi thomas,

try:

T1:

D_Key

D_Key as Fod_Key_1

from

Transaktioner

where type = 'f'

concatenate (t1)

Load

D_Key

D_Key as Fod_Key_1

from Transaktioner

where type = 'd'

and not exists(D_Key)

[#3]

I think u misunderstood my questions.

I wanna know how i can do the same logic in SQL.

[#4]

Hi Thomas,

I am not 100% sure if I understood you correctly, but try this to statements.

T1:

Load

  *;

SQL Select DISTINCT

  D_Key

  D_Key as Fod_Key_1

from

  Transaktioner

where

  type = 'f'

  Or type = 'd'

The first one may work if your requested fields are always the same (no matter if type is f or d).

T1:

Load

  *;

SQL Select DISTINCT

  D_Key

  D_Key as Fod_Key_1

from

  Transaktioner

where

  type = 'f'

  Or (type = 'd' and not exists (Select 1 from Transaktioner t2 where type = f and Transaktioner.D_Key = t2.D_Key)

This may not be the fastest solution, but at least it should work.

Cheers,

Claus

[#5]

But how come when i load this table for it self

t2:

load

D_key

,d_Key as Fod_Key_1

from transkationer

where not exists(fod_key_1,D_key)

it returns less rows? When fod_key_1, and d_key is the same?

when only loading one table?

so without exists it has 6.000.000 rows.

but the exists it has 5.800.000 rows?

[sasiparupudi1]

try

2:

load

D_key

,d_Key as Fod_Key_1

from transkationer

where not exists(D_key,D_key)

and see if it returns the correct records?

actually you can not access aliases in the conditions.. My assumption is that it is probably eliminating null values. do you perhaps have nulls in that column?

hth

Sasi