Home » SQL & PL/SQL » SQL & PL/SQL » Having trouble understanding EXISTS clause
Having trouble understanding EXISTS clause [message #189026] Tue, 22 August 2006 17:21 Go to next message
ferrethouse
Messages: 43
Registered: August 2006
Member
This query is costing me my sanity.

This query returns one record (workorder = 06T182623)...

SELECT workorder,priority,dueDate from(
SELECT workorder,priority,dueDate,rownum as rn FROM(
SELECT tblWorkorder.workorder as workorder, nvl(tblWorkorder.priority,'') as priority, lims.env_order_header.order_duedate as dueDate
FROM tblWorkorder, lims.env_order_header
WHERE
tblWorkorder.workorder = lims.env_order_header.order_id
AND tblWorkorder.workorder IN (select wo FROM (select lims.env_order_header.order_id as wo FROM lims.env_order_header,tblWorkorder WHERE tblWorkorder.workorder = lims.env_order_header.order_id AND env_order_header.order_duedate is not null ORDER BY priority,lims.env_order_header.order_duedate desc) WHERE rownum < 1000)
AND EXISTS (SELECT null
FROM tblWorkorder,tblSample, tblAnalysis, tblPackage, tblPackageParam, tblParam 
WHERE tblWorkorder.workorder = tblSample.workorder
AND tblSAmple.sampleindex = tblAnalysis.sampleindex 
AND tblAnalysis.packageid = tblPackage.packageid 
AND tblPackage.packageid = tblPackageParam.packageid 
AND tblPackageParam.paramid = tblParam.paramid
AND tblParam.instrument = 'ION CHROMATOGRAPH')
ORDER BY priority, lims.env_order_header.order_duedate desc
))
WHERE rn between 1+2*20 and 20+2*20
AND workorder = '06T182623'


But it shouldn't. Because that workorder does not exist in the EXISTS clause portion of the query. The EXISTS clause should filter out that workorder. If I run just the EXISTS clause with an extra condition to only look for that workorder it returns nothing...

SELECT tblWorkorder.workorder
FROM tblWorkorder,tblSample, tblAnalysis, tblPackage, tblPackageParam, tblParam 
WHERE tblWorkorder.workorder = tblSample.workorder
AND tblSAmple.sampleindex = tblAnalysis.sampleindex 
and tblAnalysis.packageid = tblPackage.packageid 
and tblPackage.packageid = tblPackageParam.packageid 
and tblPackageParam.paramid = tblParam.paramid
and tblParam.instrument = 'ION CHROMATOGRAPH'
and tblWorkorder.workorder = '06T182623'



So why does my EXISTS clause not exclude that workorder as part of the larger query?
Re: Having trouble understanding EXISTS clause [message #189028 is a reply to message #189026] Tue, 22 August 2006 17:34 Go to previous messageGo to next message
Todd Barry
Messages: 4819
Registered: August 2001
Senior Member
I do not see a correlation value in your EXISTS clause. Usually there is a reference to a table in the outer query (tblWorkorder or lims.env_order_header), but you have included tblWorkorder again in the FROM clause in the EXISTS.

If I am looking for departments without employees:

select *
  from dept d
 where not exists (select null
                     from emp e
                    where e.deptno = d.deptno);

See how DEPT does not appear in the EXISTS - only a reference to d.deptno.
Re: Having trouble understanding EXISTS clause [message #189070 is a reply to message #189028] Wed, 23 August 2006 02:07 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I think Todd's hit the nail on the head.
Remove the talbe TblWorkorder from the FROM clause in the EXISTS subquery and see what happens.
Previous Topic: PL/SQL Records
Next Topic: How to call a URL and read what it returns?
Goto Forum:
  


Current Time: Wed Dec 07 18:38:32 CST 2016

Total time taken to generate the page: 0.10066 seconds