Oracle SQL BETWEEN not working as expected

Question

I have a simple query which selects a few lines, has two inner joins and has a couple of where statements.

However the line WHERE g.company BETWEEN '2' AND '21' isn't working as i expect.

Current Code

SELECT
g.date_reg AS Date_Entered,
g.userid AS UserID,
x.account AS Account_Number, 
g.company AS Company_Number
FROM gtable g
INNER JOIN MSI f ON g.vt= f.vt AND g.company = f.company AND g.vn = f.vn
INNER JOIN grow x ON f.vn = x.vn AND f.cb = x.cb AND (f.dac = x.ca OR f.dad = x.da)
WHERE g.company BETWEEN '2' AND '21'
AND g.vn = '123123456'
AND g.year = '2014'

With the current code i get the following result.

Date_Entered UserID Account_Number Company_Number
01/01/2014   AB01   1112           20

if i change that line to WHERE g.company BETWEEN '1' AND '21' I get the following:

Date_Entered UserID Account_Number Company_Number
03/03/2014   CD01   1312           19
02/02/2014   BG01   1412           19
01/01/2014   AB01   1112           20

Which doesn't make sense as I have only changed the lower bounds.

Then even more strange, if i change it to WHERE g.company BETWEEN '0' AND '21'

I get the following:

Date_Entered UserID Account_Number Company_Number
03/03/2014   CD01   1312           02
02/02/2014   BG01   1412           02
03/03/2014   CD01   1312           03
02/02/2014   BG01   1412           05
03/03/2014   CD01   1312           05
02/02/2014   BG01   1412           05
03/03/2014   CD01   1312           06
02/02/2014   BG01   1412           06
03/03/2014   CD01   1312           07
02/02/2014   BG01   1412           07
03/03/2014   CD01   1312           07
02/02/2014   BG01   1412           07
03/03/2014   CD01   1312           07
02/02/2014   BG01   1412           08
03/03/2014   CD01   1312           08
02/02/2014   BG01   1412           08
03/03/2014   CD01   1312           09
02/02/2014   BG01   1412           09
03/03/2014   CD01   1312           19
02/02/2014   BG01   1412           19
01/01/2014   AB01   1112           20

What I want from the where statement is to show any company that has a number between and including 2 and 21 inclusive.

it seems datatype issue, if company datatype is not numeric type then '2' <> '02' same thing applies for other values — Koti Panga
– Koti Panga, Commented Nov 7, 2014 at 12:12
Which also means company '02' is not company #2 but the company with company code '02' (and one day there may be a company with code 'X1'). It makes no sense to apply math to this column, not even mere numeric ranges. When accessing these codes one should always name them explicitely: WHERE g.company IN ('01', '02', '03', '04'). — Thorsten Kettner
– Thorsten Kettner, Commented Nov 7, 2014 at 12:22

Multisync · Accepted Answer · 2014-11-07 12:21:15Z

2

WHERE g.company BETWEEN '0' AND '21'

I suppose that g.company is from char group (has type VARCHAR2 for example); '0' AND '21' are from char group => Oracle compares strings using there ASCII codes. In this case

'0' < '1' < '2' < ... < '9' => '2' > '1[anything else]'

If you're sure that g.company contains only numberic values you may try this

WHERE g.company BETWEEN 0 AND 21

(Oracle should implicitly convert g.company to numeric)

Or this:

WHERE to_number(g.company) BETWEEN 0 AND 21

If g.company can contain not only numeric values (and you don't need these values) then you may try this:

WHERE case when regexp_like(g.company, '^[[:digit:]]+$') then to_number(g.company) else -1 end BETWEEN 0 AND 21

edited Nov 7, 2014 at 12:21

answered Nov 7, 2014 at 12:07

Multisync

8,8371 gold badge20 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Oracle SQL BETWEEN not working as expected

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related