If and Join - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

If and Join

Author

Topic

kwikwisi
Constraint Violating Yak Guru

283 Posts

Posted - 2010-02-08 : 21:33:37

I want to set the variable value and use it in join clause. something like but I dont know how to arrange it.

@test as varchar(10)
as
select col1,col2, if (col3=1) set @test='A' else if (col3=2) set @test='B' else @test='C'from table1 join table2 on table2.col=@test +'something'

Thanks

kwikwisi
Constraint Violating Yak Guru

283 Posts

Posted - 2010-02-09 : 01:04:04

Thanks.
I got following error.

"A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations.
"

quote:
Originally posted by haroon2k9

please try now

select col1,col2,@test=case when col3=1 then 'A'
when col3=2 then 'B' ELSE 'C' END
from tbl

Sachin.Nand

2937 Posts

Posted - 2010-02-09 : 01:08:03

select * from
(
select col1,col2, case col3 when 1 then 'A' when 2 then 'B' else 'C' end as test from table1

)t inner join table2 on table2.col=t.test

PBUH

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-02-09 : 01:10:55

[code]
select col1,
col2
from table1 t1
inner join table2 t2 on t2.col = case col3
when 1 then 'A'
when 2 then 'B'
else 'C'
end + 'something'
[/code]

KH
[spoiler]Time is always against us[/spoiler]

kwikwisi
Constraint Violating Yak Guru

283 Posts

Posted - 2010-02-09 : 01:30:27

thanks all.

thanks khtan. you helped me correct.

quote:

Originally posted by khtan


select col1,
       col2
from   table1 t1
       inner join table2 t2 on t2.col = case col3
                                             when 1 then 'A'
                                             when 2 then 'B'
                                             else        'C'
                                             end + 'something'

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources