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
Copying stored procedures between databases on the same SQL Server 2005 can be done through scripting the stored procedures with this method.continue reading
Find how to query in SQL Server to get all database names created by users.continue reading
Learn why using the sp_OACreate stored procedure with a SQL Server 2005 trigger, is likely to result in a permissions problem.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.