Hi
I want to find records in a UD01 Table in one company where they do not match the same UD01 record in the other company or the record doesn’t exist in the other company.
The first part is simple enough:
select
[UD01].[Key1] as [UD01_Key1],
[UD01].[Character02] as [UD01_Character02],
[UD01].[Character01] as [UD01_Character01]
from Ice.UD01 as UD01
inner join Ice.UD01 as UD011 on
UD01.Key1 = UD011.Key1
and not (UD01.Character01 = UD011.Character01
or not UD01.Character02 = UD011.Character02)
and ( UD011.Company = ‘50108140’ )
where (UD01.Company = ‘50008065’ and not UD01.Character02 = ‘’ and UD01.Key1 like ‘994’)
order by UD01.Key1
But then how do I check for if the record doesn’t exist in the 50108140 company?
You need a LEFT JOIN first since an INNER JOIN won’t fetch rows if you don’t have a matching records on both side. Once you’ve put the LEFT JOIN in place, a WHERE clause with UD011.company IS NULL will identify the rows with no records in UD011.
Thanks for the tip Jose.
EXCEPT seems like the sort of thing I am looking for.
I’ve created a new subquery that is a union with the first and then added an other subquery which is an “Except” type but it doesn’t pull through the missing records from the other company.
This is my new query:
select
[UD011].[Company] as [UD011_Company],
[UD01].[Key1] as [UD01_Key1],
[UD01].[Key2] as [UD01_Key2],
[UD01].[Key3] as [UD01_Key3],
[UD01].[Key4] as [UD01_Key4],
[UD01].[Key5] as [UD01_Key5],
[UD01].[Character01] as [UD01_Character01],
[UD01].[Character02] as [UD01_Character02]
from Ice.UD01 as UD01
inner join Ice.UD01 as UD011 on
UD01.Key1 = UD011.Key1
and not (UD01.Character01 = UD011.Character01
or not UD01.Character02 = UD011.Character02)
and ( UD011.Company = ‘50108140’ )
where (UD01.Company = ‘50008065’ and not UD01.Character02 = ‘’ and UD01.Key1 like ‘994’)
union
select
(‘50108140’) as [Calculated_Company],
[UD012].[Key1] as [UD012_Key1],
[UD012].[Key2] as [UD012_Key2],
[UD012].[Key3] as [UD012_Key3],
[UD012].[Key4] as [UD012_Key4],
[UD012].[Key5] as [UD012_Key5],
[UD012].[Character01] as [UD012_Character01],
[UD012].[Character02] as [UD012_Character02]
from Ice.UD01 as UD012
where (UD012.Company = ‘50008065’ and UD012.Key1 like ‘994’ and not UD012.Character02 = ‘’)
except
select
[UD013].[Company] as [UD013_Company],
[UD013].[Key1] as [UD013_Key1],
[UD013].[Key2] as [UD013_Key2],
[UD013].[Key3] as [UD013_Key3],
[UD013].[Key4] as [UD013_Key4],
[UD013].[Key5] as [UD013_Key5],
[UD013].[Character01] as [UD013_Character01],
[UD013].[Character02] as [UD013_Character02]
from Ice.UD01 as UD013
Hi Alexandre,
That sounds like it might be easier and it seems to be giving me the results I expect
This is the BAQ:
select
[UD011].[Company] as [UD011_Company],
[UD01].[Key1] as [UD01_Key1],
[UD01].[Key2] as [UD01_Key2],
[UD01].[Key3] as [UD01_Key3],
[UD01].[Key4] as [UD01_Key4],
[UD01].[Key5] as [UD01_Key5],
[UD01].[Character01] as [UD01_Character01],
[UD01].[Character02] as [UD01_Character02]
from Ice.UD01 as UD01
inner join Ice.UD01 as UD011 on
UD01.Key1 = UD011.Key1
and not (UD01.Character01 = UD011.Character01
or not UD01.Character02 = UD011.Character02)
and ( UD011.Company = ‘50108140’ )
where (UD01.Company = ‘50008065’ and not UD01.Character02 = ‘’ and UD01.Key1 like ‘994’)
union
select
(‘50108140’) as [Calculated_Company],
[UD012].[Key1] as [UD012_Key1],
[UD012].[Key2] as [UD012_Key2],
[UD012].[Key3] as [UD012_Key3],
[UD012].[Key4] as [UD012_Key4],
[UD012].[Key5] as [UD012_Key5],
[UD012].[Character01] as [UD012_Character01],
[UD012].[Character02] as [UD012_Character02]
from Ice.UD01 as UD012
left outer join Ice.UD01 as UD013 on
UD012.Key1 = UD013.Key1
and ( UD013.Company = ‘50108140’ )
where (UD012.Company = ‘50008065’ and UD012.Key1 like ‘994’ and not UD012.Character02 = ‘’)
and UD013.Company is null
order by UD01.Key1
