Still having an issue with old problem

[dpk136]

I am still having this problem, no one seems to be able to figure this out. let me know what you think about it

thread222-1230395

David Kuhn
------------------

http://www.davidpkuhn.com

 

[Golom]

I think that George did figure it out but ...

SELECT   Max(ProcessNum) AS MaxProcessNum, JobNum 

FROM     FurnaceUsage 
 
WHERE   JobNum [red]IN[/red] (Select JobNum 
                   FROM   FurnaceUsage 
                   WHERE Status='INPROCESS' 
                     AND FurnaceNum='x' 
                     AND Location='y' 
                     AND TimeOut=##)

GROUP BY JobNum
ORDER BY JobNum

Your test should be a WHERE clause rather than a HAVING clause because you are not using any aggregate functions in the test. WHERE is applied before grouping occurs and HAVING after grouping. As you have it, the SQL would compute the MAX(ProcessNum) for every JobNum in the table and then drop all those where JobNum didn't meet the criteria. With it as a WHERE clause, only the specified JobNums are selected and MAX computed.

The sub-query is, in some cases, returning more than one record and an "equals" operator cannot be used to compare a single value (i.e. JobNum) with multiple values returned by the sub-query.

If you don't believe that the sub-query should return multiple values then go back and run a sub-query like

Select Count(*) As Occurrences, 
       Status, FurnaceNum, Location, TimeOut,
       MIN(JobNum) As MinJob, MAX(JobNum) As MaxJob
FROM   FurnaceUsage 
Group By Status, FurnaceNum, Location, TimeOut 
Having Count(*) > 1

To see those records that could produce multiple JobNumbers

 

[dpk136]

I'll try this and see. i'll let you know. Thanks

David Kuhn
------------------

http://www.davidpkuhn.com

 

[Golom]

Upon further contemplation, I don't see that you really need a sub-query at all

SELECT   Max(ProcessNum) AS MaxProcessNum, JobNum 

FROM     FurnaceUsage 
 
WHERE    Status='INPROCESS' 
  AND    FurnaceNum='x' 
  AND    Location='y' 
  AND    TimeOut=##

GROUP BY JobNum
ORDER BY JobNum

should do the same thing for you since "FurnaceUsage" is used in both the main and sub queries.

 

[dpk136]

Hmmm... i think you may have found the problem, i need to put this into the code and test it, but this would speed things up and allow it to run with less problems

David Kuhn
------------------

http://www.davidpkuhn.com