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

From: Jan Doggen <j.doggen_at_BLOCKqsa.nl>
Date: Fri, 6 Jan 2006 17:12:04 +0100
Message-ID: <43be96d5$0$11062$e4fe514c@news.xs4all.nl>

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 Received on Fri Jan 06 2006 - 10:12:04 CST