Solved

MySQL SQL to Informix Conversion Issue

Posted on 2013-05-31
6
545 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
Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

 

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to shrink a transaction log file down to a reasonable size.
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.

920 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

11 Experts available now in Live!

Get 1:1 Help Now