Hi,
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,
AdrNr,
AnsNr,
Tel)
values (
CAST({{value.ID}} AS INTEGER),
CAST({{value.AdrNr}} AS INTEGER),
CAST({{value.AnsNr}} AS INTEGER),
IFNULL({{value.Tel}}, '')
);
else
update tablename set
ID=CAST({{value.ID}} AS INTEGER),
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?
Thanks