Syntax Error in correlated Subquery

[Isaac]

Can anyone spot my syntax error?

MaxDateImported: (select max([DateImported]) from snapshottable2 as [st] where [st].[sp_id] = [snapshottable2].[sp_id])

 

[theDBguy]

Hi. Just a guess, but maybe try removing the square brackets after the "as" for the alias. i.e. "...from snapshottable2 a st where..."

 

[Ranman256]

use a query (not sql) and you wont get syntax errors.

 

[Isaac]

Hi. Just a guess, but maybe try removing the square brackets after the "as" for the alias. i.e. "...from snapshottable2 a st where..."

Thank you, that did not work. I have very similar code in other subqueries (select something from snapshottable2 as [st] where [st].[something]= etc) and it works OK, but this is the first time I used max in a subquery in recent memory.

use a query (not sql) and you wont get syntax errors.

Ha ha, yes, you make a fine point there.

But, coming up soon, I will be potentially using BASE SAS's proc sql , and I have been doing anything that comes along to help me hone my skills in writing pure unadulterated ANSI SQL, which includes embracing Access subqueries, to the extent that THEY embrace ansi sql, which I believe they generally do.

 

[isladogs]

@Ranman256
It is a query & Isaac has got syntax errors

@Isaac
Your expression uses a self join for no obvious reason (at least to me). Why not just use MaxDateImported: Max(DateImported)

If I'm missing something and a self join is required, try removing ALL superfluous bracketing starting with ...AS [st]

 

[Isaac]

Your expression uses a self join for no obvious reason (at least to me). Why not just use MaxDateImported: Max(DateImported)

Basically I wanted to use the chance to do a (perhaps unnecessary) subquery. Also, I didn't want to convert the whole thing to a Totals query.
The reason I didn't want to do this is because I thought it may "mess up" (or require too much more tuning) the rest of the query.

I know what I am about to say might be a terribly non-optimized approach, but sometimes I write a subquery to get an aggregate function instead of converting the query to a Totals query because either 1) I can't group by a subquery, and I already have OTHER subqueries, 2) I'm not sure I want to convert the whole thing to a Totals query.

 

[Isaac]

PS - However I just tried your suggestion

Your expression uses a self join for no obvious reason (at least to me). Why not just use MaxDateImported: Max(DateImported)

... and I still get a syntax error even though I'm NOT doing it by subquery. Must be something amiss with my data.......let me triple check on that.

 

[isladogs]

Using a subquery will always be slow. Using a subquery for no reason instead of a Max expression is inefficient.
In any case using Max(FieldName) as a query field isn't the same as using an aggregate query

 

[Isaac]

In any case using Max(FieldName) as a query field isn't the same as using an aggregate query

I see what you mean. I will try that, thanks