Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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)
I think u misunderstood my questions.
I wanna know how i can do the same logic in SQL.
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
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?
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