hey

i got a problem once again :D a little info first: im trying to copy data from one table to an other table(structure is the same). now one cell needs to be incremented, beginns per group at 1 (just like a histroy).

i have this table:

create table My_Test/My_Test2 (
my_Id Number(8,0),
my_Num Number(6,0),
my_Data Varchar2(100));

(my_Id, my_Num is a nested PK)

if i want to insert a new row, i need to check if the value in my_id already exists.
if this is true then i need to use the next my_Num for this Id.

i have this in my Table:

My_Id   My_Num    My_Data
1       1         'test1'
1       2         'test2'
2       1         'test3'

if i add now a row for my_Id 1, the row would look like this: i have this in my Table:

My_Id   My_Num    My_Data
1       3         'test4'

this sounds pretty easy ,now i need to make it in a SQL and on SQL Server i had the same problem and i used this:

Insert Into My_Test (My_Id,My_Num,My_Data)
SELECT my_Id,
  (
    SELECT
      CASE (
          CASE MAX(a.my_Num)
            WHEN NULL
            THEN 0
            Else Max(A.My_Num)
          END) + b.My_Num
        WHEN NULL
        THEN 1
        ELSE (
          CASE MAX(a.My_Num)
            WHEN NULL
            THEN 0
            Else Max(A.My_Num)
          END) + b.My_Num
      END
    From My_Test A
    where my_id = 1
  )
  ,My_Data
From My_Test2 B
where my_id = 1;

this Select gives null back if no Rows are found in the subselect

is there a way so i could use max in the case? and if it give null back it should use 0 or 1?

greets Auro