Re: INNER and OUTER JOIN syntax in 7.1

Nils.Myklebust@ccmail.telemax.no (Nils Myklebust) wrote: >How can you even attempt to do this without the fine manuals from >Informix. It's actually fairly easy to do it in Delphi, it's database engine does most of the work for you (getting descriptions of tables, fields, functions etc. handling heterogeneous queries). In the end I simply have to convert the visually constructed query to valid sql for that type of database. They only problem I've run into so far is just small syntax differences like this. >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.) That would be nice, I'll call them up and try but from what I saw at their web site they appear to be heading in the other direction $. >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 >FROM>EMPLOYEE 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 Perfect, I understand it now. >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. Just when I was getting used to it as it is :-( I think I already have the stored functions and triggers (if that's what you mean) covered but jagged sets are going to give me a few late nights. Is there a white paper somewhere you know of that I can read? >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. I know nothing of the construct statement. I don't see how a statement can actually be a query builder ? I'll have to do a little research. Thanks for your comprehensive answer. regards Jeffrey Cooke 100026.3107@compuserve.com