SELECT syntax with alias
Hi All,
In Oracle I can do the following:
SELECT a.k, a.l, b.x, b.y from blabla a,
(select x,y from z wher x>5) b;
How can I use a subquery's result as a table in Informix?
Do be more specific my complete SQL is:
SELECT distinct b.project, b.event, max(logtime), a.user_info
FROM hvsuser a,
(select elem_id,project,event into bla from hvslog_elem where
event='ProjectStarted' or
event='ProjectEnded') b
WHERE
logtime between datetime(1999-01-24 15:50) year to minute and
datetime(1999-01-24 15:52) year to minute
and a.elem_id = b.elem_id
GROUP BY project,event, user_info;
I get an error on the "b" table.
What is the correct syntax?
Thanks,
Rottem
-----------== Posted via Deja News, The Discussion Network ==----------
http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own
rottem@veon.com wrote:
> Hi All,
>
> In Oracle I can do the following:
>
> SELECT a.k, a.l, b.x, b.y from blabla a,
> (select x,y from z wher x>5) b;>
> How can I use a subquery's result as a table in Informix?
> Do be more specific my complete SQL is:
>
> SELECT distinct b.project, b.event, max(logtime), a.user_info
> FROM hvsuser a,
> (select elem_id,project,event into bla from hvslog_elem where
> event='ProjectStarted' or>
Check the syntax in your select statement for alias b. You are selecting
three columns into one variable. You might want to try "..into bla.elem_id,
bla.project, bla.event from..". Or you could put the "..into bla.." at the
end of the select. Thus, selecting into a temp table "bla".
> event='ProjectEnded') b
> WHERE
> logtime between datetime(1999-01-24 15:50) year to minute and
> datetime(1999-01-24 15:52) year to minute
> and a.elem_id = b.elem_id
> GROUP BY project,event, user_info;
>
> I get an error on the "b" table.
> What is the correct syntax?
>
> Thanks,
>
> Rottem
>
> -----------== Posted via Deja News, The Discussion Network ==----------
> http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own
Mark Roach wrote:
> rottem@veon.com wrote:
>
> > Hi All,
> >
> > In Oracle I can do the following:
> >
> > SELECT a.k, a.l, b.x, b.y from blabla a,
> > (select x,y from z wher x>5) b;> >
> > How can I use a subquery's result as a table in Informix?
> > Do be more specific my complete SQL is:
> >
> > SELECT distinct b.project, b.event, max(logtime), a.user_info
> > FROM hvsuser a,
> > (select elem_id,project,event into bla from hvslog_elem where
> > event='ProjectStarted' or> >
>
> Check the syntax in your select statement for alias b. You are selecting
> three columns into one variable. You might want to try "..into bla.elem_id,
> bla.project, bla.event from..". Or you could put the "..into bla.." at the
> end of the select. Thus, selecting into a temp table "bla".
>
Correction: that would be "..into temp bla" at the end to select into a temp
table "bla".
> > event='ProjectEnded') b
> > WHERE
> > logtime between datetime(1999-01-24 15:50) year to minute and
> > datetime(1999-01-24 15:52) year to minute
> > and a.elem_id = b.elem_id
> > GROUP BY project,event, user_info;
> >
> > I get an error on the "b" table.
> > What is the correct syntax?
> >
> > Thanks,
> >
> > Rottem
> >
> > -----------== Posted via Deja News, The Discussion Network ==----------
> > http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own
>
> ------------------------------------------------------------------------
>
> Mark Roach <mroach@bju.IT5_PO>
> Informix Programmer
> Bob Jones University
>
> Mark Roach
> Informix Programmer <mroach@bju.IT5_PO>
> Bob Jones University
> Netscape Conference Address
> Netscape Conference DLS Server
> Additional Information:
> Last Name Roach
> First Name Mark
> Version 2.1