Syntax Error when trying to SubQuery in MSQUERY

**tpcervelo** · 08-04-2009, 05:06 PM

I have table JobCost_Hdr and ShopOrd_Hdr with Job# and Shop# the matching fields. JobCost has one record per Job but ShopOrd can have multiple records per Job. I am trying to pull records from Job and one field from the first matching record from Shop. I have tried putting a Select Top 1 sub query but continue to get a syntax error regardless of what I try.
This is the latest SQL try from Microsoft Query (Excel 2008).
SELECT JOBCST_HDR.ID_JOB_ODBC, JOBCST_HDR.NAME_CUST,
(SELECT TOP 1 SHPORD_HDR.ID_ITEM_PAR
FROM PUBLIC.SHPORD_HDR SHPORD_HDR
WHERE JOBCST_HDR.ID_JOB_ODBC=SUBSTRING(SHPORD_HDR.ID_SO_ODBC,4,6))
FROM PUBLIC.JOBCST_HDR JOBCST_HDR,
WHERE JOBCST_HDR.STAT_JOB='O'
Thak you in advance for any help you can offer.

**tony h** · 08-05-2009, 08:13 AM

I can't test tis at the moment but I would have expected you to need an "AS" in the "FROM" clauses ie:

FROM PUBLIC.SHPORD_HDR AS SHPORD_HDR

**Richard Schollar** · 08-05-2009, 08:22 AM

Howdy

You appear to have a comma after the FROM clause and before the WHERE clause, so try removing it:

SELECT JOBCST_HDR.ID_JOB_ODBC, JOBCST_HDR.NAME_CUST,
(SELECT TOP 1 SHPORD_HDR.ID_ITEM_PAR
FROM PUBLIC.SHPORD_HDR SHPORD_HDR
WHERE JOBCST_HDR.ID_JOB_ODBC=SUBSTRING(SHPORD_HDR.ID_SO_ODBC,4,6)) 
FROM PUBLIC.JOBCST_HDR JOBCST_HDR  
WHERE JOBCST_HDR.STAT_JOB='O'

Are you targeting SQL Server with the code (as Substring isn't supported in the Access driver AFAIK)? I don't know if SQL Server supports SELECT TOP 1 syntax so this may be another source of the problem.

Richard