SQL String to get data from DB2

Question

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#

Answer 1

I can't see much wrong with it. Maybe use BTRIM if on Db2 11.1 or above rather than RTRIM(LTRIM( and maybe indent with fewer spaces (e.g. align the first column with the rest), but that is really a matter of style

• Zack E: unfortunately, i cant use BTRIM; I tried. Also thats how the editor I use formats the string and im not a fan of it. Thanks for taking a look at it.