Re: Classification Based on Values in a String
Date: Thu, 18 Aug 2011 17:43:35 -0500
Message-ID: <4E4D9597.5030006_at_ardentperf.com>
select claim_id,
DECODE( LENGTH(REPLACE(REPLACE(REPLACE(REPLACE(rejection_list,'07'),'09'),'22'),',')),
0,'REJECT_QUEUE',
'PROVIDER_SETUP_QUEUE'
)
from claim;
On 8/18/2011 5:36 PM, Jeremy Schneider wrote:
> Can there be three-digit codes in the list, or only two digit codes in
> the list? If there's only two-digit codes then you could just use
> REPLACE to remove all instances of '07' and '09' and '22' - then if
> there's anything besides commas in the string, it goes to the reject
> queue.
>
> -J
>
> On 8/18/2011 4:47 PM, David Barbour wrote:
>> 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.ardentperf.com +1 312-725-9249 Jeremy Schneider Chicago -- http://www.freelists.org/webpage/oracle-lReceived on Thu Aug 18 2011 - 17:43:35 CDT