I decided to split the SQL Part away fromThis 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#

1 Answer1

Start asking to get answers

Find the answer to your question by asking.

Explore related questions

See similar questions with these tags.