Re: Classification Based on Values in a String

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

Other related posts: