March 23, 2011 at 8:21 am
HI,
I subsribe to several listservs and had this query come across. I would like to convert it to use in sql server 2008 R2.
I can get through everything but the decode. Would someone translate it for me?
SELECT snq.emplid
, snq.last_name
, snq.first_name
, SUBSTR(snq.middle_name
,0
,1) AS MIDDLE_INITIAL
, TO_CHAR(p.birthdate
,'mm-dd-yy') AS BIRTHDATE
-- , addrivw.address_type
, addrivw.address1
, addrivw.address2
, addrivw.city
, addrivw.state
, addrivw.postal
, addrivw.country
FROM ps_NAMES SNQ
--, ps_addresses a
, PS_PERSON p,(SELECT EMPLID
, ADDRESS_TYPE
, EFFDT
, EFF_STATUS
, ADDRESS1
, ADDRESS2
, ADDRESS3
, ADDRESS4
, CITY
, STATE
, POSTAL
, COUNTRY
FROM PS_ADDRESSES
where eff_status = 'A' ) ADDRIVW
WHERE SNQ.emplid = addrivw.emplid
AND SNQ.emplid = P.emplid
AND SNQ.name_type = 'PRI'
AND SNQ.eff_status = 'A'
AND SNQ.effdt = (
SELECT MAX(SNQ_ED.EFFDT)
FROM PS_NAMES SNQ_ED
WHERE SNQ.EMPLID = SNQ_ED.EMPLID
AND SNQ.NAME_TYPE = SNQ_ED.NAME_TYPE
AND SNQ.EFFDT <= SYSDATE )
/* commented >> AND INSTR('MAILHOMEPERMLOCOTH', a.ADDRESS_TYPE) = (
SELECT MIN(INSTR('MAILHOMEPERMLOCOTH'
, a_ed.ADDRESS_TYPE))
FROM ps_addresses a_ed
WHERE a.emplid = a_ed.emplid
AND a.effdt = (
SELECT MAX(a2.effdt)
FROM ps_addresses a2
WHERE a.emplid = a2.emplid
AND a2.effdt <= sysdate ) ) << commented */
AND ADDRIVW.ADDRESS_TYPE = (
SELECT MIN(ADDR1.ADDRESS_TYPE) KEEP(DENSE_RANK FIRST
ORDER BY DECODE(ADDR1.ADDRESS_TYPE
, 'MAIL'
, 1
, 'HOME'
, 2
, 'PERM'
, 3
, 'LOC'
, 4
, 'OTH'
, 5))
FROM PS_ADDRESSES ADDR1
WHERE ADDR1.EMPLID = ADDRIVW.EMPLID)
AND ADDRIVW.EFFDT = (
SELECT MAX(ADDR2.EFFDT)
FROM PS_ADDRESSES ADDR2
WHERE ADDR2.EMPLID = ADDRIVW.EMPLID
AND ADDR2.ADDRESS_TYPE = ADDRIVW.ADDRESS_TYPE)
ORDER BY 1
thanks in advance.
March 23, 2011 at 9:36 am
Ok, I kept noodling and came up with this. Just in case anyone ever needs to do this................
SELECT SNQ.EMPLID
, SNQ.LAST_NAME
, SNQ.FIRST_NAME
, SUBSTRING(SNQ.MIDDLE_NAME,1,20) AS MIDDLE_INITIAL
, CONVERT(CHAR,P.BIRTHDATE,101) AS BIRTHDATE
-- , ADDRIVW.ADDRESS_TYPE
, ADDRIVW.ADDRESS1
, ADDRIVW.ADDRESS2
, ADDRIVW.CITY
, ADDRIVW.STATE
, ADDRIVW.POSTAL
, ADDRIVW.COUNTRY
FROM PS_NAMES SNQ
--, PS_ADDRESSES A
, PS_PERSON P,(SELECT EMPLID
, ADDRESS_TYPE
, EFFDT
, EFF_STATUS
, ADDRESS1
, ADDRESS2
, ADDRESS3
, ADDRESS4
, CITY
, STATE
, POSTAL
, COUNTRY
FROM PS_ADDRESSES
WHERE EFF_STATUS = 'A' ) ADDRIVW
WHERE SNQ.EMPLID = ADDRIVW.EMPLID
AND SNQ.EMPLID = P.EMPLID
AND SNQ.NAME_TYPE = 'PRI'
AND SNQ.EFF_STATUS = 'A'
AND SNQ.EFFDT = (
SELECT MAX(SNQ_ED.EFFDT)
FROM PS_NAMES SNQ_ED
WHERE SNQ.EMPLID = SNQ_ED.EMPLID
AND SNQ.NAME_TYPE = SNQ_ED.NAME_TYPE
AND SNQ.EFFDT <= GETDATE() )
AND EXISTS
( SELECT MIN(ADDR1.ADDRESS_TYPE), DENSE_RANK() OVER(
ORDER BY CASE ADDR1.ADDRESS_TYPE
WHEN 'MAIL' THEN 1
WHEN 'HOME' THEN 2
WHEN 'PERM' THEN 3
WHEN 'LOC' THEN 4
WHEN 'OTH' THEN 5
END )
FROM PS_ADDRESSES ADDR1
WHERE ADDR1.EMPLID = ADDRIVW.EMPLID
AND ADDRIVW.EFFDT = (
SELECT MAX(ADDR2.EFFDT)
FROM PS_ADDRESSES ADDR2
WHERE ADDR2.EMPLID = ADDRIVW.EMPLID
AND ADDR2.ADDRESS_TYPE = ADDRIVW.ADDRESS_TYPE)
GROUP BY ADDRESS_TYPE)
ORDER BY 1
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply