SQL - join where a column is between a number range

Hello,

I cannot for the life of me figure out why this "where" clause is giving me troubles, I have used this logic a thousand times with no problems.

The problem is coming on my join of c to a. Specifically, it seems to happen with my "where c.result_code is greater or equal to 9.5 and c.result_code is less than or equal to 11.99

When I run this, it returns 0 rows for me, which is not correct. there are many rows that fall between 11.99 and 9.5

If I remove either the greater equal to 9.5 or the less than or equal 11.99, i get results

The only thing I can thing of is that this column is a char column? (char(5), null) and it also sometimes stores letters? other than that I have no idea


select a.product_id,

a.unit_number,

b.status_code,

c.test_code,

c.result_code


 


From product_header a


left join product_latest_status b

on a.product_id = b.product_id


inner join lab_unit_test_result c

on a.unit_number = c.unit_number and (c.test_code = 'CCPQ' and (c.result_code >= '9.5' and c.result_code <= '11.99'))

Tagged:

Best Answer

  • MarkSnodgrass
    MarkSnodgrass Portland, Oregon 🟣
    Accepted Answer

    If it is a char column, you can't do a numeric type between statement. You would need to convert your column to numeric and then either write it as

    (c.result_code >= 9.5 and c.result_code <= 11.99)
    


    or


    (c.result_code BETWEEN 9.5' and 11.99)
    


    If result_code contains non-numeric characters, you would need to create another column that only has the numeric values from result_code and use that column in your where clause

Answers

  • Jbrorby
    Jbrorby ⚪️

    Thanks, this was helpful. I did try_cast [column] as float between 9.5 and 11.99 and it is working now

    First i tried to do it as numeric but for some reason it would include 12.0-12.2 in the return

Sign In or Register to comment.

Hey, Stranger!

It looks like you're new here. Those who sign in get access to engage with even MORE fire content. To get involved, click one of these buttons!