sql - CASE statement in JOIN on different Data Type -
i wondering if possible use case
statement on join
arguments returned case
of different type.
i try explain better question example: have 2 tables: tab1
, tab2
. in column of table 2
have parameters divided pipe character "|".
depending on length of first parameter need join specific column of tab1
. in 1 case column of tab1
has integer
values, in other varchar
.
i tried this:
select * tab1 t1 inner join tab2 t2 on isnull(nullif(substring(t2.param, 0, charindex('|', t2.param, 1)),''), t2.param) = case when len(isnull(nullif(substring(t2.param, 0, charindex('|', t2.param, 1)),''), t2.param)) >= 16 t1.codfis when isnull(nullif(substring(t2.param, 0, charindex('|', t2.param, 1)),''), t2.param) < 16 t1.id end
but returns error:
conversion failed when converting varchar value 'paacpp70h41b296y' data type int.
assuming t1.id
of integer
type. data types of input_expression
, each when_expressio
n must same or must implicit conversion
select * tab1 t1 inner join tab2 t2 on isnull(nullif(substring(t2.param, 0, charindex('|', t2.param, 1)), ''), t2.param) = case when len(isnull(nullif(substring(t2.param, 0, charindex('|', t2.param, 1)), ''), t2.param)) >= 16 t1.codfis when isnull(nullif(substring(t2.param, 0, charindex('|', t2.param, 1)), ''), t2.param) < 16 convert(varchar(50), t1.id) end end
Comments
Post a Comment