Re: INNER and OUTER JOIN syntax in 7.1

cooke@pnc.com.au (Jeffrey Cooke) wrote: :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 $. That may be so, but a set of manuals shouldn't be a problem for a purpose like yours. :>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? Sorry I don't. Informix haven't yet understood the vast need of this type of whitepapers. Again a set of manuals, this time on the Illustra database might help you. As to stored functions remember they are user defined, there may be any number of them, some in one installation, another set in another installation of the same engine. Your select may return HTML pages, maps, pictures, all kinds of strange stuff. So far I know very little about how this all works, but am trying to learn to be able to use the new functionallity effectively. :>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. Well it was an overstatement that it was a query builder. It only builds the where part of a query, but that's often the hard part. The statement is used for query by form type queries that are simple enough that any end user can learn to use them in no time. Essentially there is a screen form whith fields for all the columns you want the end user to be able to query on (usually all columns of one or more tables). The user can then type in things like a value (generating fieldname = value), >value (generating fieldname > value) and so on. All the usual comparison operatiors can be typed in. | (vertical bar) is used for or (within one field), : for values between. There is nothing like such a front end to allow the user to query for data from a database, be it for the purpose of look at/select rows on screen or make a report, for regular reasonably simple queries. It's fast and requires virtually no learning. (For complex queries something else is needed.) The Informix construct statement doesn't support things like A*|B* (would generate: fieldname matches "A*" or fieldname matches "B*" or in standard SQL syntax: fieldname like "A%" or fieldname like "B%") which you should of course. :Thanks for your comprehensive answer. Keep asking, and we may get another good product for our selected database engine. Nils.Myklebust@ccmail.telemax.no NM Data AS, P.O.Box 9090 Gronland, N-0133 Oslo, Norway My opinions are those of my company