Re: Translate SQL Server CASE/ELSE (SELECT ..) into Oracle SQL?

Jan Doggen wrote:
> Can someone translate this MS SQL Server statement into Oracle PL/SQL?
> The purpose is:
>
> Table DTATemp initially has structure and content:
> (actually, there are more fields, but these are two relevant ones)
> ATF_ID TempID
> 92 NULL
> 94 NULL
> 95 NULL
> 96 NULL
> 93 NULL
> I want TempID filled with sequential values, starting with 0, in the order
> of ATF_ID
> (these values are not necessarily sequential, there may be gaps like
> 1,2,4,5,6).
> In MS SQL Server this does the trick:
>
> UPDATE DTATemp
> SET TempID =
> CASE WHEN DTATemp.TempID IS NULL
> THEN 0
> ELSE (SELECT COUNT(TempID) FROM DTATemp AS F1 WHERE F1.ATF_ID <
> DTATemp.ATF_ID)
> END
>
> The result is:
> ATF_ID TempID
> 92 0
> 94 2
> 95 3
> 96 4
> 93 1
>
> How do I do this in Oracle?
> I keep running into the (SELECT COUNT...) not being possible or something...
> This should work under Oracle 8 and up.
>
> Here's the SQL to create the test table:
> create table dtatemp (ATF_ID INT NOT NULL, TempID INT NULL);
> INSERT INTO DTATemp values (92,NULL);
> INSERT INTO DTATemp values (94,NULL);
> INSERT INTO DTATemp values (95,NULL);
> INSERT INTO DTATemp values (96,NULL);
> INSERT INTO DTATemp values (93,NULL);
>
> Thanks very much in advance
> Jan Doggen

www.psoug.org
click on Morgan's Library
click on CASE

The syntaxes are essentially the same between the two products.

-- 
Daniel A. Morgan
http://www.psoug.org
damorgan_at_x.washington.edu
(replace x with u to respond)