Re: Classification Based on Values in a String

From: Jeremy Schneider <jeremy.schneider_at_ardentperf.com>
Date: Thu, 18 Aug 2011 17:36:34 -0500
Message-ID: <4E4D93F2.1070304_at_ardentperf.com>



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-l
Received on Thu Aug 18 2011 - 17:36:34 CDT

Original text of this message