real newie on derived columns

Question

real newie on derived columns

J M Davis

Grasshopper

Points: 13
More actions
January 8, 2005 at 8:28 pm

#61516

How can you use a derived column, derived from a case statetment for a test in a join statement in the "on" statement?
Example:
select
table1.some_column,
case when table2.some_column = '187' then '95' else '01' end as "derived.col"
case when table2.some_column = '197' then '93' else '01' end as "derived.col"
from
table1
join
table2
on
"derived.col" = table1.someother_column

Viewing 5 posts - 1 through 4 (of 4 total)

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

Kathi Kellenberger SSChampion Points: 11811 More actions · Answer 1

You can't really join on a derived column because the join happens before the column is derived. But, if I understand your question correctly this should work:

CREATE TABLE [dbo].[Table1] (

[someother_column] [varchar] (3) ,

[some_column] [varchar] (3)

) GO

CREATE TABLE [dbo].[Table2] (

[some_column] [varchar] (3)

) GO

insert into table1 select '95','a'
insert into table1 select '93','b'
insert into table1 select '01','c'
insert into table2 select '187'
insert into table2 select '197'
insert into table2 select '100'
insert into table2 select '50'
insert into table2 select '75'
--doesn't use the "inner join" syntax
select t1.some_column, t1.someother_column "derived.col"
from table1 t1, table2 t2 where t1.someother_column = case t2.some_column when '187' then '95'
when '197' then '93' else '01' end
--results
some_column derived.col ----------- ----------- a                   95 b                   93 c                   01 c                   01 c                   01
But if you are going to have lots of possibilities in your case statement, it would make more sense to add table3 to avoid hard coding:

CREATE TABLE [dbo].[Table3] ( [t1] [varchar] (3) , [t2] [varchar] (3) ) GO
insert into Table3 select '95','187'
insert into Table3 select '93','197'

select t1.some_column, [derived.col] from table1 t1 join (select case when t1 is null then '01' else t1 end
[derived.col] from table2 left join table3 on some_column = t2) t2 on t1.someother_column = t2.[derived.col]

-- results
some_column derived.col ----------- ----------- a                    95 b                    93 c                    01 c                    01 c                    01

Aunt Kathi Data Platform MVP
Author of Expert T-SQL Window Functions
Simple-Talk Editor

J M Davis Grasshopper Points: 13 More actions · Answer 2

Thank you,

I am learning: temp tables are good, hard code is bad!

Thanks again.

JM

Bob Monahon Ten Centuries Points: 1282 More actions · Answer 3

Hello J M Davis,

You can also tell SQL to calculate the derived column before the join, by putting the selections for table2 in a sub-select:

SELECT

table1.some_column

, "derived.col_one"

, "derived.col_two"

FROM table1

JOIN (SELECT

case when table2.some_column = '187' then '95' else '01' end as "derived.col_one"

, case when table2.some_column = '197' then '93' else '01' end as "derived.col_two"

FROM table2) AS t2

WHERE t2."derived.col_one" = table1.someother_column

Regards,

Bob Monahon

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 4

You can also place the CASE in the ON clause directly:

select *

from #t1 t1

join #t2 t2

on t1.col1 = case t2.col2

when 187

then 95

when 197

then 93

else 1

end

Note, however, that when using CASE, there is the caveat that CASE may only return one, and only one datatype, which means that in above example t1.col1 and t2.col2 along with the 'when/then' arguments in the CASE preferrably should be of the same datatype. If they aren't, there will be an implicit conversion to the datatype with the highets precedence. If implicit conversion cannot be done, there will be an error instead.

/Kenneth