Variable values in Oracle where Clause

I am trying to pass an variable value in my Select statement where clause

Declare v_WK varchar(20);
exec :v_WK :='Wk 6'

Select * from myTable Where Column = :v_Wk

In toad it pop up the filtering window(How do I avoid that)

DATE_DIM table is like below

UNIQUE_ID   WEEK_NAME   WEEK_BEGIN  WEEK_END
1   Week 6, 2018    20180205    20180211
2   Week 5, 2018    20180129    20180204
3   Week 4, 2018    20180122    20180128
4   Week 3, 2018    20180115    20180121

And Fact Table is like

ACCT_ID WEEK_NAME   SALES
10001   20180205    10
10001   20180206    20
10001   20180207    10
10001   20180208    15
1002    20180129    100
1002    20180130    100
1002    20180131    100
1002    20180132    100

Here I need an Output like

ACCT_ID WEEK_NAME   SALES
10001   Week 6, 2018    55
1002    Week 5, 2018    406

So Basically I wanna loop each ACCT_ID like

SELECT *
FROM Fact_Table
where Week_Name between (Select Week_Begin from DATE_DIM WHERE WEEk_NAME =: v_Wknm) /* Here I will have to loop Each Week_name for Each Acct_ID */

This is what I have tried

DECLARE v_Init_Cnt NUMBER(10):=1;
        v_Tot_Cnt NUMBER(10);

BEGIN

    SELECT COUNT(*) INTO v_Tot_Cnt FROM DIM_DATE;

    WHILE(v_Init_Cnt <= v_Tot_Cnt)
    LOOP

    dbms_output.put_line(v_Init_Cnt);

    VARIABLE v_Id NUMBER(10);
    exec :v_Id:= v_Init_Cnt;

    dbms_output.put_line('var' || v_Id);

    SELECT * FROM FACT_TABLE 
    WHERE WEEK_NAME between (select WEEk_BEGIN FROM DIM_DATE WHERE UNIQUE_ID :=v_Init_Cnt) and (select WEEk_END FROM DIM_DATE WHERE UNIQUE_ID :=v_Init_Cnt) 

    v_Init_Cnt :=  v_Init_Cnt +1;
    END LOOP;

END;  

I am not bit handy in PL/SQL like in MSSQL, sorry if this is simple question

1 answer

  • answered 2018-02-13 02:30 Kaushik Nayak

    You could use the VARIABLE command to define bind variables. Also note that you should use VARCHAR2. In Toad, you may have to do "Execute as script" or run assqlplus for this to work.

    VARIABLE v_WK VARCHAR2(20)
    exec :v_WK :='Wk 6'
    
    Select * from myTable Where Column_name = :v_Wk;
    

    Regarding your code block, we don't use a TABLE variable in Oracle as in TSQL. Also, you can't run the above select statement and blocks in PL/SQL scope( i.e within BEGIN..END ). It would be better if you tell us what you are ultimately trying to accomplish, so that an efficient solution could be provided.

    EDIT : You don't need plsql to get your output. Use a simple join query like this.

    SELECT a.acct_id,
           b.week_name,
           COUNT(*)
    FROM   fact a
           JOIN date_dim b
             ON a.week_name BETWEEN b.week_begin AND b.week_end
    GROUP  BY a.acct_id,
              b.week_name;