SQL - join where a column is between a number range

Jbrorby
Jbrorby Member
edited March 2023 in SQL DataFlows

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
    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

    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.

Answers

  • MarkSnodgrass
    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

    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.
  • 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