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