How to return one of several values

  • Greetings to the group...

    I'm working on an interface and can't figure out how to do this in SQL...

    If an employee has a home phone, I want to return that... if there is no home phone listed, then return a cell number.

    Currently we have to make sure everyone with a phone number has a "home" phone, even if it is a cell phone.

    If they have both, the sql returns two rows and that messes up the target system.

    It is kind of lengthy, but here is the select statement if it helps to clarify what I'm doing...

    SELECT A.EMPLID AS PERSON_NBR,

    (SELECT '005/'+'116'+ '/' + (LEFT(A.DEPTID,5))+ '/' + A.JOBCODE + '/' + 'NA'+ '/' + 'NA' + '/' + 'NA') AS LABORACCOUNT,

    0 AS HOURLY_RT,

    '' AS SUPERVISOR_NAME,

    A.EMPL_STATUS AS EMPL_STATUS,

    D.SERVICE_DT AS HIRE_DT,

    '' AS AC_DEVICEGRP,

    CASE A.GRADE

    WHEN '01' THEN 'FABCO Salaried Non Exempt'

    WHEN '02' THEN 'FABCO Salaried Non Exempt'

    WHEN '03' THEN 'FABCO Salaried Non Exempt'

    WHEN '04' THEN 'FABCO Salaried Non Exempt'

    WHEN '05' THEN 'FABCO Salaried Non Exempt'

    WHEN '06' THEN 'FABCO Salaried Non Exempt'

    WHEN '07' THEN 'FABCO Salaried Non Exempt'

    WHEN '08' THEN 'FABCO Salaried Non Exempt'

    WHEN '09' THEN 'FABCO Salaried Non Exempt'

    WHEN '10' THEN 'FABCO Salaried Non Exempt'

    WHEN '11' THEN 'FABCO Salaried Exempt Eligible'

    WHEN '12' THEN 'FABCO Salaried Exempt Eligible'

    WHEN '13' THEN 'FABCO Salaried Exempt'

    WHEN '14' THEN 'FABCO Salaried Exempt'

    WHEN '15' THEN 'FABCO Salaried Exempt'

    WHEN '16' THEN 'FABCO Salaried Exempt'

    WHEN '17' THEN 'FABCO Salaried Exempt'

    WHEN '18' THEN 'FABCO Salaried Exempt'

    WHEN '19' THEN 'FABCO Salaried Exempt'

    WHEN '20' THEN 'FABCO Salaried Exempt'

    WHEN '21' THEN 'FABCO Salaried Exempt'

    WHEN '22' THEN 'FABCO Salaried Exempt'

    WHEN '23' THEN 'FABCO Salaried Exempt'

    WHEN '24' THEN 'FABCO Salaried Exempt'

    WHEN '25' THEN 'FABCO Salaried Exempt'

    ELSE '**** BAD PAY RULE ****' END AS PAYRULE,

    0 AS AC_BADGENUM,

    F.BIRTHDATE AS BIRTHDATE,

    G.FIRST_NAME AS FIRST_NAME,

    G.LAST_NAME AS LAST_NAME,

    H.ADDRESS_TYPE AS ADDRESS_TYPE,

    H.ADDRESS1 AS ADDRESS1,

    H.CITY AS CITY,

    H.STATE AS STATE,

    H.POSTAL AS POSTAL,

    H.COUNTRY AS COUNTRY,

    I.PHONE_TYPE AS PHONE_TYPE,

    --'HOME' AS PHONE_TYPE,

    I.PHONE AS PHONE,

    A.JOBCODE AS JOBCODE,

    A.EFFDT AS EFFDT,

    A.SHIFT AS SHIFT,

    D.SERVICE_DT AS SERVICE_DT,

    A.ACC_CLOCK_ID AS ACC_CLOCK_ID,

    A.EMPLID,

    CASE A.GRADE

    WHEN '01' THEN 'Fabco Salaried Grade 1-15'

    WHEN '02' THEN 'Fabco Salaried Grade 1-15'

    WHEN '03' THEN 'Fabco Salaried Grade 1-15'

    WHEN '04' THEN 'Fabco Salaried Grade 1-15'

    WHEN '05' THEN 'Fabco Salaried Grade 1-15'

    WHEN '06' THEN 'Fabco Salaried Grade 1-15'

    WHEN '07' THEN 'Fabco Salaried Grade 1-15'

    WHEN '08' THEN 'Fabco Salaried Grade 1-15'

    WHEN '09' THEN 'Fabco Salaried Grade 1-15'

    WHEN '10' THEN 'Fabco Salaried Grade 1-15'

    WHEN '11' THEN 'Fabco Salaried Grade 1-15'

    WHEN '12' THEN 'Fabco Salaried Grade 1-15'

    WHEN '13' THEN 'Fabco Salaried Grade 1-15'

    WHEN '14' THEN 'Fabco Salaried Grade 1-15'

    WHEN '15' THEN 'Fabco Salaried Grade 1-15'

    WHEN '16' THEN 'Fabco Salaried Grade 16-19'

    WHEN '17' THEN 'Fabco Salaried Grade 16-19'

    WHEN '18' THEN 'Fabco Salaried Grade 16-19'

    WHEN '19' THEN 'Fabco Salaried Grade 16-19'

    WHEN '20' THEN 'Fabco Salaried Grade 20+'

    WHEN '21' THEN 'Fabco Salaried Grade 20+'

    WHEN '22' THEN 'Fabco Salaried Grade 20+'

    WHEN '23' THEN 'Fabco Salaried Grade 20+'

    WHEN '24' THEN 'Fabco Salaried Grade 20+'

    WHEN '25' THEN 'Fabco Salaried Grade 20+'

    ELSE '**** BAD ACCRUAL PROFILE ****' END AS ACCRUAL_PROFILE,

    '13404' AS TIMEZONE,

    A.PAYGROUP AS PAYGROUP,

    J.ACNETLOGON

    FROM dbo.PS_JOB A INNER JOIN

    dbo.PS_PERSON_NAME B ON A.EMPLID = B.EMPLID INNER JOIN

    dbo.PS_EMPLOYMENT D ON A.EMPLID = D.EMPLID LEFT OUTER JOIN

    dbo.PS_AC_KRONOS_INT E ON A.EMPLID = E.EMPLID INNER JOIN

    dbo.PS_PERSON F ON A.EMPLID = F.EMPLID INNER JOIN

    dbo.PS_NAMES G ON A.EMPLID = G.EMPLID INNER JOIN

    dbo.PS_ADDRESSES H ON A.EMPLID = H.EMPLID LEFT OUTER JOIN

    dbo.PS_PERSONAL_PHONE I ON A.EMPLID = I.EMPLID INNER JOIN

    dbo.PS_ACNETWORK_LOGON J ON A.EMPLID = J.EMPLID

    WHERE (A.EFFDT =

    (SELECT MAX(A_ED.EFFDT)

    FROM PS_JOB A_ED

    WHERE A.EMPLID = A_ED.EMPLID

    AND A.EMPL_RCD = A_ED.EMPL_RCD

    AND A_ED.EFFDT <= SUBSTRING(CONVERT(CHAR, GETDATE()), 1, 11)))

    AND (A.EFFSEQ =

    (SELECT MAX(A_ES.EFFSEQ)

    FROM PS_JOB A_ES

    WHERE A.EMPLID = A_ES.EMPLID

    AND A.EMPL_RCD = A_ES.EMPL_RCD

    AND A.EFFDT = A_ES.EFFDT))

    AND (G.EFFDT =

    (SELECT MAX(G_ED.EFFDT)

    FROM PS_NAMES G_ED

    WHERE G.EMPLID = G_ED.EMPLID

    AND G_ED.EFFDT <= SUBSTRING(CONVERT(CHAR, GETDATE()), 1, 11)))

    AND (G.NAME_TYPE = 'PRI')

    AND (H.EFFDT =

    (SELECT MAX(H_ED.EFFDT)

    FROM PS_ADDRESSES H_ED

    WHERE H.EMPLID = H_ED.EMPLID

    AND H_ED.EFFDT <= SUBSTRING(CONVERT(CHAR, GETDATE()), 1, 11)))

    AND (H.ADDRESS_TYPE = 'HOME')

    AND (I.PHONE_TYPE = 'HOME' or I.PHONE_TYPE IS NULL or I.PHONE_TYPE = 'CELL')

    AND (A.COMPANY = '007')

    AND A.PAYGROUP = '701'

    AND A.EMPLID NOT IN ('04171','04172','04173','04175','04272','04299','04309','04395','04423','04445','04510')

    ORDER BY A.EMPLID

  • COALESCE would help you in this case...it's really just a CASE WHEN construct:

    SELECT COALESCE(HOMEPHONE,CELLPHONE,EMERGENCYPHONE,'911')

    FROM SOMETABLE.

    it returns the first non-null item...so if homephone is null, it returns the cellphone, but if that is null, it returns the next, etc.

    you could improve your case statement a bit if you did something like this:

    CASE

    WHEN CASE A.GRADE IN('01','02','03','04','05','06','07','08','09','10','11','12','13','14','15')

    THEN 'Fabco Salaried Grade 1-15'

    WHEN CASE A.GRADE IN('16','17','18','19')

    THEN 'Fabco Salaried Grade 16-19'

    WHEN CASE A.GRADE IN('17','18','19','20','21','22','23','24','25')

    THEN 'Fabco Salaried Grade 20+'

    ELSE '**** BAD ACCRUAL PROFILE ****' END AS ACCRUAL_PROFILE,

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • I don't think COALESCE will work in this case.

    Try

    SELECTA.EMPLID AS PERSON_NBR,

    '005/116/' + LEFT(A.DEPTID, 5) + '/' + A.JOBCODE + '/NA/NA/NA' AS LABORACCOUNT,

    0 AS HOURLY_RT,

    '' AS SUPERVISOR_NAME,

    A.EMPL_STATUS AS EMPL_STATUS,

    D.SERVICE_DT AS HIRE_DT,

    '' AS AC_DEVICEGRP,

    CASE

    WHEN A.GRADE BETWEEN '01' AND '10' THEN 'FABCO Salaried Non Exempt'

    WHEN A.GRADE BETWEEN '11' AND '12' THEN 'FABCO Salaried Exempt Eligible'

    WHEN A.GRADE BETWEEN '13' AND '25' THEN 'FABCO Salaried Exempt'

    ELSE '**** BAD PAY RULE ****'

    END AS PAYRULE,

    0 AS AC_BADGENUM,

    F.BIRTHDATE AS BIRTHDATE,

    G.FIRST_NAME AS FIRST_NAME,

    G.LAST_NAME AS LAST_NAME,

    H.ADDRESS_TYPE AS ADDRESS_TYPE,

    H.ADDRESS1 AS ADDRESS1,

    H.CITY AS CITY,

    H.STATE AS STATE,

    H.POSTAL AS POSTAL,

    H.COUNTRY AS COUNTRY,

    I.PHONE_TYPE AS PHONE_TYPE,

    I.PHONE AS PHONE,

    A.JOBCODE AS JOBCODE,

    A.EFFDT AS EFFDT,

    A.SHIFT AS SHIFT,

    D.SERVICE_DT AS SERVICE_DT,

    A.ACC_CLOCK_ID AS ACC_CLOCK_ID,

    A.EMPLID,

    CASE

    WHEN A.GRADE BETWEEN '01' AND '15' THEN 'Fabco Salaried Grade 1-15'

    WHEN A.GRADE BETWEEN '16' AND '19' THEN 'Fabco Salaried Grade 16-19'

    WHEN A.GRADE BETWEEN '20' AND '25' THEN 'Fabco Salaried Grade 20+'

    ELSE '**** BAD ACCRUAL PROFILE ****'

    END AS ACCRUAL_PROFILE,

    '13404' AS TIMEZONE,

    A.PAYGROUP AS PAYGROUP,

    J.ACNETLOGON

    FROMdbo.PS_JOB AS A

    INNER JOINdbo.PS_PERSON_NAME B ON B.EMPLID = A.EMPLID

    INNER JOINdbo.PS_EMPLOYMENT AS D ON D.EMPLID = A.EMPLID

    LEFT JOINdbo.PS_AC_KRONOS_INT AS E ON E.EMPLID = A.EMPLID

    INNER JOINdbo.PS_PERSON AS F ON F.EMPLID = A.EMPLID

    INNER JOINdbo.PS_NAMES AS G ON G.EMPLID = A.EMPLID

    INNER JOINdbo.PS_ADDRESSES AS H ON H.EMPLID = A.EMPLID

    LEFT JOINdbo.PS_PERSONAL_PHONE AS I ON I.EMPLID = A.EMPLID

    AND I.PHONE_TYPE = (SELECT TOP 1 x.PHONE_TYPE FROM dbo.PS_PERSONAL_PHONE AS x WHERE x.EMPLID = A.EMPLID ORDER BY CASE x.PHONE_TYPE WHEN 'HOME' THEN 0 WHEN 'CELL' THEN 1 END)

    INNER JOINdbo.PS_ACNETWORK_LOGON AS J ON J.EMPLID = A.EMPLID

    WHEREA.EFFDT = (SELECT MAX(A_ED.EFFDT) FROM PS_JOB AS A_ED WHERE A_ED.EMPLID = A.EMPLID AND A_ED.EMPL_RCD = A.EMPL_RCD AND A_ED.EFFDT <= CONVERT(CHAR(10), GETDATE(), 112))

    AND A.EFFSEQ = (SELECT MAX(A_ES.EFFSEQ) FROM PS_JOB AS A_ES WHERE A_ES.EMPLID = A.EMPLID AND A_ES.EMPL_RCD = A.EMPL_RCD AND A_ES.EFFDT = A.EFFDT)

    AND G.EFFDT = (SELECT MAX(G_ED.EFFDT) FROM PS_NAMES AS G_ED WHERE G_ED.EMPLID = G.EMPLID AND G_ED.EFFDT <= CONVERT(CHAR(10), GETDATE(), 112))

    AND G.NAME_TYPE = 'PRI'

    AND H.EFFDT = (SELECT MAX(H_ED.EFFDT) FROM PS_ADDRESSES AS H_ED WHERE H_ED.EMPLID = H.EMPLID AND H_ED.EFFDT <= CONVERT(CHAR(10), GETDATE(), 112))

    AND H.ADDRESS_TYPE = 'HOME'

    AND A.COMPANY = '007'

    AND A.PAYGROUP = '701'

    AND A.EMPLID NOT IN ('04171', '04172', '04173', '04175', '04272', '04299', '04309', '04395', '04423', '04445', '04510')

    ORDER BYA.EMPLID


    N 56°04'39.16"
    E 12°55'05.25"

  • Coalesce will only work if the phone number columns are nullable and have nulls when the value is not there - otherwise if a record did exist for e.g. home but there was no phone number for home it would still return the empty string ... one way around this is to use NullIf as well - so in the earlier hint on using Coalesce

    SELECT COALESCE(HOMEPHONE,CELLPHONE,EMERGENCYPHONE,'911')

    FROM SOMETABLE.

    becomes

    SELECT COALESCE(NULLIF(HOMEPHONE,''),NULLIF(CELLPHONE,''),NULLIF(EMERGENCYPHONE,''),'911')

    FROM SOMETABLE.

    That way if any of the fields are empty '' then they are turned to Null to fall through to next otpion in the Coalesce - I am not sure how this performs compared to a CASE statement though - probably simialrly as the work required by the engine to evaluate is pretty much the same

  • Yes, but the phone table has both TYPE and PHONE columns, which makes me think the table is normalized liek this

    TYPE PHONE

    HOME 555-5687

    CELL 444-45524

    And then coalesce will not work when the JOIN fetches two records.


    N 56°04'39.16"
    E 12°55'05.25"

  • Ah .. didn't notice that .. your subselect makes more sense then (though again it might be necessary to exclude rows in subselect where the actual phone field was not filled but that depends on how the app populates this table (i.e. does it ever add a row for HOME number with empty contents for the actual number)

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply