Home » SQL & PL/SQL » SQL & PL/SQL » I want to insert only integer values in table (merged)
I want to insert only integer values in table (merged) [message #274562] Tue, 16 October 2007 08:09 Go to next message
victoryhendry
Messages: 95
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
Re: I want to insert only integer values in table [message #274566 is a reply to message #274562] Tue, 16 October 2007 08:15 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
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


Re: I want to insert only integer values in table [message #274569 is a reply to message #274562] Tue, 16 October 2007 08:19 Go to previous messageGo to next message
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
Re: I want to insert only integer values in table [message #274570 is a reply to message #274562] Tue, 16 October 2007 08:20 Go to previous messageGo to next message
Maaher
Messages: 7062
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 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You took the time to give a name to the constraint, I was too lazy for that. Wink

Regards
Michel
Re: I want to insert only integer values in table [message #274652 is a reply to message #274578] Tue, 16 October 2007 13:03 Go to previous messageGo to next message
joy_division
Messages: 4640
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
Re: I want to insert only integer values in table [message #276864 is a reply to message #274570] Fri, 26 October 2007 09:15 Go to previous messageGo to next message
victoryhendry
Messages: 95
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
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 Go to previous messageGo to next message
victoryhendry
Messages: 95
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?
Re: Can you please help me..if the data exist(float values) in the table [message #276867 is a reply to message #276866] Fri, 26 October 2007 09:28 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
look at ENABLE NOVALIDATE in the documentation for constraints
Re: Can you please help me..if the data exist(float values) in the table [message #276869 is a reply to message #276866] Fri, 26 October 2007 09:32 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Now that I merged the 2 topics, you can merge the 2 answers. Wink

Regards
Michel
Previous Topic: column name length Alias
Next Topic: Getting the SCN as a variable
Goto Forum:
  


Current Time: Sat Dec 03 01:02:20 CST 2016

Total time taken to generate the page: 0.04964 seconds