?
Solved

MySQL SQL to Informix Conversion Issue

Posted on 2013-05-31
6
Medium Priority
?
576 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 

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

Get real performance insights from real users

Key features:
- Total Pages Views and Load times
- Top Pages Viewed and Load Times
- Real Time Site Page Build Performance
- Users’ Browser and Platform Performance
- Geographic User Breakdown
- And more

Question has a verified solution.

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

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Containers like Docker and Rocket are getting more popular every day. In my conversations with customers, they consistently ask what containers are and how they can use them in their environment. If you’re as curious as most people, read on. . .
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

764 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