Hi Tom,

Hope you are doing good.

I have table_1 -

create table table_1 ( col1 number, col2 number );

table_2 -

create table table_2 ( col3 number, col4 number );

I've inserted some records in both the tables. Now, I want to merge the data in the table_1 into table_2, based on the following condition.
__________________________

merge into table_2 t2
using ( select * from table_1 where col1 > 100 ) t1
on ( t1.col1 = min(t2.col3,t2.col4) )

when matched then

update table_2
set col4 = col2;

_____________________________

This above query gives an error - ORA:00934 - Group function not allowed here.

when this merge statement is modified using the case statement - it works fine.

____________

merge into table_2 t2
using ( select * from table_1 where col1 > 100 ) t1
on ( t1.col1 = case when ( t2.col3 >t2.col4 ) then t2.col4
else t2.col3
end)

when matched then

update table_2
set col4 = col2;


_________________

is there any difference in how the above query functions when compared to the intent of the first query?

Please explain.

Thanks!

DOH..!! Sorry!

Least() - I Meant to use exactly that!!

I will ensure I get sober before I trouble you the next time. :)

Thanks Again!

hi Tom,
i tried the approach mentioned by you.
below is my MERGE statement.

MERGE INTO Rftm_Pidwise_Revenue_Dtls TRG
USING (SELECT SUM(ltd) EFF,project_id pi
       FROM rftm_time_entry_2
       WHERE project_id IN (select pid 
                            from rftm_pid_details
                                where ideal_ref_no_lv ='140014'
                                and version_number= (select max(version_number) from rftm_pid_details))
       AND version_number= (select max(version_number) from rftm_time_entry_2)
       GROUP BY project_id) SRC
ON
(TRG.pid= SRC.pi)
WHEN MATCHED THEN UPDATE
SET TRG.efforts_prior_qtr= SRC.EFF

but the rows are not getting updated. However, when i change the assignment in the SET clause, say i changed it to 1, it works.
but i want to use the sum for each PID coming under the ideal number '140014'.