SQL: Subquery nesting is too deep.

[psperry2]

Here is my SQL statement:

SELECT A.last, A.first, A.mpotc, C.clas_Title, A.mdoe, A.sdoe, A.dob, B.h_Salary ;
FROM EMP_MAST A RIGHT JOIN EMP_HIST B ON A.SSN = B.SSN AND B.Effect_dat IN ;
(SELECT MAX(Effect_dat) FROM EMP_HIST WHERE EMP_HIST.SSN = EMP_MAST.SSN) ;
JOIN CLASSCOD C ON C.Class_code = B.H_classcd ;
WHERE A.STATUS = 'A'

I have to find the one EMP_HIST record that has the latest effect_dat not greater that today's date. Generally the very last physical record in the table with a matching ssn.

I still need to code to make sure that I don't get a record with a future effect_dat, but I thought I would come back to that after I succeeded getting the very last emp_hist record in the query. And there could be 2 records entered on the same day, with the same effect_dat, so I always want the last one entered.

This is a legacy system with a poor design and its not easy getting the information I need.

Thanks for any help!

 

[Mike Lewis]

So, what exactly is the problem?

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[psperry2]

I get the message SQL: Subquery Nesting is too deep.

I just need another different SQL statement that will get the data without an error

 

[Mike Lewis]

You might try moving the subquery from the ON clause to a WHERE clause. In other words:

RIGHT JOIN EMP_HIST B ON A.SSN = B.SSN .....
WHERE A.STATUS = 'A' AND AND B.Effect_dat IN .....

Also, I'd advise you not to use single-letter aliases. VFP reserves the letters A to J for built-in work area aliases. This is probably not directly related to your problem, but it could cause other issues.

Also, could you let us know which version of VFP you are using? There were some changes between 8 and 9 in the way subqueries are handled.

Mike



__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[psperry2]

I am using VFP 7. I will make the changes you suggested and see what happens1

 

[dgrewe]

To help figure out the record that was but in last on the same day, Do the tables have a field for a sequence number or primary key that can be used to determine what order the records were entered ?



David W. Grewe Dave

 

[psperry2]

Unfortunately no. There is no sequence number. Poor design, legacy system.