[ic] Ideas to substitute for a [query] inside a [query]?

Dan B db@cyclonehq.dnsalias.net
Wed, 14 Feb 2001 21:33:19 -0800


Thanks for the reply Christopher F. Miller,

At 12:07 AM 2/15/2001 -0500, you wrote:
>On Wed, Feb 14, 2001 at 08:15:15PM -0800, Dan B wrote:
> > Since Interchange cannot do a [query] inside a [query] (docs say "Nesting:
> > NO"), what can I use to get the same functionality?
>
>There are **at least** four things you can do:
>1) Figure out how to rewrite the query as a JOIN

I would really love to do this, but I didn't think a JOIN would apply to my 
situation.  Here's psuedocode for what I'm trying to do:

SELECT category WHERE x = y
         display category1
                 SELECT skus WHERE x = category1 AND x = y
                         display sku1
                         display sku2
                         display sku3
                         display sku4
         display category2
                 SELECT skus WHERE x = category2 AND x = y
                         display sku1
                                 display sku2
                         display sku3
                         display sku4
         display category3
                 SELECT skus WHERE x = category3 AND x = y
                         display sku1
                         display sku2
                         display sku3
                         display sku4

Is there anyway to get the above result from one SQL query?

>2) Try to preprocess and denormalize your tables so you can pull what
>you need in one record

(Probably as a last resort).

>3) Make multiple queries and reconcile with perl, eg map %CATEGORIES
>result set to %SKU set.

I was thinking about doing this type of thing, but it seems like #4 is 
easier.  Besides, I'm not sure where to get started (kind of like #4).

>4) Write it as a perl sub so you can make more than one query
>and do it based on what the first query returns.
>
>It does not look like you need 4, 2 is a short dead end but very
>good in some cases, 1 is the most economical approach, and 3
>means you couldn't figure out how to do it right but just have
>to get it to work.  :-)
>
>I'd bet you could get that to work as a simple JOIN.
>
>We do next to everything in perl (option 4).  If you go that route
>you want to start looking at glue to the internal database routines.


Thanks for your help!  I'm going to get cracking on that perl code.

-Dan Browning




>{
>     $table ||='products';
>
>     $db=$Vend::Database{$table} or
>         ::logGlobal(sprintf(qq`%s: %d ERROR: dbi_query: unknown base 
> table: %s, SQL: %s`,__FILE__,__LINE__,$table,$query));
>
>     $db=$db->ref();
>     $db=$db->[$DBI];
>     my($sth);
>     eval {
>         unless($sth=$db->prepare($query)) {
>             ::logGlobal(" Error: 5 $DBI::errstr on $query") unless 
> ($DBI::errstr=~/specials/); # specials
>             return;
>         }
>         unless($sth->execute()) {
>             ::logGlobal(" Error: 4 $DBI::errstr on $query") unless 
> ($DBI::errstr=~/exist/); # specials
>             return;
>         }
>
>         # This returns a reference to array of references to arrays
>         $tmp=$sth->fetchall_arrayref;
>     };
>     if ($@||$DBI::errstr) {
>         ::logGlobal("ERROR: 6 array_query croaked, error: $@ $DBI::errstr 
> SQL: $query") unless ($DBI::errstr=~/exist/);
>         return;
>     }
>     if($sth->rows) {
>         return @{$tmp};
>     } else {
>         return;
>     }
>}
>
> >
> > Here's the current code (working):
> > [query
> >          list=1
> >          st=db
> >          sql=|
> >                  SELECT  DISTINCT category
> >                  FROM    products
> >                  WHERE   hcpcs IN (
> >                                  SELECT  hcpcs
> >                                  FROM    coverage
> >                                  WHERE   carrier_idr = '[value 
> carrier_pri]'
> >                                  )
> >                  ORDER BY category;
> >          |
> > ]
> >          [sql-param category]<BR>
> > [/query]
> >
> > It just lists the categories.  But I would like to link the categories 
> with
> > a very specific link, based on some values I get from a different query....
> >
> > [query
> >          list=1
> >          st=db
> >          sql=|
> >                  SELECT  DISTINCT category
> >                  FROM    products
> >                  WHERE   hcpcs IN (
> >                                  SELECT  hcpcs
> >                                  FROM    coverage
> >                                  WHERE   carrier_idr = '[value 
> carrier_pri]'
> >                                  )
> >                  ORDER BY category;
> >          |
> > ]
> >          [page href="scan"
> >                  arg="
> >                  fi=products
> >                  sp=results
> >                  st=db
> >                  co=yes
> >                  sf=category
> >                  se=[query
> >          list=1
> >          st=db
> >          sql=|
> >                  SELECT  sku
> >                  FROM    products
> >                  WHERE   category = '[sql-param category]'
> >                  AND             hcpcs IN (
> >                                  SELECT  hcpcs
> >                                  FROM    coverage
> >                                  WHERE   carrier_idr = '[value 
> carrier_pri]'
> >                                  )
> >                  ORDER BY category;
> >                  |
> >          ][sql-param sku]|[/query]"
> >          ]
> >          [sql-param category][/page]<BR>
> >          [comment] The above [page] code would result in something like
> > se=3245|5894|6546|7657|3124|  [/comment]
> > [/query]
> >
> > Does anyone have any good ideas about what kind of [calc] I should try to
> > get these kind of results?  Is there any code samples already of the
> > [query] functionality done in perl?
> >
> > Thanks,
> >
> >
> > 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
>
>--
>
>Christopher F. Miller, Publisher                             cfm@maine.com
>MaineStreet Communications, Inc         208 Portland Road, Gray, ME  04039
>1.207.657.5078                                       http://www.maine.com/
>Content management, electronic commerce, internet integration, Debian linux
>
>_______________________________________________
>Interchange-users mailing list
>Interchange-users@lists.akopia.com
>http://lists.akopia.com/mailman/listinfo/interchange-users

Dan Browning, Cyclone Computer Systems, danb@cyclonecomputers.com