从DB2获取数据的SQL字符串

Question

我决定将SQL部分从这个问题中分离出来。在我们开始之前，我需要告诉您一个关键的信息:由于离站数据库中的权限，我不允许在数据库中创建表，即使是临时的表，我也不能从数据库中获取数据。

话虽如此:下面的所有代码都按照预期工作，但是我想回顾一下它，因为我知道必须有一种更干净的方法来编写SQL字符串。

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

我看不出有什么问题。也许在Db2 11.1或更高版本上使用Db2 if，而不是RTRIM(LTRIM(，或者缩进更少的空格(例如，将第一列与其他列对齐)，但这实际上是一个风格问题。