google bigquery - SQL query behavior based on input parameter and using one query only - Stack Overflow

admin管理员组

文章数量:1122832

Say I have a biq query table with 3 columns customer_id, name and type.

Also, say in my query, the where clause will always have customer_id for sure. But in some cases, I want to fetch all the rows for a customer_id no matter what the type but in some cases, I want to fetch only the rows where type in (<>) is satisfied.

I can write 2 separate queries where in one of them I do have both the customer_id and type in (<>) and a second query with only customer_id.

Original ask

How do I write this in one single query say if I pass '' as type then the clause for type always evaluates to true I cannot say (and type in (<>) or true) because this will always evaluate to true. I want type to be ignored if I do not wish to filter on type

Corrected ask

How do I write this in one single query that type column is ignored in where clause at times when I want to ignore it, or it must fetch rows along with the expected values I pass for type clause. For example in some instances I do not want type clause to impact the result of query and in another instance, I may have values v1,v2 for type and I want to fetch rows by filtering on those types as well. The logic should be generic so that I can extend it to more columns

Say I have a biq query table with 3 columns customer_id, name and type.

Also, say in my query, the where clause will always have customer_id for sure. But in some cases, I want to fetch all the rows for a customer_id no matter what the type but in some cases, I want to fetch only the rows where type in (<>) is satisfied.

I can write 2 separate queries where in one of them I do have both the customer_id and type in (<>) and a second query with only customer_id.

Original ask

How do I write this in one single query say if I pass '' as type then the clause for type always evaluates to true I cannot say (and type in (<>) or true) because this will always evaluate to true. I want type to be ignored if I do not wish to filter on type

Corrected ask

How do I write this in one single query that type column is ignored in where clause at times when I want to ignore it, or it must fetch rows along with the expected values I pass for type clause. For example in some instances I do not want type clause to impact the result of query and in another instance, I may have values v1,v2 for type and I want to fetch rows by filtering on those types as well. The logic should be generic so that I can extend it to more columns

• sql
• google-bigquery

Share Improve this question Follow edited Nov 23, 2024 at 16:53 curiousengineer asked Nov 23, 2024 at 0:59 curiousengineercuriousengineer 2,60399 gold badges4747 silver badges6767 bronze badges 2

• can you elaborate on "if I pass '' as type" do you mean to say pass inside your table? why wouldn't you then just put type condition as type in (<>, '')? – Georgina Skibinski Commented Nov 23, 2024 at 12:43
• I elaborated and fixed my question. Please help now – curiousengineer Commented Nov 23, 2024 at 16:52

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 1

If I understand correctly , you want to only apply type filter if a valid value for type is provided. If type is an empty string , the type condition should be ignored, but the customer_id condition should still be applied.

Let me know if the my understanding is not correct.

you can try something like this :

 SELECT customer_id, name, type
FROM your_table
WHERE customer_id = @customer_id
  AND (@type = '' OR type IN (@type));

For example :

If you pass @type = '' (empty string): The query will return all rows for customer_id = 123, regardless of the type.

SELECT customer_id, name, type
FROM customers
WHERE customer_id = 123
  AND ('' = '' OR type IN (''));  -- type filter is ignored, returns all rows for customer_id 123

If you pass @type = 'A': The query will return rows where customer_id = 123 and type is A.

SELECT customer_id, name, type
FROM customers
WHERE customer_id = 123
  AND ('A' = '' OR type IN ('A'));  -- only returns rows for customer_id 123 with type 'A'

If you pass @type = 'A', 'B': The query will return rows where customer_id = 123 and type is either A or B.

SELECT customer_id, name, type
FROM customers
WHERE customer_id = 123
  AND ('A,B' = '' OR type IN ('A', 'B'));  -- returns rows for customer_id 123 with type 'A' or 'B'

本文标签： google bigquerySQL query behavior based on input parameter and using one query onlyStack Overflow