Access数据库IIf语法转换为SQL CASE WHEN语法的技术求助

Access SpreadType 字段逻辑转标准SQL实现 #

原Access的多层IIF嵌套逻辑已完全对齐转换为标准CASE WHEN语法，适配绝大多数关系型数据库：

SELECT 
    CASE
        -- 第一层：QRLF类判断
        WHEN mv_Loan_Ext1.LoanPurposeTypeCode IN ('008', '080') 
            THEN CONCAT('QRLF-', CASE WHEN FAReceivesPatronage = 0 THEN 'NPAT' ELSE 'PAT' END)
        -- 第二层：非QRLF判断
        WHEN SecDistrictIndustryCode = '999' THEN 'Non-QRLF'
        -- 第三层：RHL判断
        WHEN PriDistrictIndustryCode = '999' THEN 'RHL'
        -- 第四层：其他住房类判断，原Access中+0转数值的逻辑用CAST实现
        WHEN CAST(mv_Loan_Ext1.LoanPurposeTypeCode AS UNSIGNED) BETWEEN 1 AND 7 THEN 'Other home'
        -- 第五层：参与购买票据判断
        WHEN FANoteIsParticipationPurchased = 1 
            THEN CASE WHEN mv_FANote.BranchNbr = '092' THEN '100% GP' ELSE 'PP' END
        -- 第六层：FCE判断
        WHEN PatronagePoolCode = '14' THEN 'FCE'
        -- 第七层：YBS AgStart判断
        WHEN YBSAgStart <> 'No' 
            OR (
                CASE WHEN COALESCE(TRIM(AgGrow_NoOperator), '') IN ('', 'No') THEN 0 ELSE 1 END
                + CASE WHEN COALESCE(TRIM(AgNiche), '') IN ('', 'No') THEN 0 ELSE 1 END
                + CASE WHEN COALESCE(TRIM(AgGrowOperator), '') IN ('', 'No') THEN 0 ELSE 1 END
            ) > 0 THEN 'YBS AgStart'
        -- 第八层：粮食库存贷款判断
        WHEN GrainInventoryLoan = 'Yes' THEN 'Grain Inventory'
        -- 第九层：PPP贷款判断
        WHEN COVID19 LIKE 'SBA%' THEN 'PPP'
        -- 第十层：农民类型判断
        ELSE 
            CASE 
                WHEN COALESCE(
                    CASE 
                        WHEN FarmTypeAbbr = '' 
                            THEN CASE WHEN FCALoanTypeAbbr IN ('PRIT', 'REMG') THEN 'FL' ELSE '' END
                        ELSE FarmTypeAbbr 
                    END, ''
                ) <> 'FL' THEN 'General non-farmer'
                ELSE 'Core farmer loans'
            END
    END AS SpreadType
-- 此处补充表关联逻辑即可
FROM 业务主表
LEFT JOIN mv_Loan_Ext1 ON 主表与mv_Loan_Ext1的关联条件
LEFT JOIN mv_FANote ON 主表与mv_FANote的关联条件

方言适配提示 #

• 若使用PostgreSQL数据库，只需将CONCAT函数替换为||拼接符，CAST(xx AS UNSIGNED)替换为CAST(xx AS INTEGER)即可正常运行
• 所有字段引用已将Access特有的!连接符替换为标准SQL的表名.字段名格式，使用前需补全实际关联规则

内容的提问来源于stack exchange，提问作者Barb