Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 209
  • Last Modified:

SQL NOT EXISTS not working

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
Lynchie435
Asked:
Lynchie435
1 Solution
 
Koen Van WielinkIT ConsultantCommented:
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
 
ste5anSenior DeveloperCommented:
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
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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
 
ste5anSenior DeveloperCommented:
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
 
Anthony PerkinsCommented:
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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now