Re: SELECT Syntax

"Monika Rabold S.u.S.E. Linux 5.3" wrote: > > Hi, > considering the SELECT Syntax grammer of INFORMIX you are allowed to use > Subqueries but only in the WHERE Clause. (or did I miss something?) > Now why does the following statement work and give the wrigth result? > QUERY: > ------ > select > entg1.fgnr, > entg1.fkto, > entg1.leistungs_datum, > entg1.anzahl - (select sum( entg2.anzahl_storno) > from tee entg2 > where entg2.fk_tdbnummer = 4779042940 > and entg2.geschaeftsvorfall = "12" > and entg2.fkto = entg1.fkto > and entg2.fgnr = entg1.fgnr > and entg2.leistungs_datum = > entg1.leistungs_datum > and entg2.betrag = entg1.betrag > and entg2.storno_zeitstempel = > entg1.storno_zeitstempel > ) diff_anzahl, > entg1.fk_tdbnummer, > entg1.auftrag_nummer, > entg1.auftrag_niederlass, > entg1.storno_zeitstempel, > entg1.faktgr_zeitstempel, > entg1.anzahl_storno > from tee entg1 > where entg1.fk_tdbnummer =4779042940 > and entg1.geschaeftsvorfall <> "12" > order by entg1.fkto, > entg1.auftrag_niederlass, > entg1.faktgr_zeitstempel, > entg1.fgnr, > entg1.leistungs_datum, > entg1.auftrag_nummer, > entg1.storno_zeitstempel > > Estimated Cost: 2 > Estimated # of Rows Returned: 1 > Temporary Files Required For: Order By > > 1) mora.entg1: INDEX PATH > > Filters: mora.entg1.geschaeftsvorfall != '12' > > (1) Index Keys: fk_tdbnummer geschaeftsvorfall lfdnr > Lower Index Filter: mora.entg1.fk_tdbnummer = 4779042940 > > This is the output of sqexplain.out and as you can see the Subquery is not > listed in this output, but the result is OK. I looked at the documentation > on www.informix.com but could not find anything regarding this "problem". > Is this an undocumented feature or just a programers "mistake"? > > Thanks in advance You missed something. :-) But now that you mention it, I haven't seen any reference to using a subquery in the SELECT clause either. Even the Advanced SQL training course only says "a subquery can be used: In the WHERE or HAVING clause of a SELECT statement In the WHERE or SET clause of an UPDATE statement In the WHERE clause of a DELETE statement " You can do fancy things like SELECT a correlated subquery. In the stores database you can do: SELECT customer_num, ( SELECT MAX(order_num) FROM orders o WHERE o.customer_num = c.customer_num ) last_order FROM customer c As far as I know there is no reason not to use this type of syntax, unless it's a performance issue. ------------------------------------------------------------------------doc & training_doc, this might be something that needs picking up? ------------------------------------------------------------------------ Cheers, -- Mark. +----------------------------------------------------------+-----------+ |Mark D. Stock - Informix SA http://www.informix.com |//////// /| |mailto:mdstock@informix.com http://www.informix.com/idn |///// / //| |http://www.iiug.org +-----------------------------------+//// / ///| | Tel: +27 11 807 0313 |What year 2000 bug? year 2000 bug? |/// / ////| | Fax: +27 838250 2325 |year 2000 bug? year 2000 bug? year |// / /////| |Cell: +27 83 250 2325 |2000 bug? year 2000 bug? year 1900 |/ ////////| +----------------------+-----------------------------------+-----------+