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_expression 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

Popular posts from this blog

javascript - Using jquery append to add option values into a select element not working -

Android soft keyboard reverts to default keyboard on orientation change -

Rendering JButton to get the JCheckBox behavior in a JTable by using images does not update my table -