On Jun 22, 1:52=A0pm, "Art Kagel" <art.ka...@[EMAIL PROTECTED]
> wrote:
> IDS 10.00 does NOT sup****t derived tables (ie a select in the FROM
clause
> acting as a table). =A0That syntax is first sup****ted in IDS 11.10.
=A0Yo=
u can
> unwind this to a simple query as:
>
> SELECT =A0 =A0 loc.location, loc.code, acc.descr, count(loc.reqd_for) AS
> CountReqdfor
> FROM =A0 =A0 =A0informix.loc_accessorials loc, informix.accessorial acc
> WHERE =A0 =A0loc.code=3Dacc.code =A0AND loc.code =3D '1018'
> GROUP BY loc.location, loc.code, acc.desc;
>
> It is VERY rare that you cannot turn a derived table reference into a
sim=
ple
> query or join. =A0The most common case where you cannot is when you need
=
to
> perform other operations on the results of an aggregation. =A0In those
ca=
ses,
> Informix provides temp tables, which is really how derived tables are
> implemented when they cannot be folded automatically by the engine into
a
> simpler query (IDS 11.50 tries to do that for you).
>
> Art
>
> On Fri, Jun 20, 2008 at 9:57 AM, saad_tariq via DBMonster.com <
>
> u43...@[EMAIL PROTECTED]
> wrote:
> > SELECT =A0 exp.*
> > FROM =A0 =A0 (SELECT =A0 loc.location, loc.code, acc.descr,
count(loc.r=
eqd_for)
> > AS
> > CountReqdfor
> > FROM =A0 =A0 =A0informix.loc_accessorials loc, informix.accessorial
acc
> > WHERE =A0 =A0loc.code=3Dacc.code =A0AND loc.code =3D '1018'
> > GROUP BY loc.location, loc.code, acc.descr) exp
>
> --
> Art S. Kagel
> Oninit (www.oninit.com)
> IIUG Board of Directors (a...@[EMAIL PROTECTED]
)
>
> Disclaimer: Please keep in mind that my own opinions are my own opinions
=
and
> do not reflect on my employer, Oninit, the IIUG, nor any other
organizati=
on
> with which I am associated either explicitly or implicitly. Neither do
th=
ose
> opinions reflect those of other individuals affiliated with any entity
wi=
th
> which I am affiliated nor those of the entities themselves.
I completely concur I think if you search for derived tables in this
news group I have several rants on the subject that should just be
referenced in the future. ;-)
Here is one:
http://groups.google.com/group/comp.databases.informix/browse_thread/thread=
/5fa50a300d9b6e59/e1d9624e38bf9a67?lnk=3Dgst&q=3Dderived+tables#e1d9624e38b=
f9a67
|
