OraFAQ Forum: Forms » Query Problem at pre-insert

Home » Developer & Programmer » Forms » Query Problem at pre-insert

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

Query Problem at pre-insert [message #277469]

Tue, 30 October 2007 04:49

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

Littlefoot
Messages: 21818
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.

Report message to a moderator

Re: Query Problem at pre-insert [message #277479 is a reply to message #277478]

Tue, 30 October 2007 05:33

musman
Messages: 147
Registered: July 2007
Location: Lahore

Senior Member

hmm..
yes you find it..thanks..it`s working fine now..

Report message to a moderator

Re: Query Problem at pre-insert [message #277480 is a reply to message #277469]

Tue, 30 October 2007 05:34

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

Report message to a moderator

Re: Query Problem at pre-insert [message #277507 is a reply to message #277480]

Tue, 30 October 2007 07:03

Maaher
Messages: 7065
Registered: December 2001

Senior Member

My thoughts exactly.

MHE

Report message to a moderator

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

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Sep 27 06:27:23 CDT 2024