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

[cfm@maine.com]

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?


The issue is not so much nesting as proceedure.  The results of
the query are not there when you want them for the next query.  You 
don't really have a [query] inside a [query].

There are **at least** four things you can do:
1) Figure out how to rewrite the query as a JOIN
2) Try to preprocess and denormalize your tables so you can pull what
you need in one record
3) Make multiple queries and reconcile with perl, eg map %CATEGORIES
result set to %SKU set.
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.


{
    $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