Written by

Question Warren Oyco · Aug 3, 2023

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 = $$$OKTry {
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

Add reply

Comments

Luis Angel Pérez Ramos · 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