[Date Prev][Date Next][Thread Prev][Thread Next][Interchange by date
][Interchange by thread
]
Re: [ic] Ideas to substitute for a [query] inside a [query]?
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
_______________________________________________
Interchange-users mailing list
Interchange-users@lists.akopia.com
http://lists.akopia.com/mailman/listinfo/interchange-users
