I have an update statement that works fine straight up, but fails when included in a stored procedure. Can you help?
SET Deny_Cd = (SELECT Deny_Code
WHERE claim = x.Claim
AND Edit_Code = (SELECT MIN(SUBSTRING(Edit_Code,1,1)+Convert(char(3),CONVERT(INTEGER,SUBSTRING(Edit_Code,2,LEN(Edit_Code)-1))))
WHERE claim = x.claim
AND deny_code != null)),
Checkk_Date = getdate(),
Change_Date = getdate(),
Change_Init = 'CISEdtTx'
FROM CPSA_CIS_Edit_Processing x
WHERE Med_Claims.Claim = x.Claim
AND x.Process_Action = 'Deny'
AND x.Deny_Code != null
Table data is:
Claim Episode Edit_Code Edit_Action Deny_Code Process_Action
05207E20K4 F78N51 F78 Deny D490
05207E20K4 F78N51 N51 Deny D051 Deny
05226E90S7 N51F75 F75 Deny D480
05226E90S7 N51F75 N51 Deny D051 Deny
05226E93EG N53F77 F77 Deny D491
05226E93EG N53F77 N53 Deny D430 Deny
05226EC146 F26N30 F26 None [NULL]
05226EC146 F26N30 N30 Deny D Deny
05226EC1C5 N63N207 N207 Deny D207
05226EC1C5 N63N207 N63 Deny D420 Deny
05226EC2FU N63F83 F83 Deny D
05226EC2FU N63F83 N63 Deny D420 Deny
Also tried to use a case statement for the set and had the same problem.