CODEWITHSUNDEEP
sql-serversubstitution
Carlos
Carlos

Reputation: 47

Substitute a LEFT OUTER JOIN in SQL Server (SSMS)

When I try to create a Unique index in the VIEW below, I am getting the following error,

Msg 10113, Level 16, State 1, Line 431
Cannot create index on view "V3CONv.ADVANCED.BillsToBeDeletedVIEW" because it uses a LEFT, RIGHT, or FULL OUTER join, and no OUTER joins are allowed in indexed views. Consider using an INNER join instead.

INNER JOIN will a produce a data set different from the required, how can I substitute the LEFT OUTER JOIN in Microsoft SQL server ??

CREATE VIEW ADVANCED.BillsToBeDeletedVIEW
WITH SCHEMABINDING
AS
    SELECT DISTINCT 
        I_BILLNUMBER 
    FROM 
        ADVANCED.BIF951_C 
    LEFT OUTER JOIN 
        ADVANCED.BIF003ToBeDeleted ON BIF003ToBeDeleted.C_CUSTOMER = ADVANCED.BIF951_C.C_CUSTOMER 
                                   AND BIF003ToBeDeleted.C_ACCOUNT = ADVANCED.BIF951_C.C_ACCOUNT
    WHERE 
        BIF003ToBeDeleted.C_CUSTOMER IS NOT NULL
        OR D_BILLDATE < '2016-06-01'

Upvotes: 1

Views: 506

Answers (1)

David Browne - Microsoft
David Browne - Microsoft

Reputation: 89266

You can use two separate indexed views, like this:

create schema ADVANCED
go

    create table ADVANCED.BIF951_C(id int, I_BILLNUMBER int, D_BILLDATE datetime, C_CUSTOMER INT, C_ACCOUNT INT)

    CREATE TABLE ADVANCED.BIF003ToBeDeleted(ID INT, C_CUSTOMER INT, C_ACCOUNT INT)

    GO

    CREATE OR ALTER VIEW ADVANCED.BillsToBeDeletedVIEW_1
    WITH SCHEMABINDING
    AS
        SELECT  
            I_BILLNUMBER 
        FROM 
            ADVANCED.BIF951_C 
        JOIN 
            ADVANCED.BIF003ToBeDeleted ON BIF003ToBeDeleted.C_CUSTOMER = ADVANCED.BIF951_C.C_CUSTOMER 
                                       AND BIF003ToBeDeleted.C_ACCOUNT = ADVANCED.BIF951_C.C_ACCOUNT
     GO

     CREATE UNIQUE CLUSTERED INDEX IX_BillsToBeDeletedVIEW_1 ON ADVANCED.BillsToBeDeletedVIEW_1(I_BILLNUMBER)
     GO


    CREATE OR ALTER VIEW ADVANCED.BillsToBeDeletedVIEW_2
    WITH SCHEMABINDING
    AS


        SELECT I_BILLNUMBER
        FROM  ADVANCED.BIF951_C 
        WHERE D_BILLDATE < CONVERT(DATETIME,'2016-06-01',120)

        GO


     CREATE UNIQUE CLUSTERED INDEX IX_BillsToBeDeletedVIEW_2 ON ADVANCED.BillsToBeDeletedVIEW_2(I_BILLNUMBER)
     GO

     CREATE OR ALTER VIEW ADVANCED.BillsToBeDeletedVIEW
     AS 

       SELECT I_BILLNUMBER
       FROM BillsToBeDeletedVIEW_1
       UNION
       SELECT I_BILLNUMBER
       FROM BillsToBeDeletedVIEW_2

Upvotes: 1

Related Questions