Re: Classification Based on Values in a String - Thanks
Date: Fri, 19 Aug 2011 10:39:02 -0500
Message-ID: <CAFH+iff0hJSCWvgYUhLFytcm6=5aygfoaP3kYRLPC_aW-VG-sw_at_mail.gmail.com>
Thank you very much Jeremy. I had tried replace (with a variety of decodes/instr/substr/etc) but sometimes I need someone to slap me in the face with the obvious (and simple) answer.
Here's what I ended up with:
For claims going to the standard reject queue:
select distinct claim_id, rejection_list , replace(replace(replace(replace (rejection_list,'09'),'07'),'22'),',','') as Codes
from wellmed_owner.claim
where claim_status = 'R'
and replace(replace(replace(replace(rejection_list,'09'),'07'),'22'),',','') is not null
For claims going to the provider set up queue:
select distinct claim_id, rejection_list , replace(replace(replace(replace (rejection_list,'09'),'07'),'22'),',','') as Codes
from wellmed_owner.claim
where claim_status = 'R'
and replace(replace(replace(replace(rejection_list,'09'),'07'),'22'),',','') is null
The queries shown above contain the ‘rejection_list’ and ‘as Codes’ selection elements just for review.
Only the claim_id is actually relevant.
At one point I was actually so far into nested, twisted functions I forgot what I had initially set out to accomplish. As Norman has pointed out, this is not a normalized database. And that lack has already caused numerous headaches since I swear these guys don't have a data dictionary, any type of code versioning and simply do not talk to each other when making changes. Since there's no referential integrity, they add/drop/change tables and columns which allows whatever code we receive to work, but breaks about 10 other things.
Steady employment anyway.
On Fri, Aug 19, 2011 at 1:51 AM, Dunbar, Norman (Capgemini) < norman.dunbar.capgemini_at_environment-agency.gov.uk> wrote:
> Morning all,
>
> >> 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.
> Ouch. Multiple data items in one column - think normalisation. The
> problem is a classic case of a non-normalised database design causing
> problems further down the line. (But you already know that!)
>
> >> 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:
>
> The solution Jeremy has indicated - replacing all the '09','07' and '22'
> codes with commas is the one I would have used in your situation as
> well. It's going to be pretty efficient I think - not that I have tested
> it - and will work. Of course, if "they" decide to add more rejection
> code in the future, you'll need to revisit this code.
>
> Good luck. I too have problems with vendors using a single column to
> hold multiple values. You should see some of the queries we have to run.
> :-(
>
> Why not ask them to fix it? "That's an enhancement request and will cost
> you extra". Yeah right! :-(
>
>
>
> Cheers,
> Norm.
>
> Norman Dunbar
> Contract Senior Oracle DBA
> Capgemini Database Team (EA)
> Internal : 7 28 2051
> External : 0113 231 2051
>
>
> Information in this message may be confidential and may be legally
> privileged. If you have received this message by mistake, please notify the
> sender immediately, delete it and do not copy it to anyone else.
>
> We have checked this email and its attachments for viruses. But you should
> still check any attachment before opening it.
> We may have to make this message and any reply to it public if asked to
> under the Freedom of Information Act, Data Protection Act or for litigation.
> Email messages and attachments sent to or from any Environment Agency
> address may also be accessed by someone other than the sender or recipient,
> for business purposes.
>
> If we have sent you information and you wish to use it please read our
> terms and conditions which you can get by calling us on 08708 506 506. Find
> out more about the Environment Agency at www.environment-agency.gov.uk
>
-- http://www.freelists.org/webpage/oracle-lReceived on Fri Aug 19 2011 - 10:39:02 CDT