I am trying to join two tables in the below query in Snowflake. And it is throwing me the below error since, tst1.id is varchar and tst2.id is binary

Can not convert a parameter tst2.id of type BINARY(16) into expected type VARCHAR(40)

My Query:

Select tst2.id, tst1.id 
from test1 as tst1
inner join test2 as tst2
on tst1.id = tst2.id
where tst2.id ::binary = '18374683274748987' :: binary
and tst2.date :: date >= '2022-06-20' :: date;

CodePudding user response：

You cannot encode to binary directly strings, they need to be in a HEX format, so this won't work:

select '18374683274748987' :: binary;

The following string is not a legal hex-encoded value: '18374683274748987'

but this will:

select to_binary(hex_encode('18374683274748987'), 'HEX');

TO_BINARY(HEX_ENCODE('18374683274748987'), 'HEX')
3.138333734363833323734373438393837e 33

In your case try:

Select tst2.id, tst1.id 
from test1 as tst1
inner join test2 as tst2
on tst1.id = tst2.id
where tst2.id = to_binary(hex_encode('18374683274748987'), 'HEX')
and tst2.date :: date >= '2022-06-20' :: date;

CodePudding user response：

So if we start with no ON/WHERE's and just get the data types correct in some example data:

WITH test1(id) as (
    select * 
    from values
        ('18374683274748987'::varchar)
), test2(id, date) as (
    select to_binary(hex_encode(column1)), column2::date 
    from values
        ('18374683274748987', '2022-06-20')
)
Select 
    tst2.id as bin_val, 
    tst1.id as text_val
from test1 as tst1
join test2 as tst2

so that ON join on tst1.id = tst2.id will cause the error

Can not convert parameter 'TST2.ID' of type [BINARY(8388608)] into expected type [VARCHAR(16777216)]

because the type are wrong, so that should become:

    on to_binary(hex_encode(tst1.id), 'HEX') = tst2.id

and thus the WHERE needs the same transform, and thus this SQL works for the noted input:

WITH test1(id) as (
    select * 
    from values
        ('18374683274748987'::varchar)
), test2(id, date) as (
    select to_binary(hex_encode(column1)), column2::date 
    from values
        ('18374683274748987', '2022-06-20')
)
Select 
    tst2.id as bin_val, 
    tst1.id as text_val
from test1 as tst1
join test2 as tst2
    on to_binary(hex_encode(tst1.id), 'HEX') = tst2.id
where tst2.id = to_binary(hex_encode('18374683274748987'), 'HEX')
and tst2.date :: date >= '2022-06-20' :: date;

I put some other things in expecting needing to show that TRY_TO_BINARY should be used by null and 'this is not hex string' actually where handled, so I am at a loss to prove, the TRY_ versions should be used, but it also depends if you want your code to silent move on, or explode when the input is different than you expected.