Re: Select In From Clause
}>From ilist@rmy.emory.edu Tue Jun 24 17:29:30 1997
}>Received: from rmy.rmy.emory.edu by hobbes.dbintellect.com via ESMTP (950413.SGI.8.6.12/930416.SGI.AUTO)
}> for <settler@dbintellect.com> id RAA11711; Tue, 24 Jun 1997 17:29:28 -0600
}>Received: (from ilist@localhost) by rmy.rmy.emory.edu (8.7.1/8.7.1) id QAA04639 for informix-list-out; Tue, 24 Jun 1997 16:50:02 -0400 (EDT)
}>From: Jeremy Rickard <Jeremy@jbdr.demon.co.uk>
}>Message-Id: <V0YZKWASHBszEwlP@jbdr.demon.co.uk>
}>Subject: Re: Select In From Clause
}>Date: Tue, 24 Jun 1997 19:28:34 +0100
}>Reply-To: Jeremy Rickard <Jeremy@jbdr.demon.co.uk>
}>Organization: United Systems (UK) Ltd.
}>Sender: informix-list-owner@rmy.emory.edu
}>To: informix-list@rmy.emory.edu
}>X-Informix-List-Id: <news.39612>
}>
}>In article <5onbvc$a0t@masters0.InterNex.Net>, Chez David
}><dmitchell@SagentTech.com> writes
}>
}>>Anyone know if Informix ( 7.x ) supports SQL of this form? SQL Server and
}>>Oracle seem to.
}>>
}>>SELECT
}>>T0.C0, T1.C0
}>>FROM
}>>( SELECT JOB_ID AS C0 FROM JOBS ) T0,
}>>( SELECT JOB_ID AS C0 FROM EMPLOYEE ) T1
}>>WHERE
}>>T0.C0 = T1.C0
}>
}>Does anyone want it to?!
}>
}>Personally, I think this could lead to some very ugly SQL queries. Can
}>you think of any examples that couldn't be re-written as a "normal"
}>query without looking at least as elegant?
}>
}>If this is really needed then I think it would be "prettier" to define
}>T0 and T1 as temporary tables (using WITH or whatever construct
}>is/becomes the standard for this).
}>
}>What's the general opinion on this form?
}>--
}>Jeremy Rickard
}>
Actually, you can do this type of thing:
select salary_amt
,(select sum(salary_amt) from emp) totsal
from emp;
...and you might want to if, as illustrated above,
you wanted to get an individual's salary amount
and total salary in order to calculate percent of
total (or something like that anyway).
-------------------------------------------------------------------
Jeff Craig | dbINTELLECT Technologies, Inc.
Senior Consultant | Golden, CO
| (303) 275-6954 ext. 8008
| email: settler@dbintellect.com
| fax: (303) 275-2134
-------------------------------------------------------------------