Home » Developer & Programmer » Forms » Query Problem at pre-insert
Query Problem at pre-insert [message #277469] Tue, 30 October 2007 04:49 Go to next message
musman
Messages: 147
Registered: July 2007
Location: Lahore
Senior Member

am using this query at pre-insert at block level in forms6i.
form is tabular form.
select nvl(max(module),0)+1 into :module
from st_modules;

it works fine from 0.....9
but after that it only inserting 10 in each field not going to
11.
where`s the problem

[Updated on: Tue, 30 October 2007 05:22]

Report message to a moderator

Re: Query Problem at pre-insert [message #277478 is a reply to message #277469] Tue, 30 October 2007 05:29 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I suspect that 'st_modules.module' is of a CHARACTER data type, so when 9 is entered into it, the next value is 9 + 1 = 10. But, when characters are in question, 9 > 10 so you'll always get 10 as the next result.
SQL> l
  1  with test as
  2    (select '8' col from dual union
  3     select '9' col from dual union
  4     select '10' col from dual
  5    )
  6  select col from test
  7* order by col
SQL> /

CO
--
10
8
9


So, either alter the table and make 'module' a NUMERIC, or use TO_NUMBER function in a trigger.
Re: Query Problem at pre-insert [message #277479 is a reply to message #277478] Tue, 30 October 2007 05:33 Go to previous messageGo to next message
musman
Messages: 147
Registered: July 2007
Location: Lahore
Senior Member

hmm..
yes you find it..thanks..it`s working fine now..
Re: Query Problem at pre-insert [message #277480 is a reply to message #277469] Tue, 30 October 2007 05:34 Go to previous messageGo to next message
sudharshan
Messages: 48
Registered: November 2006
Member
hi


Think about two users using the same application which max value will it take

Try not to use max function instead use sequences.


Regards
sudharshan
Re: Query Problem at pre-insert [message #277507 is a reply to message #277480] Tue, 30 October 2007 07:03 Go to previous message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
My thoughts exactly.

MHE
Previous Topic: Show a tab Page
Next Topic: FRM-18108: Failed to load the following objects
Goto Forum:
  


Current Time: Sat Dec 10 14:54:15 CST 2016

Total time taken to generate the page: 0.08966 seconds