Stories
Slash Boxes
Comments
NOTE: use Perl; is on undef hiatus. You can read content, but you can't post it. More info will be forthcoming forthcomingly.

All the Perl that's Practical to Extract and Report

use Perl Log In

Log In

[ Create a new account ]

Mark Leighton Fisher (4252)

Mark Leighton Fisher
  (email not shown publicly)
http://mark-fisher.home.mindspring.com/

I am a Systems Engineer at Regenstrief Institute [regenstrief.org]. I also own Fisher's Creek Consulting [comcast.net].
Friday May 12, 2006
11:25 AM

SQL Tricks: For and For-Each Loops

[ #29594 ]

Straight (non-procedural) SQL can perform simple For and For-Each loops. I didn't believe it myself, but reading Parsing CSV Values Into Multiple Rows showed me it could be done.

The trick lies in joining the table(s) of interest with a field whose values are the topic of the loop ($_ in Perl). If the field values are an unbroken simple sequence of numbers, you have a For loop. All other sets of topic values would be used in For-Each loops.

A simple example: Your local police have only 2 names for unknown perpetrators in their database, John Doe and Jane Doe, but needs 20 more names like John Doe #7 and Jane Doe #4. Using a field with values 1-10 as the loop topic ($_), you can create these additional names with SQL like:

insert into
   perpetrators
   (
      Name
   )
select
   (p.Name & ' #' & i.index) as Name
from
   perpetrators p, indexes i
where
   p.Name="John Doe"
   or p.Name="Jane Doe";

Since this is a cross join (no fields are joined between the two tables), the rows returned by the SELECT will be the Cartesian product of the two sets of values:

Index  Name
1      John Doe
2      John Doe
...
10     John Doe
1      Jane Doe
2      Jane Doe
...
10     Jane Doe

As you can see from this example, it is easy to derive the new perpetrator names from this SELECT using the Perpetrators.Name and Indexes.Index fields.

Why you would want to do this in SQL, I don't know. It reminds me of XSLTunit, somehow... But it does show how SQL can be used for simple For and For-Each loops.

The Fine Print: The following comments are owned by whoever posted them. We are not responsible for them in any way.
 Full
 Abbreviated
 Hidden
More | Login | Reply
Loading... please wait.
  • An outer join is a join on a field which contains all rows from the left, right or both tables (left/right/full outer join, respectively). Wherever a row has to be included in the result set but could not be joined to a row in other the table, the other table’s column’s in that result set row are filled with NULLs.

    A join that produces a Cartesian product is called a cross join. You can denote it explicitly in modern SQL dialects by saying something like

    INSERT INTO perpetrators ( name )
    SELECT

  • You are right, the technique is the cross join. I rarely use anything other than inner joins, so I had forgotten the term. I've corrected the essay. Thanks!