Last week I gave a colleague a demonstration on why, if you have numbers stored in a character string you should convert them explicitly to numbers:
select ‘Yes’
from dual
where ‘1000’ between ‘000’ and ‘999’;‘YES’
—–
Yes
Tag: DataType
Had a “strange” problem last week that really stumped me for longer than it should have.
My colleague had run a query to list some data and was getting approximately 200,000 rows back. We were investigating why a unique constraint was being violated so I took his query and did a count distinct to try and find the duplicates. I got 30,000 distinct values.
This really confused me, I was expecting only a handfull of duplicates, if any. Then I noticed that the first query already WAS a distinct list !
select distinct col1_id, col2_id from tablea
select count(distinct col1_id + ‘-‘ + col2_id) from tablea
I concatenated the fields together so I am counting a single field, I included the separator so that I could differentiate 1-11 from 11-1.
See if you can spot what the problem is before you read on.