[RESOLVED] Is there such a thing as looping in SQL?

[caffeine]

I have a pretty tedious SQL statement that seems amenable for looping. Here's a snippet of the SQL (I'm using MS Access 2007):

SELECT 
1*(y1-0) / 12, 
2*(y1-0) / 12,
3*(y1-0) / 12,
4*(y1-0) / 12,
5*(y1-0) / 12,
6*(y1-0) / 12,
7*(y1-0) / 12,
8*(y1-0) / 12,
9*(y1-0) / 12,
10*(y1-0) / 12,
11*(y1-0) / 12,
y1,
y1 + 1*(y2 - y1),
y1 + 2*(y2 - y1),
y1 + 3*(y2 - y1),
y1 + 4*(y2 - y1),
y1 + 5*(y2 - y1),
y1 + 6*(y2 - y1),
y1 + 7*(y2 - y1),
y1 + 8*(y2 - y1),
y1 + 9*(y2 - y1),
y1 + 10*(y2 - y1),
y1 + 11*(y2 - y1),
y2,
y2 + 1*(y3 - y2),
y2 + 2*(y3 - y2),
y2 + 3*(y3 - y2),
y2 + 4*(y3 - y2),
y2 + 5*(y3 - y2),
y2 + 6*(y3 - y2),
y2 + 7*(y3 - y2),
y2 + 8*(y3 - y2),
y2 + 9*(y3 - y2),
y2 + 10*(y3 - y2),
y2 + 11*(y3 - y2),
y3
FROM hpaState;

and it goes on like this for 2 more "stanzas". The sql will be stored as a query in a MS Access file and called from an Excel VBA subroutine which receives the record-set. But this pattern is just asking for an inner loop of 0 to 12 and an outer loop of 1 to 5.

I'm a SQL newbie, so I guess my questions would be:

1. Is there a looping facility in SQL?

2. Considering the repetitive usage of things like "y2 - y1" and "y3 - y2", is there a way to declare variables in SQL so that I can calculate the value once and use it in the SQL? I assume that each usage of "y2 - y1" requires 2 lookups.

3. What's the optimum (speedwise) way of writing such a SQL statement?

Of course, I can just grab the values y1, ..., y5 and return them to the calling Excel VBA program and do these calculation in Excel VBA. But I was told that the fastest way of doing things is to do as much as you can in SQL since SQL is very fast.

Thank you!
Pete

[techgnome]

1) Yes, there is a looping mechanism in SQL - at least there is in T-SQL ( the T means transaction)
2) Access uses it's own flavor of SQL, doesn't support the looping structure (as far as I am aware)
3) You wouldn't see any kind of performance gain at all... infact, you would probably experience a degradation in performance. SQL Is designed for Set Based instructions, like the one you have. I'd leave it alone.

-tg