Re: Classification Based on Values in a String
- From: Gokul Kumar Gopal <gokulkumar.gopal@xxxxxxxxx>
- To: jeremy.schneider@xxxxxxxxxxxxxx
- Date: Fri, 19 Aug 2011 08:34:42 +0530
How about using a combination of case,like and not like in your select stmt
?
Rgds,
Gokul
On Aug 19, 2011 4:17 AM, "Jeremy Schneider" <jeremy.schneider@xxxxxxxxxxxxxx>
wrote:
> 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:
