CODEWITHSUNDEEP
sqlms-accessms-access-2013not-exists
Jamie Marshall
Jamie Marshall

Reputation: 2304

What is wrong with this access query?

Running the below query returns 0 records, but I would expect it to return 3.

SELECT
    ID,
    DW2_TV_DimStation_Id,
    DW2_OTT_DimStation_Id,
    Name,
    CoreTVCode,
    CoreOTTCode,
    StrataTVCode,
    HouseHolds,
    MaleSkew,
    FemaleSkew,
    AverageAge,
    AverageIncome,
    BroadReach,
    Description,
    Owner,
    Notes,
    timestamp,
    CreatedOn,
    ModifiedOn,
    Retired,
    1 AS Accepted
FROM
    Planning_DimStation AS src
WHERE
    src.[timestamp] = (
        SELECT
            MAX([timestamp])
        FROM
            Planning_DimStation AS src2
        WHERE
            src2.[ID] = src.[ID]
    )
    AND NOT EXISTS (
        SELECT
            1 
        FROM
            DimStation AS tgt
        WHERE
            tgt.[ID] = src.[ID]
    );

The part that breaks it is the NOT EXISTS statement. If I delete the NOT EXISTS it works fine.

Could this be a silent fail caused by type missmatch?

Table 1:

    CREATE TABLE [Planning].[DimStation]
    (
        [ID] INT PRIMARY KEY,
        [DW2_TV_DimStation_Id] INT NULL,
        [DW2_OTT_DimStation_Id] INT NULL,
        [Name] NVARCHAR(128) NOT NULL,
        [CoreTVCode] CHAR(5) NULL,
        [CoreOTTCode] CHAR(10) NULL,
        [StrataTVCode] CHAR(10) NULL,
        [HouseHolds] DECIMAL(5,2) NULL,
        [MaleSkew] DECIMAL(5,2) NULL,
        [FemaleSkew] DECIMAL(5,2) NULL,
        [AverageAge] INT NULL,
        [AverageIncome] DECIMAL(23,2) NULL,
        [BroadReach] BIT NULL,
        [Description] NVARCHAR(MAX) NULL,
        [Owner] NVARCHAR(128) NULL,
        [Notes] NVARCHAR(MAX) NULL,
        [timestamp] timestamp NOT NULL,
        [CreatedOn] DATETIME2(7) CONSTRAINT [df_Planning_DimStation_CreatedOn] DEFAULT (sysutcdatetime()) NOT NULL,
        [ModifiedOn] DATETIME2(7) CONSTRAINT [df_Planning_DimStation_ModifiedOn] DEFAULT (sysutcdatetime()) NOT NULL,
        [Retired] BIT CONSTRAINT [df_Planning_DimStation_Retired] DEFAULT (0) NOT NULL
    )
    GO

Table 2:

enter image description here

Upvotes: 0

Views: 55

Answers (1)

Erik A
Erik A

Reputation: 32642

Joining on different data types tends to yield unexpected results. To fix this, use casts.

A note is that Access doesn't allow nulls to be cast. So we need to work around that using Nz (same as ISNULL in T-SQL) and explicitly handling nulls.

AND NOT EXISTS (
    SELECT
        1 
    FROM
        DimStation AS tgt
    WHERE
        CLng(IIF(tgt.[ID] IS NULL, 0, tgt.ID)) = src.[ID] AND NOT tgt.ID IS NULL
);

Upvotes: 1

Related Questions