Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
dawnh
Starting Member
3 Posts |
 Posted - 2012-02-06 : 10:45:49
|
| Hello,I have a stored procedure that does not seem to recognize the "val -= 'Found in-network provider'. This is a part of the stored procedure: WHEN val = 'network status given' OR val = 'Found out-of-network provider' OR val = 'Found in-network provider' THEN 'Network status given'Any ideas would be appreciated.Thank you in advance. |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-02-06 : 10:58:41
|
quote:
Originally posted by dawnh
Hello,I have a stored procedure that does not seem to recognize the "val -= 'Found in-network provider'. This is a part of the stored procedure: WHEN val = 'network status given' OR val = 'Found out-of-network provider' OR val = 'Found in-network provider' THEN 'Network status given'Any ideas would be appreciated.Thank you in advance.
Assuming the statement is indeed a -= operator, it is not valid for strings. += will concatenate, but -= is invalid.If it is the WHEN clause that you showed, does it show an error message? If so you perhaps need and END keyword or ELSE clause at the end. |
 |
|
|
dawnh
Starting Member
3 Posts |
Posted - 2012-02-06 : 14:02:40
|
| Hello,There isn't an error that is produced. Please see the entire stored procedure below:Thank youSELECT DISTINCT TOP (100) PERCENT VAL AS resolution_cd, SUB_TYPE AS sr_area, CASE WHEN val = 'appointment scheduled' OR val = 'transportation arranged' OR val = 'coordination of care' OR val = 'DBM Appt. Scheduled' OR val = 'medical records obtained' THEN 'Access to Care' WHEN val = 'IDM Personal Touch' THEN 'IDM Personal Touch' WHEN val = 'appeal overturned' OR val = 'appeal upheld' OR val = 'appeal/grievance filed' OR val = 'Grievance filed' OR val = 'referred to d.o.i.' OR val = 'Client/Plan exception' OR val = 'grievance discussed' THEN 'Appeals/Grievances' WHEN val = 'benefits discussed' OR val = 'benefits education given' OR val = 'comm ins info given' OR val = 'healthcare info given' OR val = 'network benefits given' OR val = 'medicare info given' OR val = 'ancillary product info given' OR val = 'answered question' OR val = 'Benefit Education given' OR val = 'Request Outside Service Scope' OR val = 'Patient care info given' OR val = 'Admin Issue Resolved' OR val = 'SSA Information Given' OR val = 'Healthcare Reform Info Given' OR val = 'TCE Info Given' OR val = 'Redir of Svcs from DME vendor' OR val = 'Redir of Svcs from Hospital' OR val = 'Redir of Svcs from Clinic' OR val = 'Redir of Svcs from Pharmacy' OR val = 'General Dental' OR val = 'General Medical' OR val = 'Medicare' OR val = 'General Vision' OR val = 'Specific Medical' OR val = 'treatment options discussed' THEN 'Benefit Questions and Education' WHEN val = 'network status given' OR val = 'finding network provider' OR val = 'Finding a PCP' OR val = 'Found out-of-network provider' OR val = 'Found in-network provider' OR val = 'Initial PCP Appointment' THEN 'Network Issues' WHEN val = 'pbm error - corrected' OR val = 'prescription obtained' OR val = 'prescription plan discussed' OR val = 'prescription written' THEN 'Pharmacy Issues' WHEN sub_type IN ('Cost of Providers', 'Cost Comparison', 'Cost Estimate') AND (val = 'provider cost info given' OR val = 'Letter sent: Chose diff prov' OR val = 'Letter sent: Member unsure' OR val = 'Letter sent: No member reply' OR val = 'Letter sent: Prov choice given' OR val = 'Letter sent: Opted out of proc' OR val = 'Letter sent: Chose high cost' OR val = 'Letter sent: Chose lower cost' OR val = 'No letter: Member unresponsive') THEN 'Provider Cost Info Given' WHEN val = 'Provider Cost Info Given' THEN 'Provider Cost Info Given' WHEN SUB_TYPE IN ('Quality of Providers', 'Quality Estimate', 'Quality Comparison') AND (val = 'Letter sent: Member unsure' OR val = 'Letter sent: No member reply' OR val = 'Letter sent: Qual info given' OR val = 'Letter sent: Opted out of proc' OR val = 'Provider Quality Info Given' OR val = 'Best Doctors - Find Best Docs' OR val = 'No letter: Member unresponsive') THEN 'Provider Quality Info Given' WHEN val IN ('Best Doctors - Find Best Docs', 'Provider Quality Info Given') THEN 'Provider Quality Info Given' WHEN val = 'enrollment - gen conversation' OR val = 'Enrollment - gen. conversation' OR val = 'enrollment - plan comparisons' OR val = 'choosing a health plan' OR val = 'Enrollment - plan comparison' OR val = 'enrollment' THEN 'Choosing a Health Plan' WHEN val = 'eligibility issue resolved' OR val = 'ID Cards' OR val = 'continuing coverage discussed' OR val = 'term of coverage discussed' THEN 'Eligibility Issues' WHEN val = 'authorization process discussed' OR val = 'authorization proc. discussed' OR val = 'authorization in place - fixed' OR val = 'authorization given' OR val = 'Auth Forms Sent' OR val = 'Auth Form Discussed/Completed' OR val = 'Autho Forms Sent' OR val = 'authorization' THEN 'Authorization' WHEN val = 'balance bill eliminated' OR val = 'bill adjusted by provider' OR val = 'claim denied by payor - fixed' OR val = 'claim processing error - fixed' OR val = 'claim processing error fixed' OR val = 'claim paid correctly' OR val = 'member owes balance' OR val = 'claim denied by payor - upheld' OR val = 'insurance billed' OR val = 'member error - corrected' OR val = 'claim filing error - corrected' OR val = 'provider error - corrected' OR val = 'provider error corrected' OR val = 'Balanced Bill eliminated' OR val = 'Claim filing error' OR val = 'Corrected claim filed' OR val = 'Copayment amount corrected' OR val = 'pharmacist error - corrected' THEN 'Claims and Billing Issues' WHEN val = 'sales' OR val = 'HR. sales/product info given' THEN 'Sales' WHEN val = 'issue withdrawn' OR val = 'not an issue' OR val = 'member contact unsuccessful' OR val = 'membership cancelled' OR val = 'not interested' OR val = 'not a problem' OR val = 'Not Eligible for PC Services' OR val = 'Collection efforts halted' OR val = 'Member Contact Unsuccessful' OR val = 'Member Unresponsive' OR val = 'Member Error Corrected' OR val = 'Third Party Resolution' OR val = 'warm transfer' OR val = 'Insurance Termination' OR val = 'Member cancelled request' THEN 'Other' WHEN val = 'Debit Cards' OR val = 'Reimbursement obtained' OR val = 'Eligibility Issue Resolved' OR val = 'Reimbursement plan discussed' THEN 'FSA/HRA/HSA' ELSE '' END AS TRANSLATION, CASE WHEN val = 'Balanced Bill eliminated' OR val = 'balance bill eliminated' OR val = 'bill adjusted by provider' THEN 'Balance Bill Write-Off' WHEN val = 'claim denied by payor - fixed' OR val = 'claim processing error - fixed' OR val = 'claim processing error fixed' THEN 'Claim Corrected' WHEN val = 'claim paid correctly' OR val = 'member owes balance' OR val = 'claim denied by payor - upheld' THEN 'Claim Paid Correctly' WHEN val = 'insurance billed' THEN 'Insurance Billed' WHEN val = 'member error - corrected' THEN 'Member Error - Corrected' WHEN val = 'claim filing error - corrected' OR val = 'provider error - corrected' OR val = 'provider error corrected' OR val = 'claim filing error' OR val = 'corrected claim filed' OR val = 'pharmacist error - corrected' THEN 'Provider Error Corrected' WHEN val = 'Resolved' OR val = 'Health Education Given' THEN 'Nurse Navigator' WHEN val = 'network status given' OR val = 'Found out-of-network provider' OR val = 'Found in-network provider' THEN 'Network status given' WHEN val = 'Finding a PCP' THEN 'Finding a PCP' WHEN val = 'Initial PCP Appointment' THEN 'Initial PCP Appointment' WHEN val = 'Redir of Svcs from Clinic' OR val = 'Redir of Svcs from DME vendor' OR val = 'Redir of Svcs from Hospital' OR val = 'Redir of Svcs from Pharmacy' THEN 'Redirection of Services' WHEN val = 'Copay Penalty' THEN 'Copay Penalty' ELSE '' END AS SUB_TRANS, ACTIVE_FLGFROM dbo.S_LST_OF_VALWHERE (TYPE = 'sr_resolution') |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-02-06 : 14:04:40
|
| why all these case statements? you should have simply put conditional values as well as the return values as a pair inside a table and added a join to that rather than complicating it like below!------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
dawnh
Starting Member
3 Posts |
Posted - 2012-02-07 : 13:22:45
|
| Thank you so much. I was able to figure out the problem. |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
Kristen
Test
22859 Posts |
Posted - 2012-02-07 : 13:54:49
|
| "SELECT DISTINCT TOP (100) PERCENT VAL ..."Is this a definition for a VIEW and includes an ORDER BY statement? If so it probably does not reliably, always, sort (or won't if you upgrade to later version of SQL Server), put the ORDER BY in the outer query.You would be advised to programmatically get rid of the DISTINCT as it will be(come) a performance issue otherwise (as will the big CASE statement and all the OR statements) |
|
|
|
|
|
|
|
|
Parameter not being seen in my query. Please help