SQL报表获取多类别基础资料字段

以付款单获取收款单位字段为例

1. 先确认多类别基础资料列表关联到的基础资料对应的表

2. 编写SQL
   

SELECT fbillno,ap.FRECTUNITTYPE,

       -- 如果往来单位类型是供应商，则获取供应商名称等信息

       (CASE WHEN ap.FRECTUNITTYPE = 'BD_Supplier' THEN su.FNAME

         WHEN ap.FRECTUNITTYPE = 'BD_Customer' THEN cu.FNAME

WHEN ap.FRECTUNITTYPE = 'BD_Department' THEN de.FNAME

WHEN ap.FRECTUNITTYPE = 'BD_Empinfo' THEN emp.FNAME

WHEN ap.FRECTUNITTYPE = 'FIN_OTHERS' THEN othe.FNAME

  WHEN ap.FRECTUNITTYPE = 'ORG_Organizations' THEN org.FNAME

   WHEN ap.FRECTUNITTYPE = 'BD_BANK' THEN ban.FNAME

    END )AS SupplierName

       -- 如果往来单位类型是客户，则获取客户名称等信息

       --CASE WHEN ap.FRECTUNITTYPE = 'BD_Customer' THEN cu.FNAME END AS CustomerName

FROM T_AP_PAYBILL ap

--left join V_FIN_CONTACTTYPE cope on ap.FRECTUNITTYPE=cope.FITEMID

LEFT JOIN T_BD_SUPPLIER_L su ON ap.FRECTUNITTYPE = 'BD_Supplier' AND ap.FRECTUNIT = su.FSUPPLIERID AND su.flocalEID='2052' 

LEFT JOIN T_BD_DEPARTMENT_L de ON ap.FRECTUNITTYPE = 'BD_Department' AND ap.FRECTUNIT = de.FDEPTID AND de.flocalEID='2052' 

LEFT JOIN T_HR_EMPINFO_L emp ON ap.FRECTUNITTYPE = 'T_HR_EMPINFO' AND ap.FRECTUNIT = emp.FID AND emp.flocalEID='2052' 

LEFT JOIN T_FIN_OTHERS_L othe ON ap.FRECTUNITTYPE = 'FIN_OTHERS' AND ap.FRECTUNIT = othe.FID AND othe.flocalEID='2052' 

LEFT JOIN T_ORG_Organizations_L org ON ap.FRECTUNITTYPE = 'ORG_Organizations' AND ap.FRECTUNIT = org.FOrgID AND org.flocalEID='2052' 

LEFT JOIN T_BD_BANK_L ban ON ap.FRECTUNITTYPE = 'BD_BANK' AND ap.FRECTUNIT = ban.FBANKID AND ban.flocalEID='2052' 

LEFT JOIN T_BD_CUSTOMER_L cu ON ap.FRECTUNITTYPE = 'BD_Customer' AND ap.FRECTUNIT = cu.FCUSTID AND cu.flocalEID='2052';