CODEWITHSUNDEEP
sqlgoogle-cloud-platformgoogle-bigquery
CHRD
CHRD

Reputation: 1957

BigQuery SQL conditional IN

I have been trying to construct a conditional IN like below, but this gives me the scalar subquery produced more than one element error. How should I approach this type of query instead? The tables are not related.

select * from my_table
where my_table.my_col in (
  case
    when 1 = 1
    then (select my_col from my_other_table_1)
    else (select my_col from my_other_table_2) 
  end
)

Upvotes: 0

Views: 98

Answers (1)

mkRabbani
mkRabbani

Reputation: 16908

Try this below-

select * from my_table
where my_table.my_col in (
  SELECT
  case
    when 1 = 1 then my_col_1 
    else my_col_2 
  END
  from my_other_table
)

As your other tables are not related, you can try this below logic-

select * from my_table
where 
(1=1 and my_col IN (select my_col_1 from my_other_table_01)
OR
(3=3 and my_col IN (select my_col_2 from my_other_table_02)

Upvotes: 2

Related Questions