Solved

MySQL SQL to Informix Conversion Issue

Posted on 2013-05-31
6
563 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
ID: 39211607
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
ID: 39211888
We are using Informix 2000.
0
 
LVL 16

Expert Comment

by:DcpKing
ID: 39212281
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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 

Accepted Solution

by:
NovoDev earned 0 total points
ID: 39226923
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
ID: 39227848
Nice code. Sorry I took so long :)
0
 

Author Closing Comment

by:NovoDev
ID: 39237146
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

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Shadow IT is coming out of the shadows as more businesses are choosing cloud-based applications. It is now a multi-cloud world for most organizations. Simultaneously, most businesses have yet to consolidate with one cloud provider or define an offic…
Azure Functions is a solution for easily running small pieces of code, or "functions," in the cloud. This article shows how to create one of these functions to write directly to Azure Table Storage.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
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…

679 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