Handling null values in parameters in Workflow using Big Query


in a workflow I want to insert/update data from a JSON object into a Big Query table using SQL mode in a loop. It worked, but when adding more fields from the JSON object to the insert/update query, I found that a few fields can contain null values and this can vary from record to record. I wanted to solve this problem by checking the fields with the IFNULL() function.
For example,

Tel = IFNULL({{value.Tel}}, '')

I got the following error message

Error: Parameter type must be provided for null values. For example, "set first_name = {{ {RT$BQ_TYPE: 'STRING', value: null} }}".

Looking for this error here in this forum I found the following topic

But I'm not sure how to use {{ {RT$BQ_TYPE: 'STRING', value: null} }} in the insert/update query for Big Query.

Here is an example of the query with 'Tel', one of the fields that could contain a null value.

if not exists (select ID from tablename where AdrNr=CAST({{value.AdrNr}} AS INTEGER) AND AnsNr=CAST({{value.AnsNr}} AS INTEGER)) then
insert into tablename (ID,
values (
CAST({{value.ID}} AS INTEGER), 
CAST({{value.AdrNr}} AS INTEGER), 
CAST({{value.AnsNr}} AS INTEGER),
IFNULL({{value.Tel}}, '') 
update tablename set
AdrNr=CAST({{value.AdrNr}} AS INTEGER),
AnsNr=CAST({{value.AnsNr}} AS INTEGER),
Tel=IFNULL({{value.Tel}}, '')
where AdrNr={{value.AdrNr}} AND AnsNr={{value.AnsNr}};
end if

Any idea how this could work?


{{ {RT$BQ_TYPE: 'STRING', value: value.Tel} }} Something like this may work

Tel=IFNULL({{ {RT$BQ_TYPE: 'STRING', value: value.Tel} }}, '') You can use like this.

Have you tried this?

When I try

Tel=IFNULL({{ {RT$BQ_TYPE: 'STRING', value: value.Tel} }}, '')

I get the following error message:

Error: Query error: No matching signature for operator = for argument types: INT64, STRING. Supported signature: ANY = ANY at [18:7]

Hey @ConDev!

What is the type of the Tel column in your DB? I'm curious if you might want to specify 'INT64' as the RT$BQ_TYPE with something like

Tel=IFNULL({{ {RT$BQ_TYPE: 'INT64', value: value.Tel} }}, 0)

Could that work for your use case?