OraFAQ Forum: SQL & PL/SQL » I want to insert only integer values in table (merged)

Home » SQL & PL/SQL » SQL & PL/SQL » I want to insert only integer values in table (merged)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

I want to insert only integer values in table (merged) [message #274562]

Tue, 16 October 2007 08:09

victoryhendry
Messages: 96
Registered: January 2007
Location: Bangalore

Member

I want to insert only integer values in table. When I am trying to insert using datatype number,integer
It was taking float value and the value got round. The thing is if I will try to insert any float value it would tell the message float value wont accept please insert only integer values.

Create table test (test_num number (10));
Insert into test values(0.7);
Insert into test values(1.2);
Insert into test values(1.6);

Select *from test ;
Test_num
0.7
1.2
1.6

If I was use integer
Create table test (test_num integer (10));

Insert into test values(0.7);
Insert into test values(1.2);
Insert into test values(1.6);

Select *from test ;
Test_num
1
1
2

But I don’t want round values … I want if some one try to insert decimal values.. it will not accept..
Can you please help me using SQL statement? Which datatype I need to be use?

with regards,
Vetrivel k

Report message to a moderator

Re: I want to insert only integer values in table [message #274566 is a reply to message #274562]

Tue, 16 October 2007 08:15

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

1/ integer(10) is not a valid datatype, you choose either integer either number(10,0)

SQL> Create table test (test_num integer (10));
Create table test (test_num integer (10))
                                    *
ERROR at line 1:
ORA-00907: missing right parenthesis

SQL> Create table test (test_num number(10,0));

Table created.

2/ This is the purpose of check constraint

SQL> Create table test (test_num number check (test_num=trunc(test_num)));

Table created.

SQL> Insert into test values(0.7);
Insert into test values(0.7)
*
ERROR at line 1:
ORA-02290: check constraint (MICHEL.SYS_C004798) violated

Regards
Michel

Report message to a moderator

Re: I want to insert only integer values in table [message #274569 is a reply to message #274562]

Tue, 16 October 2007 08:19

MarcL
Messages: 455
Registered: November 2006
Location: Connecticut, USA

Senior Member

You could use the MOD function, and output an error message to the calling application if appropriate

Report message to a moderator

Re: I want to insert only integer values in table [message #274570 is a reply to message #274562]

Tue, 16 October 2007 08:20

Maaher
Messages: 7065
Registered: December 2001

Senior Member

You could do that with a check constraint:

SQL> CREATE TABLE yourtable (test_num NUMBER )
  2  /

Table created.

SQL>
SQL> ALTER TABLE yourtable
  2    ADD CONSTRAINT mhe_test_num
  3    CHECK (test_num = ROUND(test_num))
  4  /

Table altered.

SQL>
SQL> INSERT INTO yourtable VALUES(0.7);
INSERT INTO yourtable VALUES(0.7)
*
ERROR at line 1:
ORA-02290: check constraint (MHE.MHE_TEST_NUM) violated


SQL> INSERT INTO yourtable VALUES(1.2);
INSERT INTO yourtable VALUES(1.2)
*
ERROR at line 1:
ORA-02290: check constraint (MHE.MHE_TEST_NUM) violated


SQL> INSERT INTO yourtable VALUES(1.6);
INSERT INTO yourtable VALUES(1.6)
*
ERROR at line 1:
ORA-02290: check constraint (MHE.MHE_TEST_NUM) violated


SQL> INSERT INTO yourtable VALUES(1);

1 row created.

SQL>
SQL> SELECT * FROM yourtable
  2  /

  TEST_NUM
----------
         1

SQL>
SQL> DROP TABLE yourtable PURGE
  2  /

Table dropped.

MHE

Edit: although it's nice to see that other people agree, I wish I was a bit faster in my replies Sad

[Updated on: Tue, 16 October 2007 08:21]

Report message to a moderator

Re: I want to insert only integer values in table [message #274578 is a reply to message #274570]

Tue, 16 October 2007 08:33

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

You took the time to give a name to the constraint, I was too lazy for that. Wink

Regards
Michel

Report message to a moderator

Re: I want to insert only integer values in table [message #274652 is a reply to message #274578]

Tue, 16 October 2007 13:03

joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA

Senior Member

Michel Cadot wrote on Tue, 16 October 2007 09:33

You took the time to give a name to the constraint, I was too lazy for that. Wink

Hmmm, ok then, based on the way I interpret that, lazy=fast Wink

Report message to a moderator

Re: I want to insert only integer values in table [message #276864 is a reply to message #274570]

Fri, 26 October 2007 09:15

victoryhendry
Messages: 96
Registered: January 2007
Location: Bangalore

Member

Very..thanks for your answer...
..I have..one more ..probs..below..

I want to insert only integer values...Below Example it will work only when we creating newtable...it does not work if the data are exist in the table?
if data are exist in the table how i will use ?

SQL> CREATE TABLE yourtable (test_num NUMBER )
2 /

Table created.

SQL>
SQL> ALTER TABLE yourtable
2 ADD CONSTRAINT mhe_test_num
3 CHECK (test_num = ROUND(test_num))
4 /

Table altered.

SQL>
SQL> INSERT INTO yourtable VALUES(0.7);
INSERT INTO yourtable VALUES(0.7)
*
ERROR at line 1:
ORA-02290: check constraint (MHE.MHE_TEST_NUM) violated

SQL> INSERT INTO yourtable VALUES(1.2);
INSERT INTO yourtable VALUES(1.2)
*
ERROR at line 1:
ORA-02290: check constraint (MHE.MHE_TEST_NUM) violated

Report message to a moderator

Can you please help me..if the data exist(float values) in the table [message #276866 is a reply to message #274562]

Fri, 26 October 2007 09:21

victoryhendry
Messages: 96
Registered: January 2007
Location: Bangalore

Member

Can you please help me..if the data exist(float values) in the table..
like..float values..

select test_num from yourtable;

test_num
--------
0.3
0.4
1
2
3

how we will avoid float values?...next insertion?
that mean already float values is there not an issue.. we have to avoid...in future.how?

Report message to a moderator