May 24, 2010 at 8:08 am
I inherited some SQL logic that I need to alter. The SQL employs LEFT OUTER JOINS extensively to provide optional data.
The request is to use a person’s Primary Name (PRI) if their Preferred Name (PRF) is not available. The name is still considered an optional item although the Primary Name should always be present. I’m not sure if I can do this with SQL or if I have to bring both sets of data back and let a program worry about if the preferred name is present or not.
I have added the 'LEFT OUTER JOIN BS_NAMES F' code below which will obtain the Primary Name data if present. Is there a way that I can add this code to the ‘LEFT OUTER JOIN BS_NAMES E’ statement directly above it and have SQL do the work of bring back the right name; preferred if available and, if not, primary if available?
INNER JOIN BS_PAY_CHECK B
ON A.COMPANY = B.COMPANY
AND A.EMPLID = B.EMPLID
INNER JOIN BS_POSN_INCUMBENT C
ON A.REPORTS_TO = C.POSITION_NBR
LEFT OUTER JOIN BS_NAMES E
ON C.EMPLID = E.EMPLID
AND E.NAME_TYPE = 'PRF'
AND E.EFFDT = (SELECT MAX(E_ED.EFFDT)
FROM BS_NAMES E_ED
WHERE E.EMPLID = E_ED.EMPLID
AND E.NAME_TYPE = E_ED.NAME_TYPE
AND E_ED.EFFDT <= SUBSTRING(CONVERT(CHAR,GETDATE(),121), 1, 10))
LEFT OUTER JOIN BS_NAMES F
ON C.EMPLID = F.EMPLID
AND F.NAME_TYPE = 'PRI'
AND F.EFFDT = (SELECT MAX(F_ED.EFFDT)
FROM BS_NAMES F_ED
WHERE F.EMPLID = F_ED.EMPLID
AND F.NAME_TYPE = F_ED.NAME_TYPE
AND F_ED.EFFDT <= SUBSTRING(CONVERT(CHAR,GETDATE(),121), 1, 10))
LEFT OUTER JOIN BS_DEPT_TBL G
ON A.DEPTID = G.DEPTID
LEFT OUTER JOIN BS_POSITION_VIEW H
ON A.POSITION_NBR = H.POSITION_NBR
May 24, 2010 at 9:12 am
Check out ISNULL or COALESCE in BOL. Should give you what you need here.
Try something like ISNULL(E.Name, F.Name) Name in your select.
May 24, 2010 at 9:36 am
Good call. I was focussed on the eliminating the second left outer join for "PRI". Adding a statement like ", COALESCE(E.NAME, F.NAME) 'REPORTSTO_PRF_NAME'" within the fields portion of the Select statement produces the output I was looking for.
Thank you,
Steve
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply