I should clarify my prior post by pointing out that the unqualified xMin, xMax, yMin, and yMax columns referenced in the query are all coming from the centers table.

"Kevin Myers" <KevinMyers@austin.rr.com> wrote in message news:... > I'm running the following code under SAS 8.2 on Windows 2000 Pro: > > 1084 /* determine original grid blocks corresponding to each new grid point > */ > 1085 proc sql; > 1086 create table newGrid (drop=dc) as > 1087 select newGrid.*, xc, yc, %dist(x,y,xc,yc) as dc from newGrid, centers > MPRINT(DIST): sqrt(((xc)-(x))**2+((yc)-(y))**2) > 1088 where newGrid.x between xMin and xMax and newGrid.y between yMin and > yMax > 1089 group by x, y having calculated dc=min(calculated dc) > 1090 order by xc, yc; > NOTE: The execution of this query involves performing one or more Cartesian > product joins that can not be optimized. > NOTE: The query requires remerging summary statistics back with the original > data. > > I am almost 100% certain that the SQL optimizer in SAS *does* have the > ability to optimize where clauses that contain between conditions. Yet I > have both sorted and indexed the newGrid table referenced in the above query > (ordered by x, y and composite index xy on x, y), and am still receiving the > message regarding a cartesian product join that can't be optimized. I let > this query run to completion once, and it required more than 20 minutes for > the data that I am using, which is unacceptable. > > 1. Can anyone see something else that I am overlooking in the above query > that is preventing it from being optimized? > 2. Can anyone confirm whether optimization for between conditions is present > in SAS 8.2, or was that not implemented until SAS v9.x? > > Thanks, > s/KAM > >