Home » SQL & PL/SQL » SQL & PL/SQL » problem in max() function (Oracle 10g release 2)
problem in max() function [message #441335] Sat, 30 January 2010 23:25 Go to next message
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 #441336 is a reply to message #441335] Sat, 30 January 2010 23:31 Go to previous messageGo to next message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
show us what you tried and what you got.
Please follow forum guide lines.

copy your sql * plus command output and paste here.

sriram Smile
Re: problem in max() function [message #441340 is a reply to message #441335] Sat, 30 January 2010 23:42 Go to previous messageGo to next message
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 #441344 is a reply to message #441340] Sat, 30 January 2010 23:47 Go to previous messageGo to next message
BlackSwan
Messages: 24912
Registered: January 2009
Senior Member
It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/

I am sorry to see that CUT & PASTE are broken for you.

After you get CUT & PASTE fixed, please posted an acceptable response back here.

[Updated on: Sat, 30 January 2010 23:47]

Report message to a moderator

Re: problem in max() function [message #441346 is a reply to message #441335] Sat, 30 January 2010 23:50 Go to previous messageGo to next message
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 #441347 is a reply to message #441346] Sat, 30 January 2010 23:55 Go to previous messageGo to next message
BlackSwan
Messages: 24912
Registered: January 2009
Senior Member
>its so simple guys....why aren't u getting this??
Guy, it is simple.
It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/

Are you incapable or unwilling to comply with Posting Guidelines?

>what happened to my value larger than 97...
They were in cache when the DB went down & ended up in the bit bucket.
Re: problem in max() function [message #441348 is a reply to message #441335] Sat, 30 January 2010 23:56 Go to previous message
flyboy
Messages: 1831
Registered: November 2006
Senior Member
abhi_shakya wrote on Sun, 31 January 2010 06:25
Table 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')?
Previous Topic: a complex query returning data in a refcursor....
Next Topic: Materialized views
Goto Forum:
  


Current Time: Thu Sep 29 02:14:30 CDT 2016

Total time taken to generate the page: 0.07900 seconds