locked
How to select NULL values in a view RRS feed

  • Question

  • I am creating a view and want to select records where the value of a Customer field (Klant Test Plan) is NULL or has exact the same value as for example customer field 2 (Klant Schedule).

    I have already the code below:

    SELECT DISTINCT
                          TOP (100) PERCENT dbo.Product.ERPKey, dbo.TestPlan.CoA, dbo.TestMethod.WorkInstruction, dbo.Customer.Name AS [Klant Testplan],
                          Customer_1.Name AS [Klant Schedule], LEFT(dbo.ShopFloor.ShopFloorNumber, 7) AS Schedule,
                          CASE WHEN Customer_1.Name = 'ItsMe BV' THEN Customer.Name ELSE Customer_1.Name END AS Customer
    FROM         dbo.Customer AS Customer_1 INNER JOIN
                          dbo.TestPlan INNER JOIN
                          dbo.TestPlanHeader ON dbo.TestPlan.TestPlanHeaderID = dbo.TestPlanHeader.TestPlanHeaderID INNER JOIN
                          dbo.TestMethod ON dbo.TestPlan.TestMethodID = dbo.TestMethod.TestMethodID INNER JOIN
                          dbo.ShopFloor INNER JOIN
                          dbo.Product ON dbo.ShopFloor.ProductID = dbo.Product.ProductID ON dbo.TestPlanHeader.ProductID = dbo.Product.ProductID ON
                          Customer_1.CustomerID = dbo.ShopFloor.CustomerID LEFT OUTER JOIN
                          dbo.Customer ON dbo.TestPlanHeader.CustomerShipToID = dbo.Customer.CustomerID
    WHERE     (dbo.TestPlan.CoA = 1) AND (LEFT(dbo.ShopFloor.ShopFloorNumber, 7) LIKE '1I44667%') AND
                          (CASE WHEN Customer_1.Name = 'ItsMe BV' THEN Customer.Name ELSE Customer_1.Name END = dbo.Customer.Name)
    ORDER BY Schedule, dbo.Product.ERPKey

    --------

    Thanks for answering, best regards,

    Peter

    Tuesday, September 15, 2015 2:14 PM

Answers

  • SELECT  P.ERPKey ,
            TP.CoA ,
            TM.WorkInstruction ,
            C.Name AS [Klant Testplan] ,
            C1.Name AS [Klant Schedule] ,
            LEFT(SF.ShopFloorNumber, 7) AS Schedule ,
            CASE WHEN C1.Name = 'ItsMe BV' THEN Customer.Name
                 ELSE C1.Name
            END AS Customer
    FROM    dbo.Customer C1
            INNER JOIN dbo.ShopFloor SF ON C1.CustomerID = SF.CustomerID
            INNER JOIN dbo.Product P ON SF.ProductID = P.ProductID
            INNER JOIN dbo.TestPlanHeader TPH ON TPH.ProductID = P.ProductID
            INNER JOIN dbo.TestPlan TP ON TP.TestPlanHeaderID = TPH.TestPlanHeaderID
            INNER JOIN dbo.TestMethod TM ON TP.TestMethodID = TM.TestMethodID
            LEFT JOIN dbo.Customer C ON TPH.CustomerShipToID = C.CustomerID
    WHERE   TP.CoA = 1
            AND SF.ShopFloorNumber LIKE '1I44667%'
            AND CASE WHEN C1.Name = 'ItsMe BV' THEN C.Name
                     ELSE C1.Name
                END = C.Name
            AND ( C.Name IS NULL OR C.Name = C1.Name
                );

    As ORDER BY is not allowed in a view, drop also the TOP clause.

    Use table name aliases.

    The LEFT() in LEFT(ShopFloorNumber, 7) LIKE '1I44667%' is redundant and it prohibits the use of an index.

    Sort your tables in a readable fashion.

    Due to the naming convention, I would be surprised when DISTINCT is really necessary.

    Your search condition should be C.Name IS NULL OR C.Name = C1.Name.

    E.g.

    Tuesday, September 15, 2015 2:27 PM
    Answerer
  • 1) yup. imho.

    2) Have you tried DISTINCT? It should not do that. When you want only one row based on ERPKey, COA, WorkInstruction, [Klant Schedule] what values do you want to show for the other three rows?

    You can use GROUP BY and MIN() on those rows. Or you can use ROW_NUMBER() to get the first row with a specific information. But you need a criteria to do it so. E.g.

    DECLARE @Sample TABLE
        (
          ERPKey INT ,
          CoA INT ,
          WorkInstruction NVARCHAR(255) ,
          KlantSchedule NVARCHAR(255) ,
          KlantTestplan NVARCHAR(255) ,
          Schedule NVARCHAR(255) ,
          Customer NVARCHAR(255)
        );
    
    INSERT  INTO @Sample
            ( ERPKey, CoA, WorkInstruction, KlantSchedule, KlantTestplan, Schedule, Customer )
    VALUES  ( 1, 1, N'1', N'', N'1', N'1', N'1' ),
            ( 1, 1, N'2', N'', N'1', N'1', N'1' ),
            ( 1, 1, N'3', N'', N'1', N'1', N'1' ),
            ( 2, 1, N'4', N'', N'1', N'1', N'1' ),
            ( 2, 1, N'4', N'1', N'1', N'1', N'1' ),
            ( 2, 1, N'5', N'1', N'1', N'1', N'1' ),
            ( 2, 1, N'4', N'', N'1', N'1', N'1' ),
            ( 2, 1, N'4', N'1', N'2', N'2', N'2' );
    
    WITH    Ordered
              AS ( SELECT   * ,
                            ROW_NUMBER() OVER ( PARTITION BY ERPKey, CoA, WorkInstruction, KlantSchedule ORDER BY KlantTestplan, Schedule, Customer ) AS RN
                   FROM     @Sample S
                 )
        SELECT  *
        FROM    Ordered O
        WHERE   RN = 1;

    Wednesday, September 16, 2015 2:02 PM
    Answerer

All replies

  • SELECT  P.ERPKey ,
            TP.CoA ,
            TM.WorkInstruction ,
            C.Name AS [Klant Testplan] ,
            C1.Name AS [Klant Schedule] ,
            LEFT(SF.ShopFloorNumber, 7) AS Schedule ,
            CASE WHEN C1.Name = 'ItsMe BV' THEN Customer.Name
                 ELSE C1.Name
            END AS Customer
    FROM    dbo.Customer C1
            INNER JOIN dbo.ShopFloor SF ON C1.CustomerID = SF.CustomerID
            INNER JOIN dbo.Product P ON SF.ProductID = P.ProductID
            INNER JOIN dbo.TestPlanHeader TPH ON TPH.ProductID = P.ProductID
            INNER JOIN dbo.TestPlan TP ON TP.TestPlanHeaderID = TPH.TestPlanHeaderID
            INNER JOIN dbo.TestMethod TM ON TP.TestMethodID = TM.TestMethodID
            LEFT JOIN dbo.Customer C ON TPH.CustomerShipToID = C.CustomerID
    WHERE   TP.CoA = 1
            AND SF.ShopFloorNumber LIKE '1I44667%'
            AND CASE WHEN C1.Name = 'ItsMe BV' THEN C.Name
                     ELSE C1.Name
                END = C.Name
            AND ( C.Name IS NULL OR C.Name = C1.Name
                );

    As ORDER BY is not allowed in a view, drop also the TOP clause.

    Use table name aliases.

    The LEFT() in LEFT(ShopFloorNumber, 7) LIKE '1I44667%' is redundant and it prohibits the use of an index.

    Sort your tables in a readable fashion.

    Due to the naming convention, I would be surprised when DISTINCT is really necessary.

    Your search condition should be C.Name IS NULL OR C.Name = C1.Name.

    E.g.

    Tuesday, September 15, 2015 2:27 PM
    Answerer
  • Stefan

    If we use TOP 100 percent then we are able to compile a view with ORDER BY clause, but you are absolutely correct to point out that  he must remove the ORDER BY clause from the view 


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Wednesday, September 16, 2015 6:57 AM
  • Well, I should have added: While it compiles and works as intented, it clutters the SQL Statement and reduces the readability.
    Wednesday, September 16, 2015 7:51 AM
    Answerer
  • Thanks all for answering.

    What's the reason to use table name aliases? Easier to read?

    Distinct: I want a unique value for ERPKey & COA & WorkInstruction & Klant Schedule

    So I this case I don't want to see record: 21287, 1, T09203 - Recept 2911154 Glans, Klant Testplan = Blank

    How can I fix this?

     

    Best regards,

    Peter

    Wednesday, September 16, 2015 1:41 PM
  • 1) yup. imho.

    2) Have you tried DISTINCT? It should not do that. When you want only one row based on ERPKey, COA, WorkInstruction, [Klant Schedule] what values do you want to show for the other three rows?

    You can use GROUP BY and MIN() on those rows. Or you can use ROW_NUMBER() to get the first row with a specific information. But you need a criteria to do it so. E.g.

    DECLARE @Sample TABLE
        (
          ERPKey INT ,
          CoA INT ,
          WorkInstruction NVARCHAR(255) ,
          KlantSchedule NVARCHAR(255) ,
          KlantTestplan NVARCHAR(255) ,
          Schedule NVARCHAR(255) ,
          Customer NVARCHAR(255)
        );
    
    INSERT  INTO @Sample
            ( ERPKey, CoA, WorkInstruction, KlantSchedule, KlantTestplan, Schedule, Customer )
    VALUES  ( 1, 1, N'1', N'', N'1', N'1', N'1' ),
            ( 1, 1, N'2', N'', N'1', N'1', N'1' ),
            ( 1, 1, N'3', N'', N'1', N'1', N'1' ),
            ( 2, 1, N'4', N'', N'1', N'1', N'1' ),
            ( 2, 1, N'4', N'1', N'1', N'1', N'1' ),
            ( 2, 1, N'5', N'1', N'1', N'1', N'1' ),
            ( 2, 1, N'4', N'', N'1', N'1', N'1' ),
            ( 2, 1, N'4', N'1', N'2', N'2', N'2' );
    
    WITH    Ordered
              AS ( SELECT   * ,
                            ROW_NUMBER() OVER ( PARTITION BY ERPKey, CoA, WorkInstruction, KlantSchedule ORDER BY KlantTestplan, Schedule, Customer ) AS RN
                   FROM     @Sample S
                 )
        SELECT  *
        FROM    Ordered O
        WHERE   RN = 1;

    Wednesday, September 16, 2015 2:02 PM
    Answerer
  • Stefan,

    Let me explain:

    If have tried to remove the duplicate by a DISTINCT

    When you look at me print then we see 2 records which are the same

    21287  1  T09203 - Recept 2911154 Glans  ----> Where Klant Testplan = blank

    21287  1  T09203 - Recept 2911154 Glans  ----> Where Klant Testplan = It's Me

    The first one is a general one which should be replace by the second one (Where Klant Testplan = It's Me), so I case the Klant Testplan has been filled for the same ERPKEY + COA + Workinstruction then this is the record I am looking for. The general record should be overruled by the customer (Klant Testplan) specific one.

    ERPKey, WorkInstruction, Klant Testplan and Customer should be showed.

    Wednesday, September 16, 2015 2:34 PM
  • Just fill concrete values into my sample..

    Wednesday, September 16, 2015 2:39 PM
    Answerer
  • Problem has been solved, thanks for helping.
    Thursday, September 17, 2015 11:00 AM