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

From: <fitzjarrell_at_cox.net>
Date: 6 Jan 2006 14:55:16 -0800
Message-ID: <1136588116.269785.78590@g44g2000cwa.googlegroups.com>

Comments embedded.
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
>

Removing the 'AS' before the table alias gets this code to execute in Oracle, however it takes two passes to actually populate the column as you describe:

SQL> create table dtatemp (ATF_ID INT NOT NULL, TempID INT NULL);

Table created.

SQL> INSERT INTO DTATemp values (92,NULL);

1 row created.

SQL> INSERT INTO DTATemp values (94,NULL);

1 row created.

SQL> INSERT INTO DTATemp values (95,NULL);

1 row created.

SQL> INSERT INTO DTATemp values (96,NULL);

1 row created.

SQL> INSERT INTO DTATemp values (93,NULL);

1 row created.

SQL>
SQL> commit;

Commit complete.

SQL>
SQL> UPDATE DTATemp
  2 SET TempID =
  3 CASE WHEN DTATemp.TempID IS NULL

  4      THEN 0
  5      ELSE (SELECT COUNT(TempID) FROM DTATemp F1 WHERE F1.ATF_ID <

  6 DTATemp.ATF_ID)
  7 END
  8 /

5 rows updated.

SQL>
SQL> select * From dtatemp;

    ATF_ID TEMPID
---------- ----------

        92          0
        94          0
        95          0
        96          0
        93          0

SQL>
SQL> UPDATE DTATemp
  2 SET TempID =
  3 CASE WHEN DTATemp.TempID IS NULL

  4      THEN 0
  5      ELSE (SELECT COUNT(TempID) FROM DTATemp F1 WHERE F1.ATF_ID <

  6 DTATemp.ATF_ID)
  7 END
  8 /

5 rows updated.

SQL>
SQL> select * From dtatemp;

    ATF_ID TEMPID
---------- ----------

        92          0
        94          2
        95          3
        96          4
        93          1

SQL> The NULL Condition evaluates to TRUE for all rows since the column is initially NULL; as such the initially populated values are all 0. A second pass is required to populate values as you illustrated (shown below, and in the example above):

> 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.
>

CASE is not available in Oracle 8/8i so you'll need to use DECODE() or some creative PL/SQL to accomplish this in those versions.

> 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

David Fitzjarrell Received on Fri Jan 06 2006 - 16:55:16 CST