CODEWITHSUNDEEP
sqlstored-proceduresgoogle-bigquery
Pete
Pete

Reputation: 289

Dynamic filter condition in BigQuery procedure

There is a procedure that gets the required data and then insert into table:
Is it possible to have a parameter that leads to certain filter condition, how can I achieve that?
For example, creating a new parameter called condition that if user type 1, the procedure will use filter 1 etc.
please see the code example below:

CREATE OR REPLACE PROCEDURE `peterwu.UDP_mem_profile_package`
                            (table_name STRING
                             , title_name STRING
                             , status_start_dt DATE
                             , status_end_dt DATE)
BEGIN
       -- 2. insert data
       EXECUTE IMMEDIATE format("""
              INSERT INTO `peterwu..UDP_OUTPUT_mem_profile_aggregated`
              (TITLE, GROUP_ORDER, GROUP_ID1, GROUP_NAME, MEM_CNT, TKT, SALES)
              SELECT @title_name
                     , 5
                     , COUNT(DISTINCT MEM_ID) MEM_CNT
                     , COUNT(DISTINCT CASE WHEN IS_TICKET = 1 THEN TKT_NO END) AS TICKET
                     , SUM(retail_price_amt) SALES
              FROM `peterwu.tlw_txn_since_2017`
              WHERE TRANS_DT BETWEEN @start_dt AND @end_dt
              AND MEM_ID IN (SELECT MEM_ID FROM `"""||table_name||"""`)

              /*========== FILTER CONDITION CONTROLED BY USER INPUT: FILTER 1 OR FILTER 2=========*/
              -- FILTER 1 --
              AND 
              ( 
                  SITE_ID BETWEEN '1A01' AND '1A02' OR
                  SITE_ID BETWEEN '1A03' AND '1A04'
              )
              AND
              -- FILTER 2 --
              ( 
                  SITE_ID BETWEEN '1S01' AND '1S02' OR
                  SITE_ID BETWEEN '1S03' AND '1S04'
              )
       """)
       USING status_start_dt as start_dt
             , status_end_dt as end_dt
             , title_name as title_name;       
END

Upvotes: 0

Views: 481

Answers (1)

Bihag Kashikar
Bihag Kashikar

Reputation: 1326

here's my sample code which is trying to do what you want.

basically to answer you question, it is possible to use conditional expression evaluated in the where clause. here I have assumed the input condition are two and can be assigned to two input variables and based on which where clause can be evaluated.

CREATE OR REPLACE PROCEDURE myproject.zz_DataSet.procTest
                            ( title_name STRING
                             , condition1 string
                             , condition2 string)
BEGIN

  EXECUTE IMMEDIATE format ("""
    INSERT INTO myproject.zz_DataSet.zzProcTest (TITLE_NAME)
    SELECT firstname
    FROM myproject.zz_DataSet.tblOne
    WHERE "condition1" = if(@c1 is null,@c2 ,@c1)
    """
  )
  USING condition1 as c1,
        condition2 as c2;
END;

here's my call to procedure. in this case condition 1 is evaluated to true and hence inserts condition1 in table tblOne. hope this helps

call myproject.zz_DataSet.procTest("condition1","condition1","")

Upvotes: 3

Related Questions