Re: VIEW who works in SQL Server but not in ORACL

"Stef" <toto_at_titi.com> wrote in message news:<42396d5f$0$19356$8fcfb975_at_news.wanadoo.fr>...
> Thank tou for your answer but it still doesn't work. I said something wrong,
> it's ORACLE 8i version.
> Here is the code ( the column Acquit doesn't exist in my table, it's a self
> made in the request)
> Sorry for my English
>
> prd244pr > SELECT TIMESTMP , TRANSTYPE , TAGNME, DSCRPTN,
> 2 (SELECT TAGNAME FROM (
> 3 SELECT TAGNAME FROM RSV_ALM_LOG WHERE
> 4 TRANSTYPE = 'Acked' AND TIMESTMP > T1.TIMESTMP
> 5 AND TAGNAME = T1.TAGNAME)
> 6 WHERE ROWNUM = 1) AS ACQUIT
> 7 FROM RSV_ALM_LOG T1;
> AND TAGNAME = T1.TAGNAME
>
> Error at line 5:
> OAR-00904 : invalid column name
>
>
> Oracle 8i Enterprise Edition Release 8.1.7.4.1 - Production
> PL/SQL Release 8.1.7.4.0 - Production
> CORE 8.1.7.2.1 - Production
> TNS for 32 bit windows : Version 8.1.7.4.0 - Production
> NLSRTL Version 3.4.1.0.0 - Production
>
>
> What can I do.
>
> Thanks

Upgrade to Oracle 9i, as the functionality you seem to need is not available in Oracle 8i.
The other alternative would be to move the inline view to the from clause.

from RSV_ALM_LOG T1,
(
SELECT TAGNAME FROM RSV_ALM_LOG WHERE
TRANSTYPE = 'Acked' AND TIMESTMP > T1.TIMESTMP AND TAGNAME = T1.TAGNAME)
WHERE ROWNUM = 1) Of course learning *proper* sql instead of this ugly hacked code is most recommended.

--
Sybrand Bakker
Senior Oracle DBA