[ic] Filling perl hashes with [query] and searching the hash for product display restriction

Dan B db@cyclonehq.dnsalias.net
Thu, 25 Jan 2001 12:50:44 -0800

First of all, thanks to Mark Johnson, Jeff Carnahan, and Mike Heins for 
shedding some light on the subject and even trying to understand the 
gibberish that constituted my last e-mail.  Since then I've progressed 
several steps and a thousand milligrams of caffeine. :-)

Quick Q: Is there anyway to make [query] nestable?  How about a [query] 
inside a [loop list=``]?  Any difference in 4.7.x?  From my testing it 
seems impossible.

Goal:  big hash filled with sku => copay, taken from two SELECTS.  (Unless 
someone can combine these into one JOIN or something).
         %covered_hcpcs  = SELECT hcpcs,copay    FROM coverage WHERE 
carrier_idr = 11001
         %covered_skus   = SELECT sku            FROM products WHERE hcpcs 
= $covered_hcpcs{$i} #where $i increments in a loop through all the hcpcs
         The desired result is a larger has of skus than hcpcs, since there 
are many skus per each hcpcs.

Here is how I'm trying to do it with mostly interchange tags:
[set cust_carrier_idr]11001[/set]

     [perl tables=coverage]
             my $db = $Db{coverage};
             @set = $db->query('select hcpcs,copay from coverage');
             $Scratch->{covered_hcpcs} = \@set;

     [loop list=`$Scratch->{covered_hcpcs}`]
                 [loop-param hcpcs]
                 [loop-param copay]

                 Here is where I would *like* to embed the following 
[query], so I can access [loop-param hcpcs] and [loop-param copay].  Is 
there any other way to access the $Scratch->{covered_hcpcs} hash in IC than 
the new [loop] construct?

                         SELECT sku FROM products
                         WHERE hcpcs = 'A4253'
                         [comment]WHERE hcpcs = '[sql-param hcpcs]'[/comment]
                         Covered sku = [sql-param sku]<BR>
Here is how I'm trying to do it in Perl (please don't laugh).

      [perl tables=coverage]
             my $db = $Db{coverage};
             @set = $db->query('select hcpcs,copay from coverage WHERE 
carrier_idr = 11001');
             $Scratch->{covered_hcpcs} = \@set;

                         $db = $Db{products};
                         #loop through all of $Scratch->{covered_hcpcs} 
here somehow, setting $i to hcpcs value.
                                 @set = $db->query('select sku from 
products WHERE hcpcs = $Scratch->{covered_hcpcs->{$i}}');
                         #end loop.
                         $Scratch->{covered_skus} = \@set;

It obviously needs some work.  What do you guys think?  I sure appreciate 
the help,