0

Is there a way to have SQL scripts within a subquery ?

For example:

select col_1, col_2, count(*) from (
Declare max_radius INT64;
set max_radius = 250;

select * from my_table
where radius <  max_radius
)
group by col_1,col_2

In my case it is not possible to move the variable declaration outside of the subquery. Thanks !

Yairh
  • 63
  • 1
  • 5

1 Answers1

1

For this particular example, you could use a CTE to define the parameter values:

with params as (
      select 250 as max_radius
     )
select t.col_1, t.col_2, count(*)
from params p cross join
     my_table t
where t.radius < p.max_radius
group by t.col_1, t.col_2;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Yes, this was my first though but if my_table is huge (and in reality it is) the cross join will have a very poor performance because it will duplicate the params for each row in mytable. And I wanted to workaround that problem by using a variable. – Yairh May 03 '21 at 06:51
  • @Yairh . . . The cross join should be trivial because there is only one row. – Gordon Linoff May 03 '21 at 12:33