temp table in from clause
Hi I am using informix v5. Is it possible in this to create temp table in the from clause of another query and use it in the same query? something like this? select a.* from fmcapdgr a, (select user_id, count(*) col_cnt,min(rec_serial) serial from fmcapdgr group by user_id having count(*) > 1)fmc_count b where a.user_id = b.user_id and a.rec_status='A' order by a.user_id Please let me know if any version of informix supports this type query making? It seems other RDBMS like sybase or oracle supports these. regards Deba
Deba,
This feature is called derived tables and it is first supported in the from
clause if a select statement by Informix in the latest Informix Dynamic
server version 11.50xC1 released May 6, 2008. Online 5.xx does not support
this. You will have to:
select user_id, count(*) col_cnt, min(rec_serial) serial
from fmcapdgr
group by user_id
having count(*) > 1
into temp fmc_count;
select a.* from
fmcapdgr a, fmc_count b
where a.user_id = b.user_id
and a.rec_status='A'
order by a.user_id;
Note that using a type name (ie serial) to name a column, even in a temp
table, is a dubious practice. While it will likely work, it can sometimes
get you into trouble.
On Thu, May 22, 2008 at 5:15 AM, debadatta <mishra.dd@gmail.com> wrote:
> Hi
>
> I am using informix v5. Is it possible in this to create temp table in the
> from clause of another query and use it in the same query?
>
> something like this?
>
> select a.* from
> fmcapdgr a,
> (select user_id, count(*) col_cnt,min(rec_serial) serial
> from fmcapdgr group by user_id
> having count(*) > 1)fmc_count b
> where a.user_id = b.user_id
> and a.rec_status='A'
> order by a.user_id
>
> Please let me know if any version of informix supports this type query
> making? It seems other RDBMS like sybase or oracle supports these.
>
> regards
> Deba
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--
Art S. Kagel
Oninit (www.oninit.com)
IIUG Board of Directors (art@iiug.org)
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 organization
with which I am associated either explicitely or implicitely. Neither do
those opinions reflect those of other individuals affiliated with any entity
with which I am affiliated nor those of the entities themselves.