Solved

MySQL SQL to Informix Conversion Issue

Posted on 2013-05-31
6
534 Views
Last Modified: 2013-06-11
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
Comment
Question by:NovoDev
  • 3
  • 3
6 Comments
 
LVL 16

Expert Comment

by:DcpKing
Comment Utility
What version of Informix are you using? IBM here seems to be announcing something new to 11.10.

hth

Mike
0
 

Author Comment

by:NovoDev
Comment Utility
We are using Informix 2000.
0
 
LVL 16

Expert Comment

by:DcpKing
Comment Utility
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
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 

Accepted Solution

by:
NovoDev earned 0 total points
Comment Utility
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
 
LVL 16

Expert Comment

by:DcpKing
Comment Utility
Nice code. Sorry I took so long :)
0
 

Author Closing Comment

by:NovoDev
Comment Utility
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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
CCModeler offers a way to enter basic information like entities, attributes and relationships and export them as yEd or erviz diagram. It also can import existing Access or SQL Server tables with relationships.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now