Question
· Aug 3

Calculate and Update Value on SQL for Existing Data

Hi,

Any recommendations on how i can add a logic if an incoming record that is already existing in the table but different total amount value. I need to subtract the totalamt value on the input file vs the totalamt value on the table and update the table with the difference of totalamt. Any help is much appreciated. Thanks!

 

Set tSC = $$$OK Try {
tEntEpicMoopRecord = ##class(MC.Data.EntEpicMoopFile).MemberIDIndexOpen(pRequest.MemberID)
if $ISOBJECT(tEntEpicMoopRecord) {
tEntEpicMoopRecord.DateUpdated = +$h
tSC = tEntEpicMoopRecord.%Save()
}
else {
tEntEpicMoopRecord = ##class(MC.Data.EntEpicMoopFile).%New()
quit:'$ISOBJECT(tEntEpicMoopRecord)
tEntEpicMoopRecord.MemberID = pRequest.MemberID
tEntEpicMoopRecord.LastName = pRequest.LastName
tEntEpicMoopRecord.FirstName = pRequest.FirstName
tEntEpicMoopRecord.MiddleInitial = pRequest.MiddleInitial
tEntEpicMoopRecord.DateofBirth = pRequest.DateofBirth
tEntEpicMoopRecord.Gender = pRequest.Gender
tEntEpicMoopRecord.MemberSSN = pRequest.MemberSSN
tEntEpicMoopRecord.MemberRelationship = pRequest.MemberRelationship
tEntEpicMoopRecord.SubscriberID = pRequest.SubscriberID
tEntEpicMoopRecord.SubscriberSSN = pRequest.SubscriberSSN
tEntEpicMoopRecord.SubscriberLastName = pRequest.SubscriberLastName
tEntEpicMoopRecord.SubscriberFirstName = pRequest.SubscriberFirstName
tEntEpicMoopRecord.SubscriberMiddleInitial = pRequest.SubscriberMiddleInitial
tEntEpicMoopRecord.GroupNumber = pRequest.GroupNumber
tEntEpicMoopRecord.ClaimID = pRequest.ClaimID
tEntEpicMoopRecord.AccumID = pRequest.AccumID
tEntEpicMoopRecord.AccumName = pRequest.AccumName
tEntEpicMoopRecord.IncrementAmt = pRequest.IncrementAmt
tEntEpicMoopRecord.TotalAmount = pRequest.TotalAmount
tEntEpicMoopRecord.HealthPlan = pRequest.HealthPlan
tSC = tEntEpicMoopRecord.%Save()
} Set tTarget = ..Target

}

Product version: IRIS 2022.1
$ZV: IRIS for UNIX (IBM AIX for System Power System-64) 2022.1.3 (Build 670_1U) Tue Jul 18 2023 14:26:46 EDT [HealthConnect:3.5.0-1.m1] [HealthConnect:3.5.0-1.m1]
Discussion (1)2
Log in or sign up to continue

You can add after the %Save() command something like this:

set sqlUpdateMoodRecord = "UPDATE MoopRecord SET TotalAmount = 
        CASE WHEN TotalAmount > ? THEN TotalAmount - ? WHEN TotalAmount < ? THEN ? -TotalAmount
            ELSE TotalAmount WHERE %ID = ?"
set statementUpdateMoodRecord = ##class(%SQL.Statement).%New()
set statusUpdateMoodRecord = statementUpdateMoodRecord.%Prepare(sqlUpdateMoodRecord)
if ($$$ISOK(statusUpdateMoodRecord)) {
    set resultSet = statementUpdateMoodRecord.%Execute(pRequest.TotalAmount, 
                        pRequest.TotalAmount, pRequest.TotalAmount, pRequest.TotalAmount,
                        tEntEpicMoopRecord.%ID)
}

Probably that code is not going to work...but the idea is to launch an UPDATE with a condition in the SET.