CODEWITHSUNDEEP
sqloracle-databaseoracle11g
Incognito
Incognito

Reputation: 3094

Not able to figure out the query

I have created a table to cater to some customer requirements, and have to write a query on the data to be stored in the table. I am not able to figure out an optimal way to write my query.

My table is structured like:

c1   c2_lower_limit   c2_upper_limit   c3_lower_limit   c3_upper_limit   rate   operator
1    0                10000            0                20000            10     AND
1    10000            50000            20000            30000            20     OR
1    50000            NULL             30000            NULL             30     OR 
2    0                10000            0                20000            10     AND
2    10000            50000            20000            30000            20     OR
2    50000            NULL             30000            NULL             30     OR

The user specifies a value of C1 and then values for C2 and C3 (simply one value for c2 and one for c3). Let's say that I have to return the rate column as an output. The last column in the table structure is OPERATOR, which tells me whether the C2 and C3 values should be AND or ORed.

I am facing problem in determining the opearator to be used. A user can select any value at her discretion for lower and upper limits. I tried basing my operator selection only on one group - let's say C2, but then that is not right, as I need to evaluate all variabels input by the user to get the correct operator.

Let's say that a user enters a value of 0 for c2 and enters a value of 25000 for c3 for c1 = 1. Since the values of c3 correspond to the entry with opeartor value of OR, I should OR the c2 and c3 limit values. In short, if a record with OR operator is found, I have to OR the c2 and c3 groups.

You can check SQLFiddle for table structure http://www.sqlfiddle.com/#!4/f9bf6/11

Upvotes: 0

Views: 51

Answers (1)

Mureinik
Mureinik

Reputation: 311143

A quick and dirty solution could be to add a condition on the operator field.

Using the : notation to signify passed parameters:

SELECT *
FROM   my_table
WHERE  c1 = :c1 
AND    ((operator = 'AND' 
         AND
         (:c2 BETWEEN c2_lower_limit AND c2_upper_limit OR
          (:c2 >= c2_lower_limit AND c2_upper_limit IS NULL) OR
          (c2_lower_limit IS NULL AND :c2 < c2_upper_limit) 
         AND
         (:c3 BETWEEN c3_lower_limit AND c3_upper_limit OR
          (:c3 >= c3_lower_limit AND c3_upper_limit IS NULL) OR
          (c3_lower_limit IS NULL AND :c3 < c3_upper_limit) 
        )
       OR
        (operator = 'OR' 
         AND
         (:c2 BETWEEN c2_lower_limit AND c2_upper_limit OR
          (:c2 >= c2_lower_limit AND c2_upper_limit IS NULL) OR
          (c2_lower_limit IS NULL AND :c2 < c2_upper_limit) 
         OR
         (:c3 BETWEEN c3_lower_limit AND c3_upper_limit OR
          (:c3 >= c3_lower_limit AND c3_upper_limit IS NULL) OR
          (c3_lower_limit IS NULL AND :c3 < c3_upper_limit) 
        )
       )

EDIT:
Note that there's a lot of code duplicity handling nulls with a between operation. If you can alter the database, I'd add a helper function that does this:

CREATE OR REPLACE FUNCTION null_safe_between 
(eval NUMBER, lower NUMBER, upper NUMBER) 
RETURN BOOLEAN
DETERMINISTIC
BEGIN
    RETURN eval BETWEEN lower_limit AND upper_limit OR
           (eval >= lower_limit AND upper_limit IS NULL) OR
           (lower_limit IS NULL AND eval < upper_limit);
END;

Upvotes: 1

Related Questions