need help with max effective date - Naith?

[mwhalen]

here's my code:



SELECT
PS_UWO_HEAV_DEG_VW."EMPLID",
PS_UWO_HE_APLNT_VW."ACAD_PLAN", PS_UWO_HE_APLNT_VW."EFFDT", PS_UWO_HE_APLNT_VW."DESCR",
PS_UWO_HEAV_DEG_VW."AV_CLASS_YR",
PS_UWO_HE_A_GRP_VW."EFFDT", PS_UWO_HE_A_GRP_VW."DESCR"
FROM
"SYSADM"."PS_UWO_HEAV_DEG_VW" PS_UWO_HEAV_DEG_VW,
"SYSADM"."PS_UWO_HE_APLNT_VW" PS_UWO_HE_APLNT_VW,
"SYSADM"."PS_UWO_HE_A_GRP_VW" PS_UWO_HE_A_GRP_VW
WHERE
PS_UWO_HEAV_DEG_VW."ACAD_PLAN" = PS_UWO_HE_APLNT_VW."ACAD_PLAN" AND
PS_UWO_HEAV_DEG_VW."ACAD_ORG" = PS_UWO_HE_A_GRP_VW."ACAD_GROUP" AND
(PS_UWO_HEAV_DEG_VW."EMPLID" = '300012881' OR
PS_UWO_HEAV_DEG_VW."EMPLID" = '000313286' OR
PS_UWO_HEAV_DEG_VW."EMPLID" = '000005178') AND
PS_UWO_HE_APLNT_VW."EFFDT" =
(SELECT MAX(PS_UWO_HE_APLNT_VW."EFFDT&quot FROM PS_UWO_HE_APLNT_VW
WHERE PS_UWO_HEAV_DEG_VW."ACAD_PLAN" = PS_UWO_HE_APLNT_VW."ACAD_PLAN" and
TO_NUMBER(TO_CHAR(PS_UWO_HE_APLNT_VW.&quot;EFFDT&quot;,'yyyy'))<=PS_UWO_HEAV_DEG_VW.&quot;AV_CLASS_YR&quot AND
PS_UWO_HE_A_GRP_VW.&quot;EFFDT&quot; =
(SELECT MAX(PS_UWO_HE_A_GRP_VW2.&quot;EFFDT&quot FROM &quot;SYSADM&quot;.&quot;PS_UWO_HE_A_GRP_VW&quot; PS_UWO_HE_A_GRP_VW2
WHERE PS_UWO_HEAV_DEG_VW.&quot;ACAD_ORG&quot; = PS_UWO_HE_A_GRP_VW2.&quot;ACAD_GROUP&quot; and
TO_NUMBER(TO_CHAR(PS_UWO_HE_A_GRP_VW2.&quot;EFFDT&quot;,'yyyy'))<=PS_UWO_HEAV_DEG_VW.&quot;AV_CLASS_YR&quot
ORDER BY
PS_UWO_HEAV_DEG_VW.&quot;EMPLID&quot; ASC



the problem is I came across one id that will have something in the acad_plan table BUT NOT a corresponding record in the plant table. I still want that record to come through in the rare case that there won't be a matching record in the plant table.

 

[skuhlman]

Are you trying to do this within a SQL Server Stored Procedure?

 

[DanENW]

Try a left outer join where the acad_plan table is on the &quot;left&quot; and the plant table is on the &quot;right&quot; when you are drawing the links; then right-click the link, select options, and choose left outer join. This should work as long as you don't have any restrictions on the table being left outer joined, the plant table in this case.