Solved

SQL NOT EXISTS not working

Posted on 2014-09-09
5
171 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 33

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 49

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 33

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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL query for highest sequence 4 59
MS SQL Server connect issues 4 37
removing unwanted rows from an sql server ranked table 13 34
Convert VBA UDF to SQl SERVER UDF 4 47
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…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

740 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