[ic] Need help with a perl query

[Greg Hanson]

IC wrote:
> Hi folks,
>
> I am trying to use a piece of perl to return a list of order numbers (with
> status processing) and skus ordered, I have pasted the code below, it
> returns the list of order numbers ok from transactions but it doesn't return
> any skus from the orderline table. I also tried replacing my $lookup
> variable with a fixed orderline eg "12345-1" but it still didn't work.
>
>
> [perl orderline transactions]
>    my $db = $Db{transactions};
>    my $db2 = $Db{orderline};
>    my $ary = $db->query('select order_number from
>  transactions where status = "processing"');
>  
>
>   my $out = '';
>      foreach $row ( @$ary) {
>      my ($order_number) = @$row;
>      my $lookup = "%" . $order_number . "%";
>  
>      my $ary2 = $db2->query('select sku from
>      orderline where code like $lookup');
>   
>  
>      foreach $row ( @$ary2) {
>      my ($sku) .= @$row;
>      }
>  
>          $out .= "$order_number : $sku<br>\n";
>  }
>  return $out;
> [/perl]
>
>   
You could also just combine things to make a single query, and save a db
access...

[perl orderline transactions]
   my $db = $Db{transactions};

   my $query = qq( select o.order_number as order_number, o.sku as sku from
 transactions t, orderline o where t.order_number = o.order_number and t.status = 'processing');

   my $ary = $db->query({ sql => $query, hashref => 1});
	
   my $out;

   foreach my $row( @$ary){
	$out .= "$row->{order_number} : $row->{sku}\n";
   }

   return $out;
[/perl] 


>
> _______________________________________________
> interchange-users mailing list
> interchange-users at icdevgroup.org
> http://www.icdevgroup.org/mailman/listinfo/interchange-users
>
>
>   


-- 
Greg Hanson
Interchange Consulting
Perusion
1506 E Gilbert Ave
Coeur d'Alene, ID 83815

Email		greg at perusion.com
Phone		208-214-4306
Toll Free	800-949-1889
Fax		775-256-2231
Web		http://www.perusion.com

Nothing is fool proof to a sufficiently equipped fool