Hiya,
I'm a little stuck with this one but it is probably my understanding off how outer joins are affected with more conditions that is the problem.
Basically this first query works and delivers all the records in office even if they have a null for sales area which is as I would have expected.
SELECT
offout.name as office_name,
grpout.name as sales_area
FROM
edw.t_office_all offout,
edw.t_group_all grpout
WHERE
offout.sales_area = grpout.id (+)
However when I try adding some correlation columns for extracting data from a warehouse I only get the office records that have a sales area specified. Office records that have null for sales area are not returned.
SELECT
offout.name as office_name,
grpout.name as sales_area
FROM
edw.t_office_all offout,
edw.t_group_all grpout
WHERE
offout.SNAPSHOT_TIME =
(
SELECT
max(offin.SNAPSHOT_TIME)
FROM
edw.t_office_all offin
WHERE
offin.id = offout.id
) AND
grpout.SNAPSHOT_TIME =
(
SELECT
max(grpin.SNAPSHOT_TIME)
FROM
edw.t_group_all grpin
WHERE
grpin.id = grpout.id
)
AND offout.sales_area = grpout.id (+)
Thanks for any help.
Cheersselect offout.name as office_name
, grpout.name as sales_area
from edw.t_office_all offout
left outer
join edw.t_group_all grpout
on offout.sales_area = grpout.id
and grpout.SNAPSHOT_TIME =
( select max(grpin.SNAPSHOT_TIME)
from edw.t_group_all grpin
where grpin.id = grpout.id )
where offout.SNAPSHOT_TIME
= ( select max(offin.SNAPSHOT_TIME)
from edw.t_office_all offin
where offin.id = offout.id )
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment