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