dcsimg
Results 1 to 2 of 2

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

  1. #1

    Thread Starter
    Addicted Member
    Join Date
    Sep 2006
    Posts
    223

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

    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):

    Code:
    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

  2. #2
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    32,677

    Re: Is there such a thing as looping in SQL?

    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
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width