修改SQL视图/添加不带联接的列

Question

正在尝试修改现有视图。它当前在我的数据库上返回32行。代码如下：

ALTER VIEW [dbo].[vw_fnd_la_Statement]
AS

SELECT     
    SH.lease_id, S.expense_id, SH.statement_header_id, S.statement_id AS row_id,
    EC.name AS expense_category, SH.period, S.invoice_number, 
    S.received_date, S.due_date, S.landlord_expense_total, S.prorata_share,   
    S.tenant_expense_total, S.tenant_admin_fee, S.paid_estimates, S.net_due, 
    CASE 
        WHEN s.status = 'O' THEN 'Open' 
        WHEN s.status = 'P' THEN 'In Progress' 
        WHEN s.status = 'D' THEN 'In Dispute' 
        WHEN s.status = 'C' THEN 'Closed' 
        ELSE NULL 
    END AS status, S.approval, 
    dbo.UDF_getExpenseVendor(S.expense_id) AS vendor, S.approved_amount, 
    S.approved_payment_amount, S.disputed_amount, S.held_amount, S.statement_id, 
    EC.expense_category_id, SH.start_date, SH.end_date, S.status AS status_id
FROM      
    la_tbl_statement_header AS SH 
INNER JOIN
    la_tbl_statement AS S ON SH.statement_header_id = S.statement_header_id 
INNER JOIN
    la_tlu_expense_category AS EC ON S.expense_category_id = EC.expense_category_id
WHERE     
    (SH.IsDeleted = 0) AND (S.IsDeleted = 0) AND (EC.IsDeleted = 0)

我有一个名为la_tbl_expense_cap_setup的第四个表，它有一个生效日期列和一个与la_tbl_statement表中的费用ID匹配的费用ID。我想要的是现有视图的所有结果，外加一个包含生效日期的新列。某些费用没有上限，因此在生效日期列中该行应为NULL。我尝试在费用I上连接，但得到的是19行的子集。

Answer 1

使用左外部连接将为您提供表的所有结果，即使它不在第4个表中

Answer 2

在第四个表上尝试使用左外部联接。

    ALTER VIEW [dbo].[vw_fnd_la_Statement]
    AS

    SELECT     SH.lease_id, S.expense_id, SH.statement_header_id, S.statement_id AS row_id,          EC.name AS expense_category, SH.period, S.invoice_number, 
          S.received_date, S.due_date, S.landlord_expense_total, S.prorata_share,   S.tenant_expense_total, S.tenant_admin_fee, S.paid_estimates, S.net_due, 
          CASE WHEN s.status = 'O' THEN 'Open' WHEN s.status = 'P' THEN 'In Progress' WHEN s.status = 'D' THEN 'In Dispute' WHEN s.status = 'C' THEN 'Closed' ELSE
           NULL END AS status, S.approval, dbo.UDF_getExpenseVendor(S.expense_id) AS vendor, S.approved_amount, S.approved_payment_amount, 
          S.disputed_amount, S.held_amount, S.statement_id, EC.expense_category_id, SH.start_date, SH.end_date, S.status AS status_id, esc.effective_date
    FROM      
            la_tbl_statement_header AS SH INNER JOIN
          la_tbl_statement AS S ON SH.statement_header_id = S.statement_header_id INNER   JOIN
          la_tlu_expense_category AS EC ON S.expense_category_id = EC.expense_category_id
          left outer join la_tbl_expense_cap_setup as ecson esc.expense_id   = s.expense_id
    WHERE     (SH.IsDeleted = 0) AND (S.IsDeleted = 0) AND (EC.IsDeleted = 0)

Answer 3

OK左外部连接基本上是正确的。我只需要将'AND (ECS.IsDeleted = 0)‘添加到新的连接中，以使其正常工作。我最初在WHERE子句中有这个和，它只返回具有生效日期的行。下面是正确的代码。谢谢你的帮助。

SELECT     SH.lease_id, S.expense_id, SH.statement_header_id, S.statement_id AS row_id, EC.name AS expense_category, SH.period, S.invoice_number, 
      S.received_date, S.due_date, S.landlord_expense_total, S.prorata_share, S.tenant_expense_total, S.tenant_admin_fee, S.paid_estimates, S.net_due, 
      CASE WHEN s.status = 'O' THEN 'Open' WHEN s.status = 'P' THEN 'In Progress' WHEN s.status = 'D' THEN 'In Dispute' WHEN s.status = 'C' THEN 'Closed' ELSE
       NULL END AS status, S.approval, dbo.UDF_getExpenseVendor(S.expense_id) AS vendor, S.approved_amount, S.approved_payment_amount, 
      S.disputed_amount, S.held_amount, S.statement_id, EC.expense_category_id, SH.start_date, SH.end_date, S.status AS status_id,
      ECS.effective_date AS cap_eff_date
FROM      
        la_tbl_statement_header AS SH INNER JOIN
      la_tbl_statement AS S ON SH.statement_header_id = S.statement_header_id INNER JOIN
      la_tlu_expense_category AS EC ON S.expense_category_id = EC.expense_category_id left outer join
      la_tbl_expense_cap_setup AS ECS ON s.expense_id = ECS.expense_id AND (ECS.IsDeleted = 0)
WHERE     (SH.IsDeleted = 0) AND (S.IsDeleted = 0) AND (EC.IsDeleted = 0)