[ic] Nested Queries

Jason Korkin jkorkin@korksoft.com
Thu, 12 Apr 2001 14:32:32 -0400


Thanks Dan! Worked like a charm.

Jason Korkin
President
Korksoft LLC
http://www.korksoft.com


----- Original Message -----
From: "Dan B" <db@cyclonehq.dnsalias.net>
To: <interchange-users@lists.akopia.com>
Sent: Thursday, April 12, 2001 2:15 PM
Subject: Re: [ic] Nested Queries


> At 01:47 PM 4/12/2001 -0400, you wrote:
> >Hello all...
> >
> >I'm trying to setup a nested query... here is what I have (most pulled
from
> >mailing list archives):
> >
> >[query sql="SELECT * FROM package_data WHERE pid='[value package]'"
list=1
> >interpolate=1]
> >         [set token][sql-param sku][/set]
> >         [mvasp tables="products"]
> >         <%
> >
> >         my $retval = "";
> >         my $dbh = $Sql{products};
> >         my $sql_query = "SELECT * FROM products WHERE
> >sku='$Scratch->{token}'";
> >
> >         my $sth = $dbh->prepare($sql_query) or return
error_message("can't
> >open products database");
> >         my $rc  = $sth->execute() or return HTML("can't open products
> >database");
> >
> >         while($sku = $sth->fetchrow_arrayref()) {
> >                 $retval .= "$sku->[0]|";
> >         }
> >
> >         return $retval;
> >         %>
> >         [/mvasp]
> >[/query]
> >
> >When I pull up the page, I get nothing; no results... I'm running IC
4.6.0
> >under RH Linux 7.  Any help would be appreciated...
>
> This is probably my fault.  I was trying to do nested queries with perl,
> because the docs said that [query] was not nestable.  It turns out that
the
> docs were out of date and query *is* nestable (so you don't have to do
perl).
>
> Here's what Heins said to me (after I got the perl working):
>
> http://developer.akopia.com/archive/interchange-users/2001/msg01564.html
>
> Quoting Dan B (db@cyclonehq.dnsalias.net):
>  > Solved it, works perfect.  :-)  Thanks to Christopher F. Miller and the
>  > mailing list archive.  Hopefully this will come in handy to the next
person
>  > searching the archives.
>  >
>
> And though I jumped in late, there actually isn't any reason you can't
nest a
> query. I don't know why I never remembered to remove the "canNest" check
for
> Tagref.pm, but that is actually a legacy from the less-capable MV3 parser.
>
> This nested query:
>
>      [query list=1 prefix=outer sql="select category from products"]
>          [query
>                  list=1 prefix=inner
>                  sql="
>                      SELECT sku,description
>                      FROM products
>                      WHERE category = '[outer-code]'
>                  "][outer-code] : [inner-code] : [inner-param
description]<BR>
>          [/query]
>      [/query]
>
> seems to work fine. Of course you have to have separate prefix= things
> so the placeholders get substituted correctly, but it will work. As will
> a loop in a loop, etc. Not the best way to get great performance, though.
8-)
>
> --
> Red Hat, Inc., 131 Willow Lane, Floor 2, Oxford, OH  45056
> phone +1.513.523.7621 fax 7501 <mheins@redhat.com>
>
> Experience is what allows you to recognize a mistake the second
> time you make it. -- unknown
>
> Dan Browning, Cyclone Computer Systems, danb@cyclonecomputers.com
>
>
> _______________________________________________
> Interchange-users mailing list
> Interchange-users@lists.akopia.com
> http://lists.akopia.com/mailman/listinfo/interchange-users