Post reply

Concatenation & Matching

  • March 2, 2006 at 6:51 pm

    #89644

    Hi,

    I have a scenario where I am using a concatenation of a couple of fields in the where clause and comparing them to the concatenation of another couple of fields in another table.

     

    eg

    Select *

    from tab1

    where col1+col2+cast(col3 as varchar(20))

    not in (select col1+col2+cast(col3 as varchar(20)) from tab2)

     

    Now the problem is that when I include the field that is using the cast function, nothing is returned. As soon as I remove that field it returns values.

    If I display the values which I am interogating against, I can noticably see that there is a difference - ie I know that the select from tab2 does not match the where constraint from tab1 - so I cannot understand why it is not returning anything?!?!

    Is there some weird thing that I may have missed?

    The field format from tab 1 for col3 is int and likewise with the col3 from tab2.

    Can anyone shed some light on this at all? If I need to offer more detail - please let me know, but I have attempted to simplify it.

    Thanks

    Troy

  • March 2, 2006 at 9:18 pm

    #624518

    I see at least wrong syntax:

    cast(col3 as varchar(20))

    _____________
    Code for TallyGenerator

  • March 2, 2006 at 9:22 pm

    #624519

    opps sorry mistake while copying - the script runs without error - will update thread.

    It's just the results that seem weird for some reason.

  • March 2, 2006 at 10:35 pm

    #624530

    A better way to do this for the sake of performance is...

     SELECT tab1.*

       FROM tab1

       LEFT OUTER JOIN tab2

         ON tab1.Col1 = tab2.Col1

            tab1.Col2 = tab2.Col2

            tab1.Col3 = tab2.Col3

      WHERE tab2.Col1 IS NULL

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • March 3, 2006 at 2:54 am

    #624552

    Hard to answer this question without DDL and sample data.. An obvious reason could be that one of the columns used in concatenation contains NULL values. If only one of the concatenated columns has a NULL in it, the entire result is NULL... and NULL is a special value that will cause the query to not return this row.

    Try this:

    select 1 where 'A' not in ('B', 'C')

    select 1 where NULL not in ('B', 'C')

    select 1 where 'A' not in ('B', NULL)

    So, even if a single value in the IN-list (select col1+col2+cast(col3 as varchar(20)) from tab2) is NULL, you have a problem and the query returns nothing.

    Question: Can you use the method suggested by Jeff? The original method is necessary if you have to cope with situations like:

    tab1 : col1 = 'Stop here - ', col2 = 'DANGER!'

    tab2 : col1 = 'Stop here', col2 = ' - DANGER!'

    If these rows have to be evaluated as "equal" in terms of your query, then you really have to concatenate the values and only after that compare. If this is not the case, consider using the LEFT JOIN syntax. It is better than using "NOT IN" clause.

    In both cases, if there are NULL values in some of the columns, you have to decide what to do with them. Functions ISNULL and COALESCE should take care of that problem, like replacing NULL with an empty string : ISNULL(col1, '')

  • March 3, 2006 at 8:17 am

    #624627

    If Jeff's and Vladan's posts don't help you, please see if you can write a script that reconstructs the error. I.e give us table definitions, a few sample data on script from ("insert into" statements) along with the statement that fails.

    Jeff's query should probably be

    SELECT tab1.*

       FROM tab1

       LEFT OUTER JOIN tab2

         ON tab1.Col1 = tab2.Col1 and

            tab1.Col2 = tab2.Col2 and

            tab1.Col3 = tab2.Col3

      WHERE tab2.Col1 IS NULL

  • March 3, 2006 at 8:19 am

    #624628

    Hi ,

    also checks for Null values..

    Select *

    from tab1

    where isnull(col1,'')+isnull(col2,'')+cast(isnull(col3,'') as varchar(20))

    not in (select isnull(col1,'')+isnull(col2,'')+(cast(isnull(col3,'') as varchar(20)) from tab2)

    Regards

    Amit Gupta

  • March 3, 2006 at 10:22 am

    #624673

    To simply check, whether NULL-values are the reason, issue a

    SET CONCAT_NULL_YIELDS_NULL OFF

    in your QA before running the actual query.

     

     


    _/_/_/ paramind _/_/_/

  • March 5, 2006 at 12:36 pm

    #624790

    unbelievable!! It was NULLS!! Damn usually I am aware of these and code for them, but this time had overlooked some real basic ones (couldn't see the tree's for the forest scenario!) - had ISNULLS all over the place but just left them out of one area *groan*

    Thanks everyone for your responses - I will have a look into that OUTER JOIN instead, as always keen to take some advice on performance tweaks.

    Much appreciated!

Viewing 9 posts - 1 through 8 (of 8 total)

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