Solved

SQL NOT EXISTS not working

Posted on 2014-09-09
5
178 Views
Last Modified: 2014-09-26
Hi,

I wish to insert some records into a table only if they do not exist in another.

However I don't think my 'NOT EXISTS' is working correctly in my code. I can see from the stats that 4 policies have gone on but when I run my select statement it is only finding 2 policies.

I have manually searched for the 'Policy References' in the table I wish to compare to and they don't exist so I don't know why my NOT EXISTS is not working, can someone shed some light for me please.

SELECT DISTINCT
        [c].[Name] AS 'Name' ,
        CASE WHEN [pbr].[Affinity] IN ( 'Autosaint' , 'Ladybird' )
             THEN dbo.[GetTitle]([Name])
             ELSE ''
        END AS 'Title' ,
        LEFT([c].[Forenames] ,
             ISNULL(NULLIF(CHARINDEX(' ' , REVERSE([c].[Forenames])) - 1 , -1) ,
                    LEN([c].[Forenames]))) AS 'First Name' ,
        CASE WHEN CHARINDEX(' ' , [c].[Forenames] , 1) > 1
             THEN SUBSTRING([c].[Forenames] ,
                            CHARINDEX(' ' , [c].[Forenames] , 1) + 1 , 20)
             ELSE ' '
        END AS 'Middle Names' ,
        RIGHT(c.Name ,
              ISNULL(NULLIF(CHARINDEX(' ' , REVERSE(c.Name)) - 1 , -1) ,
                     LEN(c.Name))) AS Surname ,
        [c].[Addr1] AS 'Address line 1' ,
        [c].[Addr2] AS 'Address line 2' ,
        [c].[Addr3] AS 'Address line 3' ,
        [c].[Addr4] AS 'Address line 4' ,
        [c].[Pcode] AS 'Postcode' ,
        pbr.[Affinity] AS 'Brand' ,
        pol.[Insco] AS 'Insurer' ,
        [regtbl].[Reg] AS 'Reg' ,
        [pol].[Polno] AS 'Insurer Ref' ,
        [pol].[B@] AS 'Fresh Branch' ,
        ISNULL([lepol].[PolRef@],'BRNZ001') AS 'Add On Polref' ,
        [pol].[PolRef@] AS 'Fresh Ref' ,
        [pol].[Rdat] AS 'Renewal Date' ,
        [pol].[Idat] AS 'Inception Date' ,
		CASE WHEN lepol.PolRef@ IS NULL THEN 'No' ELSE 'Yes' END AS 'LOS' ,
        'N' AS [Status]
FROM    [dbo].[ic_yyclient] AS c
        LEFT OUTER JOIN [dbo].[ic_brpolicy] AS pol ON [c].[B@] = [pol].[B@]
                                                      AND [c].[Ref@] = [pol].[Ref@]
        LEFT OUTER JOIN ( SELECT    *
                          FROM      [dbo].[ic_brpolicy] AS lepol
                          WHERE     [lepol].[Ptype] = 'LE' AND [lepol].[Term_date] >= dbo.[Today]()
                        ) AS lepol ON pol.[B@] = lepol.[B@]
                                      AND pol.[PolRef@] = lepol.[Ref@] + ''
                                      + lepol.[Notes1]
        LEFT OUTER JOIN [dbo].[ic_brcledger] AS l ON pol.[B@] = l.[B@]
                                                     AND pol.[PolRef@] = l.[PolRef@]
        LEFT OUTER JOIN [dbo].[T_FIG_New_PolicyBrandRegister] AS pbr ON pol.[B@] = pbr.[Branch]
                                                              AND pol.[PolRef@] = pbr.[PolicyRef]
        LEFT OUTER JOIN ( SELECT    [B@] ,
                                    [PolRef@] ,
                                    [Reg] AS [Reg]
                          FROM      [dbo].[ic_BD_CF] AS cf
                          UNION
                          SELECT    [B@] ,
                                    [PolRef@] ,
                                    [Reg_mark] AS [Reg]
                          FROM      [dbo].[ic_BD_TW1] AS tw1
                        ) AS regTbl ON pol.[B@] = [regTbl].[B@]
                                       AND pol.[PolRef@] = [regTbl].[PolRef@]
        LEFT OUTER JOIN [dbo].[ic_BD_PQ11] AS pq11 ON pol.[B@] = [pq11].[B@]
                                                      AND pol.[PolRef@] = [pq11].[PolRef@]
        LEFT OUTER JOIN [dbo].[ic_BD_PQ02] AS pq02 ON pol.[B@] = [pq02].[B@]
                                                      AND pol.[PolRef@] = [pq02].[PolRef@]
WHERE   pol.[Ptype] IN ( 'TW' , 'PC' , 'MT' )
        AND pol.[B@] <> 1
        AND pbr.[Affinity] IN ('Autosaint' , 'Ladybird' , 'Motor Trade' , 'Special Risks' )
        --AND (lepol.[PolRef@] IS NULL)
        AND pol.[Term_date] IS NULL
        AND pol.[Rdat] >= dbo.[Today]()
        --AND lepol.[Insco] = 'Winn'
        AND pol.[Insco] <> 'Hawkwell'
        --AND l.[Trantype] IN ( 'New Business' , 'Renewal' , 'Transfrd NB' )
        --AND l.[Ledger_dt] BETWEEN '20140802' AND dbo.[Today]()-1
        AND NOT EXISTS ( SELECT *
                         FROM   [FreshSystems].dbo.WINNS_Live_Policy_List AS tfwpl
                         WHERE  pol.[B@] = [tfwpl].[Fresh Branch]
                                AND pol.[PolRef@] = [tfwpl].[Fresh Ref])   

Open in new window


Basically I want to SELECT any records into a temporary table using the above statement but I don't want to select them if both the B@ and PolRef/Fresh Ref exists in the WINNS_Live_Policy_List.

It is only showing me 2 cases to insert on 4 pieces of business and after doing a simply 'SELECT' I can see that there are no records with the Fresh Ref & B@ that matches the 4 to go in so it should select 4 cases.

Help!
0
Comment
Question by:Lynchie435
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 13

Expert Comment

by:Koen Van Wielink
ID: 40311573
Are you sure the issue is with the not exists clause and not with another part of your select statement?
It would be helpful if you could provide some sample data.
0
 
LVL 34

Expert Comment

by:ste5an
ID: 40311642
Test your logic with a simplified SQL. E.g.
SELECT DISTINCT
        [c].[Name] AS 'Name' ,
        LEFT([c].[Forenames], ISNULL(NULLIF(CHARINDEX(' ', REVERSE([c].[Forenames])) - 1, -1), LEN([c].[Forenames]))) AS 'First Name' ,
        CASE WHEN CHARINDEX(' ', [c].[Forenames], 1) > 1 THEN SUBSTRING([c].[Forenames], CHARINDEX(' ', [c].[Forenames], 1) + 1, 20)
             ELSE ' '
        END AS 'Middle Names' ,
        RIGHT(c.Name, ISNULL(NULLIF(CHARINDEX(' ', REVERSE(c.Name)) - 1, -1), LEN(c.Name))) AS Surname ,
        [c].[Addr1] AS 'Address line 1' ,
        [c].[Addr2] AS 'Address line 2' ,
        [c].[Addr3] AS 'Address line 3' ,
        [c].[Addr4] AS 'Address line 4' ,
        [c].[Pcode] AS 'Postcode' ,
        pol.[Insco] AS 'Insurer' ,
        [pol].[Polno] AS 'Insurer Ref' ,
        [pol].[B@] AS 'Fresh Branch' ,
        [pol].[PolRef@] AS 'Fresh Ref' ,
        [pol].[Rdat] AS 'Renewal Date' ,
        [pol].[Idat] AS 'Inception Date' ,
        'N' AS [Status] ,
        ( SELECT    COUNT(*)
          FROM      [FreshSystems].dbo.WINNS_Live_Policy_List AS tfwpl
          WHERE     pol.[B@] = [tfwpl].[Fresh Branch]
                    AND pol.[PolRef@] = [tfwpl].[Fresh Ref]
        ) AS Cnt
FROM    [dbo].[ic_yyclient] AS c
        LEFT OUTER JOIN [dbo].[ic_brpolicy] AS pol ON [c].[B@] = [pol].[B@]
                                                      AND [c].[Ref@] = [pol].[Ref@]
WHERE   pol.[Ptype] IN ( 'TW', 'PC', 'MT' )
        AND pol.[B@] <> 1
        AND pol.[Term_date] IS NULL
        AND pol.[Rdat] >= dbo.[Today]()
        AND pol.[Insco] <> 'Hawkwell';
        

Open in new window

0
 
LVL 50

Expert Comment

by:Vitor Montalvão
ID: 40311646
Maybe you want to change the AND for an OR. Also you don't need the '*' so for performance issues I changed it to a constant value (1) :
.
.
.
AND NOT EXISTS ( SELECT 1
                         FROM   [FreshSystems].dbo.WINNS_Live_Policy_List AS tfwpl
                         WHERE  pol.[B@] = [tfwpl].[Fresh Branch]
                                OR pol.[PolRef@] = [tfwpl].[Fresh Ref])   

Open in new window

0
 
LVL 34

Expert Comment

by:ste5an
ID: 40311677
Also you don't need the '*' so for performance

It's a myth. The column selection is not necessary for the EXISTS predicate, thus the optimizer ignores it.
0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 500 total points
ID: 40316021
I have no idea what you are trying to do but the following are not OUTER JOINs, they are INNER JOINs:
LEFT OUTER JOIN [dbo].[ic_brpolicy] AS pol ON [c].[B@] = [pol].[B@]  AND [c].[Ref@] = [pol].[Ref@]
LEFT OUTER JOIN [dbo].[T_FIG_New_PolicyBrandRegister] AS pbr ON pol.[B@] = pbr.[Branch]  AND pol.[PolRef@] = pbr.[PolicyRef]

if you are wondering why the following conditions in your WHERE clause are the answer
WHERE   pol.[Ptype] IN ('TW', 'PC', 'MT')
        AND pol.[B@] <> 1
        AND pbr.[Affinity] IN ('Autosaint', 'Ladybird', 'Motor Trade', 'Special Risks')
        AND pol.[Term_date] IS NULL
        AND pol.[Rdat] >= dbo.[Today]()
        AND pol.[Insco] <> 'Hawkwell'

So if you really want an OUTER JOIN you need to code it correctly.  If on the other hand you want an INNER JOIN, why not write INNER JOIN instead, to avoid any future confusion.
0

Featured Post

Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how the fundamental information of how to create a table.

738 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question