Member Avatar for jovillanuev

Guys,

When i run this sscript i got an error in Where clause?
Where should be place this where clause?

SELECT p.ESN, p.ReturnDate,s.Receiptdate,e.Shipdate
FROM USProductRecovery p with (nolock)
OUTER APPLY((SELECT TOP 1 Receiptdate
FROM ESNSalvReceipts s with (nolock)
WHERE ESN = p.ESN
AND Receiptdate > p.Returndate
ORDER BY Receiptdate )s
OUTER APPLY(SELECT TOP 1 Shipdate
FROM dbo.ESNShip s with (nolock)
WHERE ESN = p.ESN
AND Shipdate > p.Returndate
ORDER BY Shipdate)e
Where p.returndate > '2012-11-01'
and p.ESN in ('268435459401659246','268435458814622954','270113179507864837')

Thanks,

JOV

  • 2 Contributors
  • 3 Replies
  • 115 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by BitBlt

Dani AI

Generated

Short summary: a syntax problem in the FROM/APPLY region (unbalanced parentheses or an unterminated APPLY subquery) will make SQL Server report the error at the WHERE clause even though WHERE itself is fine. As observed and later confirmed, fixing the missing closing parenthesis resolves the immediate error. Below are practical checks and follow-ups to prevent the same problem and harden the query.

Quick troubleshooting checklist:

  • Verify every OUTER/CROSS APPLY subquery is properly closed and has its own alias; mismatched parentheses are the most common cause of a “WHERE clause” error in this pattern.
  • Use distinct, descriptive aliases for each subquery (for example, rcv and shp) so you do not accidentally reuse the same alias name in multiple FROM items.
  • Remember WHERE at the end of the statement filters the outer query; filters intended for the APPLY result belong inside that subquery’s WHERE.
  • If date comparisons are used, prefer an unambiguous literal format (YYYYMMDD) or parameters, and be aware of time portions and NULLs when comparing dates.

Additional practical tips:

  • Test incrementally: run the main table SELECT without APPLYs, then add one APPLY at a time to isolate syntax or logic errors.
  • Check indexes on the correlated columns used in the APPLY subqueries (the key and the date column) because TOP 1 ... ORDER BY date can be slow without a supporting index.
  • Avoid NOLOCK unless you understand dirty-read risks; it can hide issues during debugging.

These steps keep the SQL readable and make it easier to spot the sort of parentheses/aliasing slip that triggered this thread.

Recommended Answers

All 3 Replies

Member Avatar for BitBlt

You have an extra open-parenthesis on line 3. Or, depending on what you mean in your query, a missing close-parenthesis at the end of line 12.

Also, just for clarity's sake, you may want to rethink the over-used alias "s" you use in lines 4, 7 and 9. You could use it in one place but not all three...it's confusing.

Edited by BitBlt because: n/a
Member Avatar for jovillanuev

Yes BItbit. I got already the Error.
Sorry for my late reply.

I have lacking 1 Open Parenthesis in line 12. it should be e).

for aliasis, i should use different aliasis per line that you mention.?

Member Avatar for BitBlt

Glad to hear you found the error.

As far as the alias change, it is not actually "necessary". Your query will run fine without that change. It was just a suggestion to avoid potential confusion among the various different scopes of query and "apply".

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.