[ic] [query] on multiple tables/databases
Michael Lehmkuhl
interchange-users@icdevgroup.org
Thu Sep 26 10:40:04 2002
Hello.
I'm attempting to get a list of skus and quantities from the orderline
database for all transactions that aren't deleted or TEST transactions.
We're running with MySQL.
I don't get any errors in either the catalog error.log or the IC error.log
when running this, but the $outfile file is not created. If I take the same
query and run it from another MySQL client, the file is created with the
correct data inside.
[perl tables='orderline transactions']
my $ep_next_order_num = $CGI->{ep_next_order_num};
$ep_next_order_num = 0 unless ($ep_next_order_num);
$sql = "SELECT orderline.sku,orderline.quantity
INTO OUTFILE '$outfile' FROM orderline,transactions
WHERE (orderline.order_number >= '$ep_next_order_num')
AND (orderline.order_number = transactions.order_number)
AND (transactions.deleted = 0)
AND (orderline.order_number NOT LIKE 'TEST%')";
$result_hasharray = $Tag->query({
table => 'orderline,transactions',
sql => $sql,
hashref => 'my_results', },
);
[/perl]
I suppose I could first do a query to get the list of order_numbers from the
transactions database, and then do something like this:
SELECT sku,quantity FROM orderline WHERE order_number NOT IN ('<order
numbers from transactions database go here>')
I'd rather do it all in one query if possible, though. Any help on this is
appreciated.
Thanks.
--
Michael Lehmkuhl <michael@electricpulp.com>
Partner, Electric Pulp
1.605.988.0177 : 1.888.722.PULP
electricpulp.com