How to use partition pruning in google big query without hardcoding the filter condition?

Question

I have a big table in GBQ which is partitioned by date. and I want to use partition pruning to optimize my query. the problem is that filter condition is a value that is read from another table and I can't hardcode the value. I wonder if there is any way to use partition pruning in this case and also I can't use declare statement because scheduling engine that I am working with doesn't allow that. here is my code:

WITH CTE AS
(
  SELECT tmp.partitionStartDate
  FROM tmp_table tmp
)
SELECT *      
FROM table1  t
WHERE sbcc.partitionDate = (select partitionStartDate from cte)

Answer 1

Try this:

execute immediate "SELECT * FROM table1 WHERE partitionDate = ?" using (SELECT partitionStartDate FROM tmp_table);