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?

Answers


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.


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.


Need Your Help

Jenkins call to mount_smbfs fails with Authentication error

osx jenkins mount samba

I am running Jenkins on Mac OS X 10.7.5 and have it handle nightly backup to a SMB share on a Windows server. I want to mount the SMB drive automatically as part of the backup script but mount_smbfs

Join and count in SQL Server

sql-server

I have two tables; let's call them TableA and TableB. Each element of TableB is associated with an element of TableA in a many-to-one relationship. Some elements of TableA may have no corresponding

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.