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

[Dan B <db@cyclonehq.dnsalias.net>]

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;
             return;
     [/perl]

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

                 [comment]
                 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?
                 [/comment]
     [/loop]

         [query
                 list=l
                 st=db
                 sql=|
                         SELECT sku FROM products
                         WHERE hcpcs = 'A4253'
                         [comment]WHERE hcpcs = '[sql-param hcpcs]'[/comment]
                 |
         ]
                 <blockquote>
                         Covered sku = [sql-param sku]<BR>
                 </blockquote>
         [/query]
-----------------------------
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;
             return;
     [/perl]

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

-Dan


_______________________________________________
Interchange-users mailing list
Interchange-users@lists.akopia.com
http://lists.akopia.com/mailman/listinfo/interchange-users