Re: How to speeed up the query performance

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
>
>
>
>