title: How to run whole of the sql statement in the UDF or in the loop in Bigquery?
body: <p>Hi I am trying to iterate couple of bigquery SQL statement which basically returns the non-scaler dataset(returns about 10 columns with 200 records). The sql query looks like something like this</p>
  
  <pre><code>with aa as 
            (select * from mytable
              .....................),
  
              ab as 
  ( Select * from aa where not IN (select some_field from aa where somefield &lt;&gt; "some string"(parameter from function)) 
      JOIN bb 
      ...............)
       Select * from ab
  
  </code></pre>
  
  <p>I am trying to run the above code as function with different parameters with some loop kind of functionality and union the result of each iteration as a single table output. I have tried using the UDF function not sure how can I put that above code/sql statements that basically runs on the bigquery and use them as iteratively. In simple words i want something like this </p>
  
  <pre><code>1. Declare array for the parameters to be passed in the function on the step 2
  2. Make function of (my_sql_query_that_accepts_the_argument_from_this_function)
  3. Iterate the function in step2 number of times the items in declared Array step1
  4. Union the results every iteration and gives the output finally as Bigquery output.
  </code></pre>
  
  <p>any thoughts would be appreciated or if there is smarter way to do that.</p>
  
  <p>Here would be the closest example </p>
  
  <p>Hello sorry for not being very absolute. Here is the example</p>
  
  <pre><code>With table as
  (
  Select 1 as accnt_number, 20 as revenue, 'aaa' as product, date('2020-01-01') as date, date('2020-01-01') as start_date
  UNION ALL
  Select 2 as accnt_number, 22 as revenue, 'bbb' as product, date('2020-01-15') as date, date('2019-01-01') as start_date
  UNION ALL
  Select 3 as accnt_number, 22 as revenue, 'ccc' as product, date('2020-03-21') as date, date('2020-01-01') as start_date
  UNION ALL
  Select 2 as accnt_number, 22 as revenue, 'aaa' as product, date('2020-02-11') as date, date('2020-01-01') as start_date
  UNION ALL
  Select 2 as accnt_number, 22 as revenue, 'bbb' as product, date('2020-06-01') as date, date('2020-01-01') as start_date
  UNION ALL
  Select 2 as accnt_number, 22 as revenue, 'ddd' as product, date('2020-04-01') as date, date('2020-01-01') as start_date
  ),
  
  results as ( --  This logic doesn't make any sense just writing it down to give the feel of how it will be in main query i am trying to use.
  
  Select  first.* from table first
  join table second
  on first.accnt_number = second.accnt_number AND first.product = 'aaa' -- &lt;-this needs to be the changed everytime the loop runs with products in some kind of variable (1)
     And first.date between date_add(second.date, INTERVAL -7 day) AND date_add(second.date, INTERVAL +7 day)
  
  )
  
  Select * from results  -- Results of this needs to be iterated everytime with new product name from variable as mentioned in (1), UNIONioning ALL ultimately to give the final result for each product name in the loop.
  
  -- So basically all of the above sql query needs to be run again and again ie number of times as the length of the array holding the product names(aaa,bbb...) at (1) and then results get union all at the end of each iteration of the loop
  
  </code></pre>