CASE UPDATE Query

Question

Post reply

CASE UPDATE Query

pop

SSCrazy

Points: 2130
More actions
May 20, 2005 at 2:32 pm

#66546

I have the following query in Access.
UPDATE [RiskSysSum of Source ClaimType] SET [RiskSysSum of Source ClaimType].FinLagCatagory = FinanceLagCatagory([ClaimType]);
FinanceLagCatagory(Claimtype) is a public function working off the Claimtype field in the same table. The function is as follows:
Public Function FinanceLagCatagory(ClaimType As String) As String
On Error GoTo FinLagCatagory_Error
XClaimType = Trim(Nz(ClaimType, ""))
Select Case XClaimType
Case "OP", "SPU"
XFinClaimCat = "OP/SPU"
Case "SNF", "RHB"
XFinClaimCat = "SUBACUTE"
Case "PCP", "PH", "SP", "SPI", "SPO"
XFinClaimCat = "Spec"
Case "IP", "SBC", "SC"
XFinClaimCat = "IP"
Case "EM", "ER"
XFinClaimCat = "EREM"
Case "DME", "HC", "OTA", "TSP", ""
XFinClaimCat = "Other"
Case "Man"
XFinClaimCat = "Manual Check"
Case Else
XFinClaimCat = "UNK-" & XClaimType
End Select
FinanceLagCatagory = XFinClaimCat
Exit Function
FinLagCatagory_Error:
MsgBox Str(Err.Number) & " " & Err.Description
I wrote the following SQL Query, but get an error saying: "Subquery returned more than 1 value."
UPDATE [RiskSysSum of Source ClaimType]
SET [RiskSysSum of Source ClaimType].FinLagCatagory =
(SELECT
CASE
WHEN ClaimType IN ('OP','SPU') THEN 'OP/SPU'
WHEN ClaimType IN ('SNF','RHB') THEN 'SUBACUTE'
WHEN ClaimType IN ('PCP', 'PH', 'SP', 'SPI', 'SPO') THEN 'Spec'
WHEN ClaimType IN ('IP', 'SBC', 'SC') THEN 'IP'
WHEN ClaimType IN ('EM', 'ER') THEN 'EREM'
WHEN ClaimType IN ('DME', 'HC', 'OTA', 'TSP', '') THEN 'Other'
WHEN ClaimType IN ('Man') THEN 'Manual Check'
ELSE 'UNK-' + ClaimType
END
FROM [RiskSysSum of Source ClaimType])
Am I going about this the wrong way, or is something wrong in my syntax? Thanks.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

noeld SSC Guru Points: 96590 More actions · Answer 1

UPDATE [RiskSysSum of Source ClaimType]

SET FinLagCatagory =

(CASE

WHEN ClaimType IN ('OP','SPU') THEN 'OP/SPU'

WHEN ClaimType IN ('SNF','RHB') THEN 'SUBACUTE'

WHEN ClaimType IN ('PCP', 'PH', 'SP', 'SPI', 'SPO') THEN 'Spec'

WHEN ClaimType IN ('IP', 'SBC', 'SC') THEN 'IP'

WHEN ClaimType IN ('EM', 'ER') THEN 'EREM'

WHEN ClaimType IN ('DME', 'HC', 'OTA', 'TSP', '') THEN 'Other'

WHEN ClaimType IN ('Man') THEN 'Manual Check'

ELSE 'UNK-' + ClaimType

END)

* Noel

pop SSCrazy Points: 2130 More actions · Answer 2

WOW ! Thanks for the quick reply Noel ! That did it. Your help is always great.

-Pat

noeld SSC Guru Points: 96590 More actions · Answer 3

noeld

SSC Guru

Points: 96590

May 20, 2005 at 3:17 pm

#560359

You are more than welcome

* Noel