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
