基于多个case语句和连接更新基表

问题描述

 

我有一个包含声明的基表，我需要更新该表中的StatusId。此状态ID需要根据"已接受"和"已拒绝"状态进行更新，并检查此声明ID是否位于多个表中。 

基表有2个字段：  claimID，SubmissionID 

此基表需要根据以下两个条件进行更新： 

条件1：检查基表中的claimID是否存在在查询表中名为ResponseClaim_Lookup（ResponseClaimID = claimID） 

   如果存在 然后检查查找表field_number字段line_number ='000' 然后将基表中的StatusID更新为'A001' 

   如果存在，请检查是否 查找表字段field_number ='111' 然后将基表中的StatusID更新为'B002'

条件2：如果ResponseClaim_Lookup中不存在ClaimID，则检查RejectedClaim_Lookup表中的ClaimID（rejectedclaimID = ClaimId） 

   如果存在 然后检查字段line_number ='222' 然后将Base表中的StatusID更新为'Rej_001' 

   如果存在，则检查field_number ='333' 然后将基表中的StatusID更新为'Rej_003'

任何示例方案都会有所帮助。 

谢谢， 

Vishal。 

解决方案

Hi , 

I have a base table containing claims and I need to update StatusId's in that table. This status ID's need to be updated based on 'Accepted' and 'Rejected' status and checking if this claimID resides in several tables. 

Base table has 2 fields :  claimID,SubmissionID 

This base table needs to be updated based on 2 conditions : 

Condition 1: Check if claimID in base table exists in lookup table called ResponseClaim_Lookup ( ResponseClaimID = claimID) 

   if exist  then check if lookup table field_number field line_number = '000'  then update StatusID in Base table to 'A001' 

   If exists then check if  Lookup table field field_number = '111'  Then update StatusID in base table to 'B002'

Condition 2: if ClaimID does not exist in ResponseClaim_Lookup , then check for ClaimID in RejectedClaim_Lookup table ( rejectedclaimID = ClaimId) 

   if exist  then check if field line_number = '222'  then update StatusID in Base table to 'Rej_001' 

   If exists then check if field_number = '333'  Then update StatusID in base table to 'Rej_003'

Any example scenarios would help. 

Thank you , 

Vishal. 

解决方案

这篇关于基于多个case语句和连接更新基表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！