Updat after Inserting

[mtechnocrat]

Hi

I am inserting Data into OCI satge , after inserting I am dating the same data selecting SQL , after tab , its working fine , but in log file it is throwing a warning like " invalid SQL statement " , b'coz of this warning my sequnce is aborting , can u suggest me how to avoid this warning ?


thanks in advance

[Sainath.Srinivasan]

Duplicate post.

[ray.wurlod]

Get the full error message, including the SQL statement that is invalid, and fix it.

[mtechnocrat]

Get the full error message, including the SQL statement that is invalid, and fix it.

CopyOfEdu..Oracle_OCI_4: ORA-00900: invalid SQL statement


SQL Statement :

Update EDU_AGG_T
Set Imm_Row = 'N'
where ACC_NO in
(select ACC_NO From (SELECT MAX(ACC_NO) ACC_NO, ACC_KEY
from EDU_AGG_T group by ACC_KEY) )

Update is working correctly , but job is throwing above warnig , b'coz of this sequnce is aborting , need help


Thanks

[Sainath.Srinivasan]

Your inner-most SQL has 2 ACC_NO. Also it has 2 fields but the where clause attempts to match only one.

[mtechnocrat]

Your inner-most SQL has 2 ACC_NO. Also it has 2 fields but the where clause attempts to match only one.

I tried with one ACC_NO ( by removing alias name ) still same issue , I am using same logic in other componnets , there is no issue with them , any suggestions ??

Thanks

[Sainath.Srinivasan]

Try

Code: Select all

Update EDU_AGG_T 
Set Imm_Row = 'N' 
where ACC_NO in 
(SELECT MAX(ACC_NO) ACC_NO_KEY from EDU_AGG_T ) 

Or if you want to add both the Acc_Key also, then try

Code: Select all

Update EDU_AGG_T 
Set Imm_Row = 'N' 
where (ACC_NO, ACC_KEY) in 
(SELECT MAX(ACC_NO) ACC_NO2, ACC_KEY 
from EDU_AGG_T group by ACC_KEY )