Geek Answers Handbook

How do I create a table

I need to create a table for working with data, which was provided to me as follows:

col1    col2        col3
1       < 3         50%
2       < 5         50%
3       < 10        50%
1       5>RC >=3    25%
2       10>RC >=5   25%
3       20>RC >=10  25%
1       >=5         0%
2       >=10        0%
3       >=20        0%

The system user will transmit the number that is present in col2and col1above. Say the user went through 7 for col2 and 1 for col1. The business requirement is that I have to return the following line to the user

1       >=5         0%

Roughly speaking, this means that I checked the value in col2and noticed that this >=5is where my input fits.

I was thinking of splitting col2into two columns - one for storing a number and the other for an operator. Something like that:

col1    col2        col3    col4
1       3           50%     <
2       5           50%     <
3       10          50%     <
1       5>RC >=3    25%
2       10>RC >=5   25%
3       20>RC >=10  25%
1       5           0%      >=
2       10          0%      >=
3       20          0%      >=

Thsi way, ( , ). , , - 4,5,6? RC , , , , .

4,5,6 2 , - :

1       3           25%     >=
1       5           25%     <
2       5           25%     >=
2       10          25%     <
3       10          25%     >=
3       20          25%     <

, . , paased col2 = 7 AND col1 = 1. , 7 , (1- , BETWEEN)

- , ?

SQLFiddle demo: http://www.sqlfiddle.com/#!4/d2d90/7

+4
1

, col2 - , , , NULL. :

+----+-------+-------+----+
|col1|col2_lb|col2_hb|col3|
+----+-------+-------+----+
|1   |NULL   |3      |50% |
+----+-------+-------+----+
|2   |NULL   |5      |50% |
+----+-------+-------+----+
|3   |NULL   |10     |50% |
+----+-------+-------+----+
|1   |3      |5      |25% |
+----+-------+-------+----+
|... |...    |...    |... |
+----+-------+-------+----+
|1   |5      |NULL   |0%  |
+----+-------+-------+----+

, :

SELECT * 
FROM T_TABLE t 
WHERE t.col1 = :VAL1 
      AND NVL(t.col2_lb,:VAL2) <= :VAL2
      AND NVL(t.col2_hb,:VAL2+1) > :VAL2
+1

More articles:


All Articles