Snowflake Data Type Conversion

Question

I am getting a conversion error as explained below:

1. Created an insert into a Table using SHA1. The filed is binary
2. When I try to query the table with this field, I get an error shown below.

Do I need to convert binary datatype to Varhcar for querying?

Cannot convert parameter ''D832F2E3518C72414C9FAC5131951F3DADCBG51A'' of type [VARCHAR(40)] into expected type [BINARY(8388608)]

insert into test
select SHA1(concat(EMPNAME,'|',ETYPE))  Field1
from "TABLE1
select * from Table1 where     Field1 ='D832F2E3518C72414C9FAC5131951F3DADCBG51A'

Answer 1

Do I need to convert binary datatype to Varhcar for querying?

Rather string literal to binary:

SELECT * FROM Table1 WHERE Field1 ='D832F2E3518C72414C9FAC5131951F3DADCBG51A'::BINARY

---

An open question is why Field1 column is BINARY when SHA1 returns VARCHAR.

SHA1

The data type of the output is string (VARCHAR) and can be stored in a VARCHAR column:

CREATE OR REPLACE TABLE table1(Field1 VARCHAR)
AS 
SELECT SHA1(concat(EMPNAME,'|',ETYPE))  Field1
FROM (SELECT 'a' AS EMPNAME, 1 AS ETYPE) s;

SELECT * FROM Table1 WHERE Field1 ='469f9400148c19c663c1aa53ca53cbf9f2526ea6';

---

My question is why SHA1 returns Binary?

It returns VARCHAR(40) as stated in documentation. But during insert there is implicit conversion to Binary as Field1 is defined as BINARY.

select SHA1(concat(EMPNAME,'|',ETYPE))  Field1
from (SELECT 'a' AS EMPNAME, 1 AS ETYPE) s;

DESCRIBE RESULT LAST_QUERY_ID();