SET Deny_Cd = (SELECT Deny_Code FROM CPSA_CIS_Edit_Processing 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)))) FROM CPSA_CIS_Edit_Processing 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
I also tried to use a case statement for the set and had the same problem.
Thing IS NOT NULL
Dig deeper on SQL Server Stored Procedures
Related Q&A from Greg Low
Learn why using the sp_OACreate stored procedure with a SQL Server 2005 trigger, is likely to result in a permissions problem.continue reading
Find how to query in SQL Server to get all database names created by users.continue reading
A SQL Server application that needs user permission to create and modify tables in SQL Server 2005 may be handled best via a schema.continue reading
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.