Help need in using condition on join statement

Question

Help need in using condition on join statement

KGJ-Dev

SSCertifiable

Points: 5780
More actions
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.

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

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

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

KGJ-Dev SSCertifiable Points: 5780 More actions · Answer 2

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

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

KGJ-Dev SSCertifiable Points: 5780 More actions · Answer 4

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

Hugo Kornelis SSC Guru Points: 64790 More actions · Answer 5

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/

drew.allen SSC Guru Points: 76989 More actions · Answer 6

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

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

Hugo Kornelis SSC Guru Points: 64790 More actions · Answer 8

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/

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 9

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

KGJ-Dev SSCertifiable Points: 5780 More actions · Answer 10

KGJ-Dev

SSCertifiable

Points: 5780

January 7, 2016 at 1:20 pm

#1850338

thanks a lot everyone for the inputs