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'))
Best 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 toany users posts that helped you.
**Please mark as accepted the ones who solved your issue.1
Answers
-
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 toany users posts that helped you.
**Please mark as accepted the ones who solved your issue.1 -
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
1
Categories
- All Categories
- 2K Product Ideas
- 2K Ideas Exchange
- 1.6K Connect
- 1.3K Connectors
- 311 Workbench
- 6 Cloud Amplifier
- 9 Federated
- 3.8K Transform
- 657 Datasets
- 115 SQL DataFlows
- 2.2K Magic ETL
- 815 Beast Mode
- 3.3K Visualize
- 2.5K Charting
- 81 App Studio
- 45 Variables
- 775 Automate
- 190 Apps
- 481 APIs & Domo Developer
- 81 Workflows
- 23 Code Engine
- 40 AI and Machine Learning
- 20 AI Chat
- 1 AI Playground
- 1 AI Projects and Models
- 18 Jupyter Workspaces
- 410 Distribute
- 120 Domo Everywhere
- 280 Scheduled Reports
- 10 Software Integrations
- 144 Manage
- 140 Governance & Security
- 8 Domo Community Gallery
- 48 Product Releases
- 12 Domo University
- 5.4K Community Forums
- 41 Getting Started
- 31 Community Member Introductions
- 114 Community Announcements
- 4.8K Archive