Variable values in oracle where clause

Question

5.00/5 (1 vote)

See more:

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

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 */

What I have tried:

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 using TOAD, on variable exec it prompts me up an window?? How will avoid that?

Posted 13-Feb-18 3:38am

pradeep kumar

Updated 13-Feb-18 5:05am

Add a Solution

Comments

CHill60 13-Feb-18 10:20am

I don't understand why you are using a loop at all. Oracle databases are set-based.

pradeep kumar 13-Feb-18 10:27am

How will i do that?

2 solutions

Solution 2

I second the idea in CHill60's solution 1. Why loop when you can let the database do the work for you.

However, I'd like to add few points:

In PL/SQL you don't need to use semicolon in from of variable. That syntax is typically used when using a host variable. When you're referring to variables that are declared inside PL/SQL you can use the as-is. Consider the following

SQL

DECLARE 
   vCriteria NUMBER;
   vResult   NUMBER;
BEGIN
   vCriteria := 5;

   SELECT COUNT(*)
   INTO vResult
   FROM SomeTable
   WHERE SomeColumn <= vCriteria;

   DBMS_OUTPUT.PUT_LINE('Result ' || vResult);
END;

Another thing is the WEEK_NAME and the accompanying table. The week names look like dates stored in a string in YYYYMMDD format. If that is correct, I would suggest using oracle date instead of string.

If the fact table would contain dates then you could easily extract the week number, monhth, quarter etc from the date value. For example TO_CHAR(datecolumn, 'IW') returns the ISO week number for the given date. Date data type owuld give you a lot more flexibility and as far as I can see, it would eliminate the need for separate tables that define start of the week and so on.

Posted 13-Feb-18 5:05am

Wendelius

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CHill60 · Accepted Answer · 2018-02-13T04:39:00

I'm afraid I haven't got access to Oracle so this solution is using basic T-SQL syntax.

All you have to do is join the two tables based on the week_name information and then group by the account and week_name ... this works

SQL

select A.ACCTID, B.WEEK_NAME, SUM(SALES)
from Fact A
inner join DATE_DIM B ON A.WEEK_NAME >= B.WEEK_BEGIN AND A.WEEK_NAME <= B.WEEK_END
group by A.ACCTID, B.WEEK_NAME

It's a rare occurrence that you would need to use a loop in an RDBMS - I wrote an article giving alternatives Processing Loops in SQL Server[^] - although it targets SQL Server the principles also apply to Oracle (and MySQL and any other RDBMS that uses T-SQL).

As another aside, there are functions that give week numbers (e.g. see Ask Tom - week of the year[^] so you could just store the date on the Fact table (as a Date type) and calculate the week number from that to avoid having to have the DATE_DIM table