[ic] Need help with a perl query

[Mark Lipscombe]

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 
"$lookup");

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 = 
"$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'

Regards,
Mark