CODEWITHSUNDEEP
sqlplsqlif-statement
user2082503
user2082503

Reputation: 109

Correct way of writing PL SQL conditions

Below is the condition for my table Request.

level of     till 300$       301-500$      501-3400$
credit card 
usage in
3 month

0%            0%               0%             0%
1-30%         30%              0%             0%
31-50%        50%              0%             0%
51-60%        50%              15%            0%
61-70%        100%             15%            0%
70%~          100%             30%            30%

My task is to retrieve all information I mentioned above in one table using PL SQL. I have table Request which consists of 3 columns as client_id, level_3m and credit_limit So the output(for example) should look like this using above information:

level_3m     credit_limit($)      new_limit(%)

0                50                 0
45               400                0
45               250                50
65               350                15
80               1500               30

What have I done so far? Here is my own script:

DECLARE
    v_level        VARCHAR2(100);
    v_credit_limit VARCHAR2(100);
    v_id           VARCHAR2(100);
    new_limit      VARCHAR2(100);
BEGIN
    SELECT level_3m,
           credit_limit
    INTO   v_level, v_credit_limit
    FROM   request a
    WHERE  v_id = a.client_id;

    --this is for "till 300$" condition
    IF v_level = 0
       AND v_credit_limit =< 300 THEN
      new_limit := 0;
    ELSIF v_level >= 1
          AND v_level <= 30
          AND v_credit_limit =< 300 THEN
      new_limit := 30;
    ELSIF v_level >= 31
          AND v_level <= 50
          AND v_credit_limit =< 300 THEN
      new_limit := 50;
    ELSIF v_level >= 51
          AND v_level <= 60
          AND v_credit_limit =< 300 THEN
      new_limit := 50;
    ELSIF v_level >= 61
          AND v_level <= 70
          AND v_credit_limit =< 300 THEN
      new_limit := 100;
    ELSIF v_level >= 70
          AND v_credit_limit =< 300 THEN
      new_limit := 100;
    END IF;
END;

/ 

--the other conditions were written same manner as the above one.

I am new to PL/SQL so please tell is my condition right? Or is there another more simple way to write those conditions?

Upvotes: 2

Views: 155

Answers (2)

LoztInSpace
LoztInSpace

Reputation: 5697

Personally I would have a table with the v_level and credit limit in it and join to that to get new_limit out.

That's the relational way, and therefore the "proper way" in this context.

Upvotes: 1

Ro Milton
Ro Milton

Reputation: 2526

You're doing If statements right.

Another option is using CASE. It's basically the same but sometimes looks a little neater, especially if you're writing out many ELSIF clauses. 

    CASE
      WHEN v_level=0  and v_credit_limit=<300 then new_limit:=0
      WHEN v_level>=1 and v_level <=30 and v_credit_limit =<300 then new_limit:=30
      WHEN v_level>=31 and v_level<=50 and v_credit_limit=<300 then new_limit:=50
      WHEN v_level>=51 and v_level<=60 and v_credit_limit=<300 then new_limit:=50
      WHEN v_level>=61 and v_level<=70 and v_credit_limit=<300 then new_limit:=100
      WHEN v_level>=70 and v_credit_limit=<300 then new_limit:=100
    END CASE

It doesn't really matter all that much whether you use IF or CASE in my opinion.

Upvotes: 2

Related Questions