New post
Question
· Aug 3, 2023

Calculate and Update Value on SQL for Existing Data

#ObjectScript #SQL #InterSystems IRIS

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 {
s tEntEpicMoopRecord = ##class(MC.Data.EntEpicMoopFile).MemberIDIndexOpen(pRequest.MemberID)
if $ISOBJECT(tEntEpicMoopRecord) {
s tEntEpicMoopRecord.DateUpdated = +$h
s tSC = tEntEpicMoopRecord.%Save()
}
else {
s tEntEpicMoopRecord = ##class(MC.Data.EntEpicMoopFile).%New()
quit:'$ISOBJECT(tEntEpicMoopRecord)
s tEntEpicMoopRecord.MemberID = pRequest.MemberID
s tEntEpicMoopRecord.LastName = pRequest.LastName
s tEntEpicMoopRecord.FirstName = pRequest.FirstName
s tEntEpicMoopRecord.MiddleInitial = pRequest.MiddleInitial
s tEntEpicMoopRecord.DateofBirth = pRequest.DateofBirth
s tEntEpicMoopRecord.Gender = pRequest.Gender
s tEntEpicMoopRecord.MemberSSN = pRequest.MemberSSN
s tEntEpicMoopRecord.MemberRelationship = pRequest.MemberRelationship
s tEntEpicMoopRecord.SubscriberID = pRequest.SubscriberID
s tEntEpicMoopRecord.SubscriberSSN = pRequest.SubscriberSSN
s tEntEpicMoopRecord.SubscriberLastName = pRequest.SubscriberLastName
s tEntEpicMoopRecord.SubscriberFirstName = pRequest.SubscriberFirstName
s tEntEpicMoopRecord.SubscriberMiddleInitial = pRequest.SubscriberMiddleInitial
s tEntEpicMoopRecord.GroupNumber = pRequest.GroupNumber
s tEntEpicMoopRecord.ClaimID = pRequest.ClaimID
s tEntEpicMoopRecord.AccumID = pRequest.AccumID
s tEntEpicMoopRecord.AccumName = pRequest.AccumName
s tEntEpicMoopRecord.IncrementAmt = pRequest.IncrementAmt
s tEntEpicMoopRecord.TotalAmount = pRequest.TotalAmount
s tEntEpicMoopRecord.HealthPlan = pRequest.HealthPlan
s 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
Luis Angel Pére... · Aug 4, 2023

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.

0 0