• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 585
  • Last Modified:

MySQL SQL to Informix Conversion Issue

We have an Informix orders table from which we need to extract the newest order for each customer - the result would be a list of our customers containing the last (newest) order placed for each customer.
We have an existing MySQL orders table from which we are already extracting this information using the following MySQL SQL:

select t1.cust_id,t1.order_num from mytable as t1
JOIN(select cust_id, MAX(order_num) as order_num from mytable group by cust_id) as t2
on t1.cust_id=t2.cust_id and t1.order_num=t2.order_num
where row_date='05-30-2013'

However, the above SQL fails in Informix. We ran the SQL through the "Mimer" validator and it is giving a "F591, 'Derived tables'" warning. We have been busting our heads trying to correct the syntax, but are getting nowhere - can someone please offer a solution?  Thank you very much!
0
NovoDev
Asked:
NovoDev
  • 3
  • 3
1 Solution
 
DcpKingCommented:
What version of Informix are you using? IBM here seems to be announcing something new to 11.10.

hth

Mike
0
 
NovoDevAuthor Commented:
We are using Informix 2000.
0
 
DcpKingCommented:
I think that's rather old.
It's about 15 years since I programmed with Informix "in anger", but I think this might do what you're trying to achieve. If not, convert the View part into a table (create a table and fill it with the select statement) and then use that in the query.

CREATE VIEW vMaxOrders AS
   select cust_id, MAX(order_num) as order_num from mytable group by cust_id;

select t1.cust_id,t1.order_num 
from mytable as t1
JOIN vMaxOrders as t2
on t1.cust_id=t2.cust_id 
and t1.order_num=t2.order_num
where row_date='05-30-2013'

Open in new window


hth

Mike
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
NovoDevAuthor Commented:
One of my co-workers came up with the following, rather elegant, SQL from which we derived the solution - it is very fast. Never got a chance to try out what you sent, but thanks for the response!

select * from mytable t1
where t1.order_num IN
(select max(order_num)
from mytable
where cust_id=t1.cust_id) and t1.row_date ='05-30-2013'
0
 
DcpKingCommented:
Nice code. Sorry I took so long :)
0
 
NovoDevAuthor Commented:
We came up with the solution on our own before we received a response from the experts.  Our solution is working great and is very fast. Below is the SQL:

select * from mytable t1
where t1.order_num IN
(select max(order_num)
from mytable
where cust_id=t1.cust_id) and t1.row_date ='05-30-2013'
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now