[ic] Need help with a perl query

[IC]

> 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]
> 


Thanks for all the replies, Greg's example above works a treat although I
still need some help, perhaps I should start with what I am actually trying
to do here.

I am trying to create a picking list of items from orders where the status
is processing, I could really do with picking up the item description as
well as the sku which I presume would mean another query to the products
table??

Next I want to be able to list the total quantity required of single sku, ie
if 10 orders each want 1 of sku os00001 then I want my list to tell me to
pick 10 rather than just the list the sku 10 times...

Suggestions welcome,
Thanks,
Andy.