Home » SQL & PL/SQL » SQL & PL/SQL » Help with subquery
Help with subquery [message #192840] Wed, 13 September 2006 18:59 Go to next message
ferrethouse
Messages: 43
Registered: August 2006
Member
The query below executes but it isn't returning the correct rows. I know why it isn't but I'm not sure how to fix it. The subquery in the EXISTS statement joins the workorders table to the samples table. There are usually many samples for a given workorder. I only want the records returned where dateCompleted is null for all the samples on a workorder. As the query is written below it will return all workorders where any one of the samples has a dateComplete = null in tblSamplePrep.

select workorder,priority, disabled from ( 
SELECT workorder, rownum as rn, priority, disabled FROM ( 
SELECT ENV_ORDER_HEADER.ORDER_ID as workorder, nvl(tblWorkorder.priority,'') as priority, disabled 
FROM ENV_ORDER_HEADER 
INNER JOIN CLIENTMASTER ON ENV_ORDER_HEADER.ORDER_COMP_CODE = CLIENTMASTER.COMPANY_CODE 
INNER JOIN tblWorkorder ON tblWorkorder.workorder = env_order_header.order_id 
WHERE ENV_ORDER_HEADER.ORDER_DUEDATE is not null
and ((to_char(ENV_ORDER_HEADER.ORDER_DUEDATE,'MM/DD/YYYY') = '' and '' is not null) or '' is null) 
Order by priority,ENV_ORDER_HEADER.ORDER_DUEDATE desc
) t
where EXISTS (
select tblWorkorder.workorder from
tblWorkorder, tblsample, tblanalysis, tblpackage, tblSamplePrep
where t.workorder = tblWorkorder.workorder
and tblworkorder.workorder = tblsample.workorder 
and tblsample.sampleindex = tblanalysis.sampleindex 
and tblanalysis.packageid = tblpackage.packageid 
and tblSamplePrep.sampleindex = tblSample.sampleindex 
and tblSamplePrep.datecompleted is null
)
) 
where rn between 1+0*20 and 20+0*20
Group by workorder,priority, disabled 
order by priority


Thanks in advance for your help.
Re: Help with subquery [message #192847 is a reply to message #192840] Wed, 13 September 2006 21:26 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
How about:

where NOT EXISTS (
select tblWorkorder.workorder from
tblWorkorder, tblsample, tblanalysis, tblpackage, tblSamplePrep
where t.workorder = tblWorkorder.workorder
and tblworkorder.workorder = tblsample.workorder
and tblsample.sampleindex = tblanalysis.sampleindex
and tblanalysis.packageid = tblpackage.packageid
and tblSamplePrep.sampleindex = tblSample.sampleindex
and tblSamplePrep.datecompleted is NOT null
)


Ross Leishman
Re: Help with subquery [message #193025 is a reply to message #192840] Thu, 14 September 2006 10:15 Go to previous message
ferrethouse
Messages: 43
Registered: August 2006
Member
So obvious Embarassed

Thanks!!
Previous Topic: replace colum value
Next Topic: Export Data From Oracle to MS-Excel
Goto Forum:
  


Current Time: Sun Dec 04 08:31:57 CST 2016

Total time taken to generate the page: 0.04066 seconds