More SQL Server Weirdness



  • I have a lookup table with 120 rows that correspond to rows in my main products table.  When I run a join or WHERE EXISTS(SELECT sku FROM products WHERE products.sku = lookup_table.sku) clause, the stupid thing is only returning 114 rows.  using NOT EXISTS gives me the six missing rows, which I then verify EXIST in the products table by querying it for the value, and obtaining the row.  I have tried stripping whitespace with LTrim(RTrim(lookup_table.sku)), and still nothing.  It won't display these six rows no matter what I try, even though they are in both tables and should be picked up by the INNER JOIN

    Anyone experience this kind of behavior?  Using SQL Server 2005 Developer



  • @ObiWayneKenobi said:

    LTrim(RTrim(lookup_table.sku))
     

    Case sensitivity? What are the types of the two .sku fields? Given that 'not exists' returns the missing rows, there's obviously some difference between the two tables' sku fields, and/or the contents of those 6 SKU records.



  • They're both the same case (Uppercase), and both fields are nvarchar(255).  So I have no idea why they aren't being caught.. I wonder if it has something to do with the fact I imported the lookup table from Excel - Another lookup table is doing the same thing with the products table, so it's not just the one table.



  • I'd write it as "SELECT sku FROM products WHERE sku IN (SELECT sku from lookup)". Don't have access to MSSQL to test, but it works fine in a couple quick 'n dirty tables in MySQL:


    mysql> insert into products (sku) values ('a'), ('b'), ('c'), ('d'), ('e');
    mysql> insert into lookup (sku) values ('a'), ('e');
    mysql> select sku from products where sku in (select sku from lookup);
    +------+
    | sku  |
    +------+
    | a    |
    | e    |
    +------+
    2 rows in set (0.00 sec)
     

    or alternatively


    mysql> select products.sku from products right join lookup on products.sku = lookup.sku;
    +------+
    | sku  |
    +------+
    | a    |
    | e    |
    +------+
    2 rows in set (0.00 sec)

    and for the opposite effect


    mysql> select products.sku from products left join lookup on products.sku = lookup.sku where lookup.sku is null;
    +------+
    | sku  |
    +------+
    | b    |
    | c    |
    | d    |
    +------+



  • Check the collation on each of the nvarchar fields and make sure they are the same as that will affect comparison operations.  I prefer to override the defaults of SQL 2005 to use OS collations instead of SQL collations - I setup SQL to use the defaults of Analysis Services which is Latin1_General_CI_AS.

    If all else fails, I'd do a SELECT '-' + FieldName + '-' FROM Table and see if you get any weird characters coming through.  I had an imported dataset do the same thing a few months back where a non-printable character was appended that TRIM, RTRIM, LTRIM didn't handle.  Another option is to just throw the FOR XML PATH at the end of the SELECT statement and look for any weird encoded character values in the resulting XML data.

     



  • I forgot that you'll get errors if the collations are incompatible, so I think case 2 is the more likely.  To demonstrate, here is sample SQL of what I think may be happening

    BEGIN TRY
        PRINT N'Case 2'

        DECLARE @String3 nvarchar(50)
        DECLARE @String4 nvarchar(50)

        SET @String3 = N''
        SET @String4 = NCHAR(160)

        PRINT N'@String3: |' + @String3 + N'|'
        PRINT N'@String4: |' + @String4 + N'|'

        IF (RTRIM(LTRIM(@String3)) = RTRIM(LTRIM(@String4))) BEGIN
            PRINT N'@String3 EQUALS @String4'
        END
        ELSE BEGIN
            PRINT N'@String3 DOES NOT EQUAL @String4'
        END
            
        DECLARE @XML nvarchar(MAX)

        SET @XML = (SELECT @String3 as String3, @String4 as String4 FOR XML PATH)

        PRINT @XML
    END TRY
    BEGIN CATCH
        PRINT ERROR_MESSAGE()
    END CATCH

     

    Resulting in 

    Case 2
    @String3: ||
    @String4: | |
    @String3 DOES NOT EQUAL @String4
    <row><String3></String3><String4> </String4></row>

    I could have sworn that FOR XML encoded the no break space - maybe I found it by exporting the Excel doc to HTML.

     



  • @ObiWayneKenobi said:

    I imported the lookup table from Excel
     

    I think I've had something similar in the past, check for &nbsp or other odd characters appended to the end of the affected fields?

    Second thoughts, I think my problem was data exported from SQLServer to Excel and some cells wouldn't behave as numbers in Excel because of &nbsp or something tacked on the end of some fields.

     


Log in to reply