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