0
Select count(*) from table where loc between 300 to 400.
  1. loc is a varchar column.
  2. it is not selecting all the data
  3. checking the count, gives ORA :01722 error
  4. exporting the results with error.

Edit from comment:

loc contains values less than 300, more than 400, and alphanumeric like 'GT' , '3KT1'

Nick.Mc
  • 18,304
  • 6
  • 61
  • 91
sahiba thakral
  • 1
  • 3
  • Hello Sahiba, you should use use column of `number/integer` type to store numbers and not `varchar` or `varchar2` as it is bound to create problems. Also, do you know that `varchar2` column of your table has any non-numeric characters in any of the rows? Do check that and try to resolve it before running/finding issue with this query. – Kaushik Nayak Jul 03 '18 at 05:43
  • Hi Kaushik, The Loc column has char type value also like GJ, 3KT1 etc .Hence Varchar2 as column type – sahiba thakral Jul 03 '18 at 05:59
  • So what values are you expecting to be excluded by your query? Should `'40'` be in or out? How about `'3000'`? – APC Jul 03 '18 at 06:18
  • Values less than 300, more than 400, and the alphanumeric loc like 'GT' , '3KT1' – sahiba thakral Jul 03 '18 at 08:37

2 Answers2

0

Your current query is trying to compare a varchar to a number. So it tries to convert the varchar to a number on the fly. This is called implicit conversion.

You should make it compare a varchar to a varchar.

Use single quotes so that you are comparing to varchars, not numbers

Select count(*) from table where loc between '300' to '400'

Then go and read about implicit conversion

Based on the update to your question, this column is a legitimate varchar and should not be converted to a numeric data type.

However you do need to work out whether you are incorrectly storing different types of data in the same column

Nick.Mc
  • 18,304
  • 6
  • 61
  • 91
0

loc is a varchar column.
[From comment] The Loc column has char type value also like GJ, 3KT1

LOC contains values which are not convertible to numbers. This matters because your WHERE clause predicates are defined as numbers, so Oracle applies an implicit to_number(loc) to the query. This is why using proper data types is best practice: it doesn't help you now but please learn the lesson, and use NUMBER columns for numeric data.

In the meantime you have several options to deal with your shonky data model.

If you're lucky enough to be using Oracle 12c R2 you can use the new VALIDATE_CONVERSION() function to exclude values of loc which can't be cast to numbers. Find out more

If you're using an earlier version of Oracle you can build your own function:

create or replace function is_number
    (p_str in varchar2) return number 
is 
    n number;
    rv number;
begin 
    begin
        n := to_number(p_str);
        rv := 1;
    exception
        when invalid_number then
             rv := 0;
    end;
    return rv;
end;

The weakest option would be casting the predicates to strings. where loc between '300' to '400' would include '3000', '4' and various other values you probably don't want.

Here is a LiveSQL demo (free Oracle Technet account required, alas).

APC
  • 144,005
  • 19
  • 170
  • 281