I decided to split the SQL Part away from This Question.
Before we get started I need to let you know a critical piece of information: Due to permissions within an offsite database I am NOT allowed to create tables even temporary ones within the database that I am getting the data from.
With that being said: All of the code below works as expected, but I would like a review of it because I know that there has to be a cleaner way of writing the SQL String.
SELECT CONCAT(cfcif#,cfalta) AS Customer_Number,
cffna AS First_Name,
cfmna AS Middle_Name,
COALESCE(NULLIF(cflna,''),cfna1) AS Last_Name,
COALESCE(NULLIF(RTRIM(LTRIM(cfpfa1))|| ' '|| RTRIM(LTRIM(cfpfa2)),''),RTRIM(LTRIM(cfna2),'')|| ' ' || RTRIM(LTRIM(cfna3),'')) AS Street_Address,
COALESCE(NULLIF(cfpfcy,''),cfcity) AS Street_City,
COALESCE(NULLIF(cfpfst,''),cfstat) AS Street_State,
COALESCE(NULLIF(LEFT(cfpfzc, 5), 0), LEFT(cfzip, 5)) AS Street_Zip,
CONCAT(RTRIM(LTRIM(cfna2)),RTRIM(LTRIM(cfna3))) AS Mailing_Address,
cfcity AS Mailing_City,
cfstat AS Mailing_State,
LEFT(cfzip, 5) AS Mailing_Zip,
NULLIF(cfhpho,0) AS Home_Phone,
NULLIF(cfbpho,0) AS Business_Phone,
NULLIF(cfssno,0) AS TIN,
(CASE
WHEN cfindi = 'Y' THEN '1'
WHEN cfindi = 'N' THEN '2'
END) AS Customer_Type,
(CASE
WHEN cfdob7 = 0 THEN NULL
WHEN cfdob7 = 1800001 THEN NULL
ELSE cfdob7
END) AS Date_of_Birth,
cfeml1 AS Email_Address
FROM bhschlp8.jhadat842.cfmast cfmast
WHERE cfdead = 'N'
ORDER BY cfcif#
