| problem in max() function [message #441335] |
Sat, 30 January 2010 23:25  |
abhi_shakya
Messages: 50
Registered: September 2009
Location: Nepal
|
Member |
|
|
Table 1: Dealersdemand
DEMANDNO NOT NULL VARCHAR2(50)
DEMANDDATE DATE
DEMANDYEAR VARCHAR2(25)
DEMANDMONTH VARCHAR2(25)
DEMANDDAY VARCHAR2(25)
DEMANDFISCALYEAR VARCHAR2(25)
OFFICECODE VARCHAR2(50)
DEALERSCODE VARCHAR2(50)
CUSTOMERNAME VARCHAR2(400)
STATUS VARCHAR2(50)
I created a sequence to insert values in Demandno column whick is a primary key...start with 1 increment with 1;
the currval of the seq is 107..but when i run..
select max(demandno) from dealersdemand;
the result is: 97....but column has value upto 107 as in currval of the sequence....
What may be the problem here...
|
|
|
|
|
|
| Re: problem in max() function [message #441340 is a reply to message #441335] |
Sat, 30 January 2010 23:42  |
abhi_shakya
Messages: 50
Registered: September 2009
Location: Nepal
|
Member |
|
|
I insert record as
Insert into dealersdemand
values(dealersdemandseq.nextval,.....);
select dealersdemandseq.currval from dual;
O/P::107
but select max(demandno) from dealersdemand;
O/P:97
but already records with demandno 107 is inserted....
|
|
|
|
|
|
| Re: problem in max() function [message #441346 is a reply to message #441335] |
Sat, 30 January 2010 23:50 |
abhi_shakya
Messages: 50
Registered: September 2009
Location: Nepal
|
Member |
|
|
its so simple guys....why aren't u getting this??
I inserted rows in a table with sequence value inserted in the primary key column...
already PKey column has value of 107....but while selecting the max value of the PKey column....it returns 97...what happened to my value larger than 97....
|
|
|
|
|
|
| Re: problem in max() function [message #441348 is a reply to message #441335] |
Sat, 30 January 2010 23:56 |
flyboy
Messages: 1903
Registered: November 2006
|
Senior Member |
|
|
abhi_shakya wrote on Sun, 31 January 2010 06:25Table 1: Dealersdemand
DEMANDNO NOT NULL VARCHAR2(50)
<some other columns>
I created a sequence to insert values in Demandno column whick is a primary key...start with 1 increment with 1;
the currval of the seq is 107..but when i run..
select max(demandno) from dealersdemand;
the result is: 97....but column has value upto 107 as in currval of the sequence....
What may be the problem here...
I see one problem here - DEMANDNO column is supposed to store numbers, but it has VARCHAR2 data type. So its content is sorted in alphabetical order, where '100' < '97'. Wait a time until sequence generates numbers greater than 970 - then the MAX DEMANDNO will change.
The best way would be changing DEMANDNO data type to NUMBER. As a workaround, you may use TO_NUMBER( DEMANDNO ) to use other sorting rule; anyway be prepared that this column may not be containing only numbers. The treating using user defined IS_NUMBER function is described e.g. here. By the way, where should 'ABC' be placed (before or after '100')?
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
