[ic] Need help with a perl query

Mark Lipscombe markl at gasupnow.com
Tue May 26 02:04:00 UTC 2009

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.
>   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');

The problem is most likely you're missing quotes around $lookup, 
something like:

my $ary2 = $db2->query('select sku from orderline where code like 

But assuming you're using the standard store database layout, you should 
have a field in the orderline table called order_number.  Better to use 
that, rather than a LIKE query again the code field.  Try something like:

my $ary2 = $db2->query('select sku from orderline where order_number = 

Even better would be to not do the orderline query inside a loop, but to 
instead use a RIGHT JOIN to get all that information in a single query. 
  Something like:

SELECT t.order_number, o.sku
FROM transactions t
RIGHT JOIN orderline ON o.order_number = t.order_number
WHERE t.status = 'processing'


More information about the interchange-users mailing list