Classification Based on Values in a String
Date: Thu, 18 Aug 2011 16:47:03 -0500
Message-ID: <CAFH+ifeMZTDnZGcvDv9THh8fvLXy9OvqVwHR7yA+WNhUdP6YbA_at_mail.gmail.com>
This is somewhat of a puzzler where I can't find the right combination of Oracle functions to deliver the correct result.
Oracle 10.2.0.4 on RHEL 5.7
Claims coming into our system can be rejected for a variety of reasons which appear as comma separated values in the 'rejection_list' column of the claim table. Basically, there are two broad classes of rejects that need to be separated. The classes can be broken out as those rejected solely because the provider is not set up in the system and everything else.
Here's how it works:
To go to the Provider Setup Queue:
Criteria #1: The claim has a status code of ‘R’ Criteria #2: If reject code list contains one or more values, all of which are a ‘09’, a ‘07’ or a ‘22’ then it goes to the Provider Setup Queue
To go to the Reject Queue:
Criteria #1: The claim has a status of ‘R’
Criteria #2: If the reject code list contains multiple values, and if any one of the values in the list is not a ‘09’, a ‘07’ or a ‘22’, then it goes to the Reject Queue
Criteria #3: If the reject code list contains only one value, and that value is not a ‘09’, a ‘07’ or a ‘22’ then it goes to the Reject Queue
The codes can be in any order. There are a lot of codes. We do have a vendor-supplied function that determines if 09,07,22 is in the string, but it doesn't tell if any other codes are in the string as well.
It works okay if there aren't any provider reject codes in the string. The function returns a '0' and the claim goes to the generic reject queue.
'Select claim_id from claim
where FN_GET_INSTR(CLAIM.REJECTION_LIST,'07,09,22') <> 1'
It also works okay if we specify the code list only contains a single value and the value is a provider set up indicator. We get back a '1' and the claim goes to the set up queue.
'Select claim_id from claim
where length(rejection_list) = 2
and FN_GET_INSTR(CLAIM.REJECTION_LIST,'07,09,22') = 1'
Where the problem lies is determining if a rejection_list having multiple values consists solely of the provider reject codes, or contains other values.
Given a list like 04,01,09,22,53 - the function is going to return a '1'. But so is a list like 09 or 09,22. The first should go to the generic queue, the second and third to the provider set up queue.
I've tried a variety of combinations of regexp_ and old-timey Oracle functions (substr, instr, replace, etc.) but can't come up with a solution that will enable us to route claims with multiple codes in the rejection list to the proper queue. I probably need to use this function for the two cases in which it returns a valid result and just go ahead and write something totally new, but I was wondering if anybody has any ideas.
-- http://www.freelists.org/webpage/oracle-lReceived on Thu Aug 18 2011 - 16:47:03 CDT