CODEWITHSUNDEEP
informix
MrSnrub
MrSnrub

Reputation: 455

Convert an Informix SQL query into ANSI-92 SQL?

What is the ANSI-92 equivalent of the following old Informix SQL query?

select * 
from categories c,
  orders o, 
  outer (employees e, person p) 
where c.categoryid = o.categoryid
  and p.personid = e.id
  and o.employeeid = e.id
  and o.orderid = 7742
  and e.term_date is NULL

I cannot seem to figure out exactly what the "outer (table1, table2)" syntax means.

Upvotes: 2

Views: 482

Answers (2)

Jonathan Leffler
Jonathan Leffler

Reputation: 753960

Mike Burdick's answer is, I believe, essentially correct — but I'm not completely sure of that. I'd be a little more comfortable with the comparison if the query were written as:

SELECT *
  FROM Categories AS C
  JOIN Orders     AS O ON C.CategoryID = O.CategoryID
  LEFT JOIN
       (SELECT * FROM
          FROM Employees AS E
          JOIN Person    AS P ON E.ID = P.PersonID
       ) AS E ON O.EmployeeID = E.ID
 WHERE O.OrderID = 7742
   AND E.Term_Data IS NULL

The OUTER (Employees e, Person p) in the original query is an inner-join sub-query that is outer-joined to the main query, which I've made clearer in my version by using the sub-query, but I think Mike's version is OK too. My concern is that Mike's version might more nearly equivalent to this (where the parentheses around employees e are optional in this scenario):

select * 
from categories c,
  orders o, 
  outer (employees e),
  person p
where c.categoryid = o.categoryid
  and p.personid = e.id
  and o.employeeid = e.id
  and o.orderid = 7742
  and e.term_date is NULL

I'd need to think hard before coming up with data sets that could distinguish between the two queries (either the original query in the question and the slight rewrite above, or between Mike's answer and mine).

One other nagging doubt is that Informix's old-style non-standard OUTER join notation has a quirk that the standard notation simply does not support. The problem can occur when filtering on data in the subordinate table (RHS of a left outer join). The rows in the dominant table (in this case, tables — orders and categories) are preserved by Informix even when there was a match in the subordinate table that got filtered out. I think that it works OK this time because the filter condition that I'm concerned about, which is AND e.term_date IS NULL, uses IS NULL. You could run into the problem if the filter condition was AND e.term_date > MDY(7,4,2032) (and there was a row in employees that did not match the filter condition), but I think IS NULL is OK. The Informix behaviour is hard to explain, and hard to justify beyond "that is the way it works and it was documented to work thus in 1987±2 years, and backwards compatibility deals with the rest". It is only the Informix-only OUTER join notation that behaves weirdly. The ANSI-standard LEFT OUTER JOIN notation works according to the standard. But it does make conversion a challenge if this quirk affects your legacy code.

TL;DR Test the query results very carefully.

Upvotes: 2

Mike Burdick
Mike Burdick

Reputation: 838

Something like:

select  *
from        categories c
Join        orders o
on          c.categoryid = o.categoryid
Left Join employees e
on          o.employeeid = e.id
Join        person p
on          e.id = p.personid
where       o.orderid = 7742
and         e.term_data is null

Upvotes: 1

Related Questions