Re: INNER and OUTER JOIN syntax in 7.1

cooke@pnc.com.au (Jeffrey Cooke) wrote: :I am developing a visual SQL query builder component for Boland's :Delphi language. It currently supports Interbase, Sybase, the "other" :database, Borland's Local SQL. :I would like to add support for Informix but I am find the necessary :information a bit hard to find. How can you even attempt to do this without the fine manuals from Informix. Call them up and tell them who you are. They will immediately send a set of SQL manuals to you, and may be they will not even charge you for it. Tell them I told you this. (They need true support in Delphi for Informix engines to sell more of them.) :What I would like to know is; :1. Does Informix support table joins using JOIN keyword and if so does :it support INNER, OUTER joins etc., for example this is legal in :Interbase, is it also legal in Informix? :SELECT T1.EMP_NO, T1.NAME, Sum(T2.SALETOTAL) AS SALES :FROM :EMPLOYEE T1 INNER JOIN ORDERS T2 :ON T1.EMP_NO = T2.EMP_NO :GROUP BY T1.EMP_NO, T1.NAME :ORDER BY SALES DESC The above may possibly be supported in the very latest releases of Informix engines (OnLine 7.2 and up). However traditionally in Informix you would write: SELECT T1.EMP_NO, T1.NAME, Sum(T2.SALETOTAL) AS SALES FROMEMPLOYEE T1, ORDERS T2 where T1.EMP_NO = T2.EMP_NO GROUP BY T1.EMP_NO, T1.NAME ORDER BY SALES DESC If you want an outer join: Everything the same except: FROM EMPLOYEE T1, outer ORDERS T2 If you want the outer to be a right outer join instead you simply write: FROM ORDERS T2, outer EMPLOYEE T1 :2. Does Informix support quotes for table/column names for names that :may be reserved words or contain spaces? Not before 7.2 at least. And names can't contain spaces in Informix databases. :3. Which sql standard does 7.1 support? SQL92 isn't that the name of it, up to some level and part of some higher levels. I no longer remember this. They are hard at work to support SQL3, and while you are at it building your query builder you might want to look into this. You should also look at what the Illustra engine understands as SQL. This, and possibly more, will be included in Informix Universal Server due out by the end of the year. That will revolutionise what relational databases can do, and how you can use them, but also require major changes in development products to support it properly. In your query builder you will have to support a lot of new ways of writing SQL and an arbitrary number of functions as the users can put any number of advanced functions into the database. You will see jagged return sets (where each returned rows have different number of columns with different datatypes) and lots of other fancy stuff. : :regards :Jeffrey Cooke :100026.3107@compuserve.com While we are at it you should also take a hard look at the construct statement in the Informix 4GL and NewEra languages. These are also SQL query builders, and advanced once that every one who know them and use Delphi complains that Delphi lacks. Nils.Myklebust@ccmail.telemax.no NM Data AS, P.O.Box 9090 Gronland, N-0133 Oslo, Norway My opinions are those of my company