Trouble with SQL query to check for existing records in BigQuery table based on a column value

💬 Queries and Resources
,
1

Hi, I have field where users can upload csv file (fileInput1), then they can map column names from csv file to column names in database (listView2) and then they can see the final table with the content of csv file and mapped column names (tableLegacy1).

Screenshot 2023-06-19 at 16.07.25
Screenshot 2023-06-19 at 16.07.25724×696 41 KB

I want to create sql query that returns true or false deppending if the vat_number in tableLegacy1 already exists in BigQuery table marts.leads_for_retool or not.

I can't understand why my query does not work?

select case when count<>0 then true else false end as exist_in_db from (
  select count(vat_number) count from marts.leads_for_retool
  where vat_number IN UNNEST({{ tableLegacy1.vat_number }})
)

I get error:

Incorrect number of parameter types provided.

Could someone please help me understand what might be causing this error and how I can fix it?

Thank you in advance for your assistance!

2

Try {{ tableLegacy1.selectedRow.data.vat_number }}

1 Like
3

Thanks @ScottR. Yes, this works when there is only one row, but there might be more then one.
The following solved my problem:

select vat_number, case when count<>0 then true else false end as exist_in_db from (
  select vat_number, count(vat_number) over (partition by vat_number) as count from marts.leads_for_retool
  where vat_number in
  UNNEST({{formatDataAsObject(tableLegacy1.data).vat_number}})
)

Thanks for your help!

1 Like