Post reply

Help need in using condition on join statement

  • January 7, 2016 at 10:27 am

    #323455

    Hi,

    Declare @T1 table(Code varchar(25),name varchar(20));

    Declare @T2 table(Code varchar(25), searchname varchar(20));

    insert into @T1

    select '12345','sample1' union all

    select '00012345','sample2' union all

    select '12340005','sample3' union all

    select '12300045','sample4' union all

    select '1200345','sample5' union all

    select '1203405','sample6'

    insert into @T2

    select '12345' ,'sample100' union all

    select '00012345' ,'sample200' union all

    select '12341235' ,'sample300' union all

    select '123444045' ,'sample400' union all

    select '1201345' ,'sample500' union all

    select '1203405' ,'sample600'

    update T1 set T1.name = T2.searchname from @T1 T1 join @T2 T2 on(case len(T2.Code) > 4 then T2.Code like '%' + T1.Code + '%' else T2.code = T.code );

    Requirement :

    Get values from @t2 and match it with @t2. [wild card match] if exists then update value of searchname on name column

    if not create an new entry in the @T1 table.

    it's giving error. Basically i would like to know how to use the case statement in "ON" Clause. Any help please with sample.

    Thanks.

  • January 7, 2016 at 10:50 am

    #1850271

    The thing to remember is that the output of the CASE statement is an expression, not a piece of code. That is, it's something that you could put after a variable assignment.

    So you can do

    CASE WHEN <condition> THEN '%' ELSE '' END + Column = <expression>,

    but not

    CASE WHEN <condition> THEN '%' + Column = <expression> ELSE Column = <expression> END

    And you use CASE in an ON clause the same way you use it anywhere else, to return an expression.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • January 7, 2016 at 11:02 am

    #1850276

    Hi Gail,

    Thanks for the reply and can you please edit my sample update query as per your advice to help me on understanding please

  • January 7, 2016 at 11:29 am

    #1850289

    Look at the two pieces I gave you. The one I said you can't do is what you're trying. So take the one I said you can do (which is very close to what you need) and figure it out

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • January 7, 2016 at 11:43 am

    #1850297

    Hi Gail,

    I am struggling to make it where to put like operator and where to put '%' at the end

    update t1 set name = T2.searchname from @T1 T1 join @T2 T2

    ON ( CASE WHEN len(t2.Code) >4 THEN '%' ELSE '' END + t1.Code = <expression>,

    I would request you to help on giving the sample code on my logic.

    Thanks

  • January 7, 2016 at 11:56 am

    #1850303

    The short answer is to replace <<expression>> with T2.Code

    The longer and better answer is to warn you about the weird behaviour of UPDATE FROM when there is no strict 1-to-1 relationship. And to ask you what you are actually trying to achieve, because this query smells fishy.

    Also, if your table is large expect terrible performance.

    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • January 7, 2016 at 12:11 pm

    #1850308

    Hint: LIKE without wildcards in the second expression is equivalent to =

    Given that, think of it like the distributive property. You have an element in common in both inner expressions and you need to move that outside your CASE statement.

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • January 7, 2016 at 12:20 pm

    #1850312

    KGJ-Dev (1/7/2016)

    I am struggling to make it where to put like operator and where to put '%' at the end

    The = should have been a LIKE, sorry about that. To place the trailing %, you need another case statement. Look at the way I did the leading %

    And yes, this is going to perform terribly.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • January 7, 2016 at 12:38 pm

    #1850319

    GilaMonster (1/7/2016)

    KGJ-Dev (1/7/2016)

    I am struggling to make it where to put like operator and where to put '%' at the end

    The = should have been a LIKE, sorry about that. To place the trailing %, you need another case statement. Look at the way I did the leading %

    And yes, this is going to perform terribly.

    I overlooked that as well.

    You can also use a single CASE expression: CASE WHEN ... THEN '%' + Whatever + '%' ELSE Whatever END

    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • January 7, 2016 at 12:40 pm

    #1850320

    True, I overcomplicated things a little.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • January 7, 2016 at 1:20 pm

    #1850338

    thanks a lot everyone for the inputs

Viewing 11 posts - 1 through 10 (of 10 total)

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