Re: How to speeed up the query performance

Thanks Chris and Stephan
the query is working now.

Abdul Wahab Dahalan wrote:

> I cant find where to put the parenthesis as u said Stephan.
>
> Here the query :
>
> select ts.transportid,ts.transporttype,ts.transportcapacity,
> ts.transportstatus,ts.routecoverage,ts.transportregnumber
> from transportsetup ts
> where ts.bizid = 'B126'
> and ts.deletestatus = 0
> and NOT EXISTS (select t.transportid
> from transportsetup t,servicedetail s,logisticservice l
> where ts.transportid = t.transportid
> and t.bizid=l.bizid
> and l.serviceid=s.serviceid
> and t.transportid=s.transportid
> and t.bizid = 'B126'
> and l.status='Pending'
> or t.bizid=l.bizid
> and l.serviceid=s.serviceid
> and t.transportid=s.transportid
> and t.bizid = 'B126' and l.status='Reserved')
> order by ts.transporttype;
>
>
>
> Stephan Szabo wrote:
>
>>On Thu, 14 Aug 2003, Abdul Wahab Dahalan wrote:
>>
>>
>>
>>>Hai Chris!
>>>Thanks for the solution but seem it doesnt work.
>>>(0 rows) returned when I used NOT EXITS but (4 rows) returned
>>>when NOT IN is used...................
>>>
>>>
>>
>>Maybe you need a set of parenthesis around the old conditions
>>because of the or.
>>
>>
>>
>>>>Try:
>>>>select
>>>>ts.transportid,ts.transporttype,ts.transportcapacity,ts.transportstatus,ts.routecoverage,ts.transportregnumber
>>>>
>>>>
>>>>
>>>>from transportsetup ts
>>>
>>>
>>>>where ts.bizid = 'B126'
>>>>and ts.deletestatus = 0
>>>>and NOT EXISTS ( select t.transportid
>>>> from transportsetup t,servicedetail s,logisticservice l
>>>> where ts.transportid = t.transportid
>>>>
>>>>
>>
>>
>>
>>>> and t.bizid=l.bizid
>>>>
>>>>
>>Replace the above with:
>> and (t.bizid=l.bizid
>>
>>
>>
>>>> and l.serviceid=s.serviceid
>>>> and t.transportid=s.transportid
>>>> and t.bizid = 'B126'
>>>> and l.status='Pending'
>>>> or t.bizid=l.bizid
>>>> and l.serviceid=s.serviceid
>>>> and t.transportid=s.transportid
>>>> and t.bizid = 'B126' and l.status='Reserved' )
>>>>
>>>>
>>
>>Add:
>> )
>>
>>
>>
>>>>order by ts.transporttype;
>>>>
>>>>
>>
>>
>>---------------------------(end of broadcast)---------------------------
>>TIP 7: don't forget to increase your free space map settings
>>
>>
>>
>>
>