Re: Classification Based on Values in a String
- From: Jeremy Schneider <jeremy.schneider@xxxxxxxxxxxxxx>
- To: david.barbour1@xxxxxxxxx
- Date: Thu, 18 Aug 2011 17:45:21 -0500
Oops, reverse the reject and provider strings in that query - my
mistake. :)
On 8/18/2011 5:43 PM, Jeremy Schneider wrote:
> 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:
