Reputation: 1

Error on an IF function format SQL: Error Not Supported BOOL, STRING, BOOL, only IF(BOOL, ANY, ANY)

I'm trying to write an IF statement in SQL, and it's throwing an error about the supported format

SQL_ANALYSIS_ERROR: No matching signature for function IF for argument types: BOOL, BOOL, STRING. Supported signature: IF(BOOL, ANY, ANY)

Code: IF(field1 = 'string_1', field2 , 'string_2') AS field_name

This should meet the requirement of BOOL, ANY, ANY....but for whatever reason it's not. Anyone have suggestions? Or am I missing something?

Upvotes: 0

Answers (2)

Reputation: 1271151

The SQL standard for conditional logic is CASE:

(CASE WHEN field1 = 'string_1' THEN field2 ELSE 'string_2' END) AS field_name

I recommend this over bespoke functions such as if.

In this case, the problem is that field2 is BOOLEAN, so it needs to be converted. BigQuery requires explicit conversion in most cases, so:

(CASE WHEN field1 = 'string_1' THEN CAST(field2 AS STRING) ELSE 'string_2' END) AS field_name

Upvotes: 0

Reputation: 131

I think with you confuse. the function is IIF.

EX: SELECT IIF('string_1' = 'string_1', 'OK' , 'string_2')

Upvotes: 1