Solved

SQL NOT EXISTS not working

Posted on 2014-09-09
5
188 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 51

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

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

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

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to shrink a transaction log file down to a reasonable size.

615 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