OraFAQ Forum: Forms » lov problem

Home » Developer & Programmer » Forms » lov problem

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

lov problem [message #149211]

Wed, 30 November 2005 09:51

fmis
Messages: 11
Registered: October 2005
Location: CALICUT

Junior Member

we have a LOV , its select query consists of three tables and it may have more than 10,000 records. it is having two innerqueries. when we activate this lov it is taking more than 3 minutes. how we can reduce the activation time?.

thanks in advance

Report message to a moderator

Re: lov problem [message #149275 is a reply to message #149211]

Wed, 30 November 2005 22:55

kiran
Messages: 503
Registered: July 2000

Senior Member

It will be good , if you could post that queries.So that everybody get to see those and can give you better way doing it.

--Kiran.

Report message to a moderator

Re: lov problem [message #149291 is a reply to message #149275]

Thu, 01 December 2005 01:22

fmis
Messages: 11
Registered: October 2005
Location: CALICUT

Junior Member

SELECT DISTINCT MARKING_REGISTER.TREE_NO, MARKING_REGISTER.SPECIES,
MARKING_REGISTER.DATE_OF_FELLING, MARKING_REGISTER.BIT_CODE
FROM MARKING_REGISTER
WHERE MARKING_REGISTER.BIT_CODE=:BIT_DETAILS.BIT_CODE AND
MARKING_REGISTER.DATE_OF_FELLING IS NOT NULL AND
MARKING_REGISTER.BIT_CODE||MARKING_REGISTER.TREE_NO
NOT IN (SELECT STOCK_REGISTER.BIT_CODE||STOCK_REGISTER.TREE_NO
FROM STOCK_REGISTER) and MARKING_REGISTER.BIT_CODE||MARKING_REGISTER.TREE_NO not in
(select BIT_CODE||TREE_NO from poles345_det) ORDER BY TO_NUMBER(SUBSTR(TREE_NO,1,INSTR(TREE_NO,'/')-1))

Report message to a moderator

Re: lov problem [message #149292 is a reply to message #149291]

Thu, 01 December 2005 01:26

fmis
Messages: 11
Registered: October 2005
Location: CALICUT

Junior Member

hai
please see the query.

SELECT   DISTINCT marking_register.tree_no,
                  marking_register.species,
                  marking_register.date_of_felling,
                  marking_register.bit_code
FROM     marking_register
WHERE    marking_register.bit_code = :bit_details.bit_code
         AND marking_register.date_of_felling IS NOT NULL 
         AND marking_register.bit_code || marking_register.tree_no NOT IN (SELECT stock_register.bit_code || stock_register.tree_no
                                                                           FROM   stock_register)
         AND marking_register.bit_code || marking_register.tree_no NOT IN (SELECT bit_code || tree_no
                                                                           FROM   poles345_det)
ORDER BY To_number(Substr(tree_no,
                          1,
                          Instr(tree_no,
                                '/') - 1))

Mod-upd: How about formatting the code next time. By the way YOU can update YOUR OWN entries, you do not need to repost basically the same information.

[Updated on: Tue, 06 December 2005 01:54] by Moderator

Report message to a moderator

Re: lov problem [message #150054 is a reply to message #149292]

Tue, 06 December 2005 01:58

djmartin
Messages: 10181
Registered: March 2005
Location: Surges Bay TAS Australia

Senior Member
Account Moderator

Okay ... change the 'NOT IN' statements to 'NOT EXISTS' and may I suggest that you create a new field called tree number, populate it with "TO_NUMBER(SUBSTR(TREE_NO,1,INSTR(TREE_NO,'/')-1))", and use it to do the sorting. I don't like multiple key component fields. Each bit should be in its own field. You can concatenate them for reports but when combined they are a pain when trying to do retrievals.

David

Report message to a moderator

Previous Topic:	Deleting from an Oracle form
Next Topic:	Master Detail Form

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Feb 08 07:39:38 CST 2025