During the migration of a reporting platform with native Informix SQL Syntax (especially the informix outer extension syntax) to an ansi compliant system (for example Cognos) you probably may encounter problems. There is no 1:1 equivalence for the outer extension syntax in informix to ansi sql. Therefore we have to workaround those cases to elimante different results.
But what is exactly the difference? A short example should help you to understand the issue with outer joins.
Our data (2 Tables):
Simple Ansi left outer:
SELECT x.c1, x.c2, y.c1, y.c2
FROM x LEFT JOIN y ON x.c1 = y.c1
Result:
Now the same example with a filter applied on the outer table leads us to the issue.
Informix syntax (what we want to re-write in ansi):
SELECT x.c1, x.c2, y.c1 AS yc1, y.c2 AS yc2
FROM x OUTER y
where x.c1=y.c1 AND y.c2='a'
Not (!) the same as (ansi left outer) :
SELECT x.c1, x.c2, y.c1, y.c2
FROM x LEFT JOIN y ON x.c1 = y.c1
WHERE y.c2='a';
Result (Ansi):
Correctly translated ansi syntax:
SELECT x.c1, x.c2, derived_y.c1 AS yc1, derived_y.c2 AS yc2 FROM x
LEFT JOIN (SELECT c1, c2 FROM y WHERE c2='a') AS derived_y ON x.c1 = derived_y.c1;
Result (Informix):
Here we are. As you can see we make use of the derived table syntax to workaround this issue. Unfortunately Informix 9.x does not support the derived sql syntax. In addition: I always recommend to make use of the latest current informix server version where possible because many ansi sql issues (mostly performance) were adressed in the version 11.x and above
