Home » SQL & PL/SQL » SQL & PL/SQL » Using mod function on varchar2 field
Using mod function on varchar2 field [message #210090] Tue, 19 December 2006 06:08 Go to next message
attraxion
Messages: 14
Registered: October 2006
Junior Member
Hi, I'm a newbie.

I need to extract records from a very huge table (more than 100 million records) so I am doing it using the mod function on a field -

SELECT * FROM CUSTOMERS
WHERE MOD(CUSTOMER_ID,10)=&1;

where &1 is the argument which I pass while calling this SQL from shell script (naturally I will pass 0 to 9). The trouble is, customer_id is a varchar2 field and this syntax is giving me the error "INVALID NUMBER". I checked the values of customer_id in the table, there are all numerical values, no nulls or alphabets.

I tried MOD(TO_NUMBER(CUSTOMER_ID),10)=&1
and even MOD(TO_NUMBER(CUSTOMER_ID),10)=TO_NUMBER(&1)

but none of them is working, giving the same error. Please provide some pointers for the correct syntax. I know keeping numeric values in a varchar field is not good, but that wasn't my choice. Embarassed

Thanks and regards,
Rahul.
Re: Using mod function on varchar2 field [message #210094 is a reply to message #210090] Tue, 19 December 2006 06:21 Go to previous messageGo to next message
amul
Messages: 252
Registered: April 2001
Location: Chennai
Senior Member
please post the error code..

SELECT * FROM CUSTOMERS
WHERE MOD(CUSTOMER_ID,10)

till this its ok..so please poest the error message too
Re: Using mod function on varchar2 field [message #210095 is a reply to message #210090] Tue, 19 December 2006 06:29 Go to previous messageGo to next message
attraxion
Messages: 14
Registered: October 2006
Junior Member
SQL> select * from customers
2 where mod(customer_id, 10) = 0;
where mod(customer_id, 10) = 0
*
ERROR at line 2:
ORA-01722: invalid number

There you are.
Re: Using mod function on varchar2 field [message #210096 is a reply to message #210090] Tue, 19 December 2006 06:31 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
There's no inherent problem with your approach:
SQL> create table temp_cust (cust_id  varchar2(10));

Table created.

SQL> insert into temp_cust (select level from dual connect by level <= 100);

100 rows created.

SQL> select count(*) from (
  2  select cust_id, mod(cust_id,3) from temp_cust);

  COUNT(*)
----------
       100

I suspect that it's your data. Try running this query:
SELECT count(*) from (SELECT TO_NUMBER(CUSTOMER_ID) FROM CUSTOMERS);
and tell us what you get.
Re: Using mod function on varchar2 field [message #210103 is a reply to message #210095] Tue, 19 December 2006 06:42 Go to previous messageGo to next message
amul
Messages: 252
Registered: April 2001
Location: Chennai
Senior Member
create table cust(numb varchar2(10)
/
table created.

insert into cust values(222)
/
1 row created

commit
/

select * from cust where mod(numb,10)=&2
enter value for &2=2

numb
----
222



i dont have any problems here
Re: Using mod function on varchar2 field [message #210104 is a reply to message #210090] Tue, 19 December 2006 06:45 Go to previous messageGo to next message
attraxion
Messages: 14
Registered: October 2006
Junior Member
I get a correct count of the number of records.
Re: Using mod function on varchar2 field [message #210105 is a reply to message #210095] Tue, 19 December 2006 06:48 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
This might help you to figure out where exactly the error is in the data.
isnumeric
By
Vamsi

[Updated on: Tue, 19 December 2006 06:48]

Report message to a moderator

Re: Using mod function on varchar2 field [message #210108 is a reply to message #210104] Tue, 19 December 2006 07:07 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
And this query returns what:
SELECT count(*) from (SELECT MOD(TO_NUMBER(CUSTOMER_ID),5) FROM CUSTOMERS);
Previous Topic: cannot find record
Next Topic: How to insert an array from an xml into a query?
Goto Forum:
  


Current Time: Wed Dec 07 18:48:46 CST 2016

Total time taken to generate the page: 0.06670 seconds