Ross Goodman

Tag: DataType

  • Oracle Datatype Conversions

    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

  • Concatenate Two Fields And Get A Smaller Answer !?!?

    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.

    (more…)