Odd results from WHERE in SQL

  • jdasilva

    Ten Centuries

    Points: 1056

    So I have a bit of VBA put together to manage the output of data based on selections made from a form in Access 2016. This all seemed to work perfectly. I was asked to further streamline the output and added one more line (or variation based on selections). The SQL statement looks like this:

    SELECT DISTINCT
    TOP (100) PERCENT dbo.StaffListings.LocCode, dbo.StaffListings.AutoNumber, dbo.StaffListings.Floor, dbo.StaffListings.LastName, dbo.StaffListings.ShowName, dbo.StaffListings.FirstName, LEN(dbo.StaffListings.Extension) AS ExtLen, dbo.StaffListings.Department, dbo.StaffListings.FirstAid, dbo.StaffListings.FireWarden, dbo.StaffListings.SpecialStatus, dbo.OfficeCodes.Address1, dbo.OfficeCodes.Address2, dbo.OfficeCodes.City, dbo.OfficeCodes.StateProv, dbo.OfficeCodes.PostalZip, dbo.OfficeCodes.PhoneNo, dbo.OfficeCodes.FaxNo, dbo.StaffListings.TempFlex, dbo.OfficeCodes.SharepointPhone, dbo.OfficeCodes.ExtraText, LEFT(dbo.StaffListings.LastName, 1) AS Heads, '' AS [Dummy], dbo.StaffListings.Extension, dbo.StaffListings.[10DigitPhone], '' AS OfficeNumber, '' AS [Column], IIf(dbo.StaffListings.LastName=dbo.StaffListings.ShowName, dbo.StaffListings.LastName + ' ' + dbo.StaffListings.ShowName, dbo.StaffListings.LastName + ', ' + dbo.StaffListings.ShowName) as outText

    FROM dbo.StaffListings INNER JOIN dbo.OfficeCodes ON dbo.StaffListings.LocCode = dbo.OfficeCodes.LocCode

    WHERE (((dbo.StaffListings.LocCode) Like N'TOR') AND (NOT (dbo.StaffListings.LastName LIKE N'0ConfRoom%')) AND (NOT (dbo.StaffListings.LastName LIKE N'0RoomExtra')) AND (NOT (dbo.StaffListings.LastName LIKE N'0PhoneExtra')) AND (NOT (dbo.StaffListings.LastName LIKE N'0Emerg')) AND ((LEN(dbo.StaffListings.Extension) > 0) OR (LEN(dbo.StaffListings.[10DigitPhone]) > 1)) AND (NOT(dbo.StaffListings.SpecialStatus LIKE '+++')))
    ORDER BY outText

    The part that was most recently added is in bold above. The variations would be to remove the NOT. The problem is this strips out more data than just excluding the value of +++, and I am not sure why. I have gone through the data, and have marked only 2 names with the +++ value, but about have the names don't show when this last AND section is added.

  • Luis Cazares

    SSC Guru

    Points: 183532

    jdasilva - Tuesday, June 26, 2018 6:53 AM

    So I have a bit of VBA put together to manage the output of data based on selections made from a form in Access 2016. This all seemed to work perfectly. I was asked to further streamline the output and added one more line (or variation based on selections). The SQL statement looks like this:


    SELECT DISTINCT TOP (100) PERCENT dbo.StaffListings.LocCode
        ,dbo.StaffListings.AutoNumber
        ,dbo.StaffListings.Floor
        ,dbo.StaffListings.LastName
        ,dbo.StaffListings.ShowName
        ,dbo.StaffListings.FirstName
        ,LEN(dbo.StaffListings.Extension) AS ExtLen
        ,dbo.StaffListings.Department
        ,dbo.StaffListings.FirstAid
        ,dbo.StaffListings.FireWarden
        ,dbo.StaffListings.SpecialStatus
        ,dbo.OfficeCodes.Address1
        ,dbo.OfficeCodes.Address2
        ,dbo.OfficeCodes.City
        ,dbo.OfficeCodes.StateProv
        ,dbo.OfficeCodes.PostalZip
        ,dbo.OfficeCodes.PhoneNo
        ,dbo.OfficeCodes.FaxNo
        ,dbo.StaffListings.TempFlex
        ,dbo.OfficeCodes.SharepointPhone
        ,dbo.OfficeCodes.ExtraText
        ,LEFT(dbo.StaffListings.LastName, 1) AS Heads
        ,'' AS [Dummy]
        ,dbo.StaffListings.Extension
        ,dbo.StaffListings.[10DigitPhone]
        ,'' AS OfficeNumber
        ,'' AS [Column]
        ,IIf(dbo.StaffListings.LastName = dbo.StaffListings.ShowName, dbo.StaffListings.LastName + ' ' + dbo.StaffListings.ShowName, dbo.StaffListings.LastName + ', ' + dbo.StaffListings.ShowName) AS outText
    FROM dbo.StaffListings
    INNER JOIN dbo.OfficeCodes ON dbo.StaffListings.LocCode = dbo.OfficeCodes.LocCode
    WHERE (
            ((dbo.StaffListings.LocCode) LIKE N'TOR')
            AND (NOT (dbo.StaffListings.LastName LIKE N'0ConfRoom%'))
            AND (NOT (dbo.StaffListings.LastName LIKE N'0RoomExtra'))
            AND (NOT (dbo.StaffListings.LastName LIKE N'0PhoneExtra'))
            AND (NOT (dbo.StaffListings.LastName LIKE N'0Emerg'))
            AND (
                (LEN(dbo.StaffListings.Extension) > 0)
                OR (LEN(dbo.StaffListings.[10DigitPhone]) > 1)
                )
            AND (NOT (dbo.StaffListings.SpecialStatus LIKE '+++'))
            )
    ORDER BY outText;

    The part that was most recently added is in bold above. The variations would be to remove the NOT. The problem is this strips out more data than just excluding the value of +++, and I am not sure why. I have gone through the data, and have marked only 2 names with the +++ value, but about have the names don't show when this last AND section is added.

     Is this for Access or SQL Server?
    What rows are you missing? Are there NULLs in those rows? Why not use simply <> instead of NOT and LIKE?

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • jdasilva

    Ten Centuries

    Points: 1056

    This script is built in VBA for use in access 2016.

    honestly, SQL is not my first language.  The base for the SQL statement was most likely an access query converted to SQL.  So you think it may be better to use = or <> instead of Like and Not...Like?  I will give that a try.

    A quick question (since you replied back so quick) about the other LIKEs above.  I cannot remember why I put them (or if it was how I found it), but are the N' s needed?  I had it with the last clause, but took it out to see if that was something causing the issue.  In any case, will try the <> just now and let you know how it goes.  Thanks for the quick reply.

  • Lynn Pettis

    SSC Guru

    Points: 442118

    jdasilva - Tuesday, June 26, 2018 7:33 AM

    This script is built in VBA for use in access 2016.

    honestly, SQL is not my first language.  The base for the SQL statement was most likely an access query converted to SQL.  So you think it may be better to use = or <> instead of Like and Not...Like?  I will give that a try.

    A quick question (since you replied back so quick) about the other LIKEs above.  I cannot remember why I put them (or if it was how I found it), but are the N' s needed?  I had it with the last clause, but took it out to see if that was something causing the issue.  In any case, will try the <> just now and let you know how it goes.  Thanks for the quick reply.

    Here is my critique of your code based T-SQL:

    SELECT  DISTINCT TOP  (100) PERCENT -- No reason for the TOP (100) PERCENT, you want all the data.  Why is there a DISTINCT?
            [sl].[LocCode]
            , [sl].[AutoNumber]
            , [sl].[Floor]
            , [sl].[LastName]
            , [sl].[ShowName]
            , [sl].[FirstName]
            , LEN([sl].[Extension])                                                                                                                                                                                          AS [ExtLen]
            , [sl].[Department]
            , [sl].[FirstAid]
            , [sl].[FireWarden]
            , [sl].[SpecialStatus]
            , [oc].[Address1]
            , [oc].[Address2]
            , [oc].[City]
            , [oc].[StateProv]
            , [oc].[PostalZip]
            , [oc].[PhoneNo]
            , [oc].[FaxNo]
            , [sl].[TempFlex]
            , [oc].[SharepointPhone]
            , [oc].[ExtraText]
            , LEFT([sl].[LastName], 1)                                                                                                                                                                                       AS [Heads]
            , ''                                                                                                                                                                                                                              AS [Dummy]
            , [sl].[Extension]
            , [sl].[10DigitPhone]
            , ''                                                                                                                                                                                                                              AS [OfficeNumber]
            , ''                                                                                                                                                                                                                              AS [Column]
            , IIF([sl].[LastName] = [sl].[ShowName], [sl].[LastName] + ' ' + [sl].[ShowName], [sl].[LastName] + ', ' + [sl].[ShowName]) AS [outText]
    FROM
      [dbo].[StaffListings]          AS [sl]   -- Added table alaises and used those in the select, on, and where clauses.  Three and four part naming convention has been deprecated
      INNER JOIN [dbo].[OfficeCodes] AS [oc]   -- Added table alaises and used those in the select, on, and where clauses.  Three and four part naming convention has been deprecated
        ON [sl].[LocCode] = [oc].[LocCode]
    WHERE
      (
        (([sl].[LocCode]) = N'TOR') -- (([sl].[LocCode]) LIKE N'TOR')                                -- None of these are using wildcards
        AND (NOT ([sl].[LastName] = N'0ConfRoom%')) -- (NOT ([sl].[LastName] LIKE N'0ConfRoom%'))    -- None of these are using wildcards
        AND (NOT ([sl].[LastName] = N'0RoomExtra')) -- (NOT ([sl].[LastName] LIKE N'0RoomExtra'))    -- None of these are using wildcards
        AND (NOT ([sl].[LastName] = N'0PhoneExtra')) -- (NOT ([sl].[LastName] LIKE N'0PhoneExtra'))  -- None of these are using wildcards
        AND (NOT ([sl].[LastName] = N'0Emerg')) -- (NOT ([sl].[LastName] LIKE N'0Emerg'))            -- None of these are using wildcards
        AND
          (
            (LEN([sl].[Extension]) > 0)
            OR (LEN([sl].[10DigitPhone]) > 1)
          )
        AND (NOT ([sl].[SpecialStatus] = '+++')) -- (NOT ([sl].[SpecialStatus] LIKE '+++'))          -- Not using wildcards
      )
    ORDER BY
      [outText]; -- What table is this column in, need to use the table alais to identify the table

  • Luis Cazares

    SSC Guru

    Points: 183532

    jdasilva - Tuesday, June 26, 2018 7:33 AM

    This script is built in VBA for use in access 2016.

    honestly, SQL is not my first language.  The base for the SQL statement was most likely an access query converted to SQL.  So you think it may be better to use = or <> instead of Like and Not...Like?  I will give that a try.

    A quick question (since you replied back so quick) about the other LIKEs above.  I cannot remember why I put them (or if it was how I found it), but are the N' s needed?  I had it with the last clause, but took it out to see if that was something causing the issue.  In any case, will try the <> just now and let you know how it goes.  Thanks for the quick reply.

    LIKE is used for wildcards. If you're not using wildcards, there's no reason for using LIKE. In fact, it could produce unexpected implicit conversions that can cause problems.
    If you are dealing with NULLs, you need to consider that you have 3-valued logic (true, false, unknown). Read a bit about it and how the truth tables differ from 2-valued logic.
    Ns before a string are defining the string as unicode. You should avoid them if they're not needed because that can also cause undesired implicit conversions that would prevent the use of indexes.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • drew.allen

    SSC Guru

    Points: 76493

    Lynn Pettis - Tuesday, June 26, 2018 8:27 AM

    Here is my critique of your code based T-SQL:

     AND (NOT ([sl].[LastName] = N'0ConfRoom%')) -- (NOT ([sl].[LastName] LIKE N'0ConfRoom%'))    -- None of these are using wildcards

    I think you were a bit overzealous here Lynn.  This criteria is using a wildcard.

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • Lynn Pettis

    SSC Guru

    Points: 442118

    drew.allen - Tuesday, June 26, 2018 2:29 PM

    Lynn Pettis - Tuesday, June 26, 2018 8:27 AM

    Here is my critique of your code based T-SQL:

     AND (NOT ([sl].[LastName] = N'0ConfRoom%')) -- (NOT ([sl].[LastName] LIKE N'0ConfRoom%'))    -- None of these are using wildcards

    I think you were a bit overzealous here Lynn.  This criteria is using a wildcard.

    Drew

    Oh, is that the ACCESS wildcard?  Never coded in Access.

  • Lynn Pettis

    SSC Guru

    Points: 442118

    Lynn Pettis - Tuesday, June 26, 2018 2:39 PM

    drew.allen - Tuesday, June 26, 2018 2:29 PM

    Lynn Pettis - Tuesday, June 26, 2018 8:27 AM

    Here is my critique of your code based T-SQL:

     AND (NOT ([sl].[LastName] = N'0ConfRoom%')) -- (NOT ([sl].[LastName] LIKE N'0ConfRoom%'))    -- None of these are using wildcards

    I think you were a bit overzealous here Lynn.  This criteria is using a wildcard.

    Drew

    Oh, is that the ACCESS wildcard?  Never coded in Access.

    Nevermind, missed the %.  Sorry, my mistake, slap my wrist.

  • jdasilva

    Ten Centuries

    Points: 1056

    Well, there are a few with wildcards, but not all, so knowing the difference is a good thing.  Unfortunately, using <> and = gives the same results.  Well, really the LIKE/= gives the proper results.  The problem is with the NOT...LIKE/<>. 

    Now the NULL issue may be the case.  For instance, there are about 17 names in the 'last name begins with A' section.  I have marked 2 with the special status of +++.  So in theory, if I exclude (<>) +++, then I should see about 15.  I see 3.  If I add something like 'test' to a few others in this section and run it again, they show up.

    Ok, was testing while typing this, and the NULL check is what was missing.  Funny, in many of the records , specialstatus is blank.  But for some reason, some are seen as NULL and others not.  I have tried entering data, and then removing it, but for some reason, some are seen as NULL, and others not.  In any case, this was the fix:

        If Me.typeCombo.Value = "Standard" Then
            sqlSelect = sqlSelect & "AND ((dbo.StaffListings.SpecialStatus <> '+++') OR (dbo.StaffListings.SpecialStatus IS NULL))"
        Else
            sqlSelect = sqlSelect & "AND ((dbo.StaffListings.SpecialStatus = '+++'))"
        End If
       
        sqlSelect = sqlSelect & ")"

    Thanks for fighting with this with me.  I have learned a few better habits from this.  Thanks!

  • Luis Cazares

    SSC Guru

    Points: 183532

    jdasilva - Wednesday, June 27, 2018 7:37 AM


    Thanks for fighting with this with me.  I have learned a few better habits from this.  Thanks!

    Thanks for the feedback. Dealing with NULLs and three-valued logic is something that newbies struggle with. But once you understand it, it becomes very simple and obvious. It's good that you have learned something from this.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • roger.plowman

    SSChampion

    Points: 10136

    jdasilva - Wednesday, June 27, 2018 7:37 AM

    Well, there are a few with wildcards, but not all, so knowing the difference is a good thing.  Unfortunately, using <> and = gives the same results.  Well, really the LIKE/= gives the proper results.  The problem is with the NOT...LIKE/<>. 

    Now the NULL issue may be the case.  For instance, there are about 17 names in the 'last name begins with A' section.  I have marked 2 with the special status of +++.  So in theory, if I exclude (<>) +++, then I should see about 15.  I see 3.  If I add something like 'test' to a few others in this section and run it again, they show up.

    Ok, was testing while typing this, and the NULL check is what was missing.  Funny, in many of the records , specialstatus is blank.  But for some reason, some are seen as NULL and others not.  I have tried entering data, and then removing it, but for some reason, some are seen as NULL, and others not.  In any case, this was the fix:

        If Me.typeCombo.Value = "Standard" Then
            sqlSelect = sqlSelect & "AND ((dbo.StaffListings.SpecialStatus <> '+++') OR (dbo.StaffListings.SpecialStatus IS NULL))"
        Else
            sqlSelect = sqlSelect & "AND ((dbo.StaffListings.SpecialStatus = '+++'))"
        End If
       
        sqlSelect = sqlSelect & ")"

    Thanks for fighting with this with me.  I have learned a few better habits from this.  Thanks!

    The problem is in Access Nulls aren't visually distinct from an empty string. I've used Access since 1.0 and that was probably the biggest reason I went to the "no nulls ANYWHERE" camp. 🙂 Nulls are evil. At least in SQL Server nulls are visually distinct.

  • jdasilva

    Ten Centuries

    Points: 1056

    Yes.  The odd thing for me was that this field is almost never used (in the past), but some are NULL and some are not, even though all such entries are blank.  Lesson learned, always check for NULL...

    Just to finalize out.  That initial flag was to get things working.  Realistically, I would like the data entry person to put something more descriptive in the status.  Site this flag is for those not in the office (working on site) I chose to do this :

        'Seperate out SpecialStatus value
        If Me.typeCombo.Value = "Standard" Then
            'sqlSelect = sqlSelect & "AND ((dbo.StaffListings.SpecialStatus <> '+++') OR (dbo.StaffListings.SpecialStatus IS NULL))"
            sqlSelect = sqlSelect & "AND (NOT(dbo.StaffListings.SpecialStatus LIKE N'%@%') OR (dbo.StaffListings.SpecialStatus IS NULL))"
        Else
            sqlSelect = sqlSelect & "AND ((dbo.StaffListings.SpecialStatus LIKE N'%@%'))"
            'sqlSelect = sqlSelect & "AND ((dbo.StaffListings.SpecialStatus = '+++'))"
        End If

    So with this, they could put @ZZZ to indicate that the staff memeber is at the site coded by ZZZ.  I don't know if I stated the idea behind this, but the reasoning is that our office is expanding, and there are quite a few people on our phone list that are not in this office.  This allows me to cull them out and allow them to be printed on a separate report. 

    Thanks again to all.

Viewing 12 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply