link trim field to another field in different table

[sonny1974]

SELECT substr(lot_nbr,2) from <table>;


i did this .

so for example i have a new field
old field R92484
new field 92484

well when i link

int_table.rvcr = substr(lot_nbr,2)

i dont get any records back from the int.table
when i look in the int_table i know there is a record of 92484 in the rcvr field , but i dont get the records when i link it like this

 

[sonny1974]

can you give me a quick run down on what i should do

 

[sonny1974]

Im getting duplicates

select *
from wod_table wod
left join int_table int on substr(trim(wod.lot_nbr),2) = trim(int.rcvr)
where
trim(WOD.WO_NBR) = '401821' and
substr(wod.lot_nbr,1,1) in ('R','W')
AND '0123456789' like '%'||substr(int.rcvr,2,1)||'%'

 

[SantaMufasa]

Sonny,

<Mufasa climbs on soapbox>

Your situation becomes complex because whoever designed your columns (e.g., "lot_nbr" and "rcvr" at the very least), allowed the columns to become very undisciplined...to be completely candid, crappy data live in those columns:

1) "R" and "W" are acting as some sort of indicators and, as a result, should be in columns separate from "lot_nbr" (and possibly "rcvr").

2) And you said,

...seems like its only records that doesnt have a number in the 2nd character, which i dont care about those records...i only care about the records where the second character is a number after a R or W , etc.

The fact that "anything goes" (seemingly) in your columns means that your columns are simply "garbage cans" full of data and you must sort throught the "muck" every time you want to process your data. The data in your columns should act in disciplined, predictable ways. You should not have to constantly use overly contrived WHERE statements to decide whether you should process a row in one of "umpty-twelve" different ways.


Often times, when I point out that data is badly misbehaving, people respond something like

Well, this is the way our data is...I can't do anything about it...I'm only a <xyz-position>...Somebody else screwed up long, long ago. Can't you just quickly tell me what to do to get past this problem so that I can get on with what I really want to be doing?

At some point, if one is going to stop working in a trash heap, they must take ownership of the problem and be a force for implementing a "clean-up" of the situation. You might not be able to implement the resolution all by yourself, but you can (and perhaps, must) declare that "The Emporer is wearing no clothing."


<Mufasa climbs off soapbox>

So, given that:

1) I do not have a copy of your data to be able to uncover all the possible "exceptions" to your data that will cause trouble with your queries, and

2) I don't know how much authority and ability you have to "fix" your data to behave properly, and

2) I do not know how much of all of this you understand versus "Mufasa, can't you please just give me code that will untangle me from my knickers"

...We run the risk of spending the rest of the day on this issue and still not resolving, to your satisfaction, your problem(s).

So, since, by now, you are probably rather angry with my assessment, I'll try now to prove that I really want to help you:

Issue 1: "lot_nbr" and "rcvr" contain bad information...trailing blank spaces. Those trailing blank spaces cause problems for all comparisons: character comparisons between character expressions containing trailing blank spaces become problematic and between numeric expressions become impossible (as you discovered earlier). Therefore, whenever you want to deal with expressions containing trailing blank spaces, you must first use the TRIM() or RTRIM() functions to get rid of those trailing blanks.

Issue 2: If you want do deal with rows whose expressions contain only 'R' or 'W' in the first character and a numeric character in the second character, then you must constantly test for that condition with code such as:

...WHERE substr(<column>,1,1) in ('R','W')
     AND '0123456789' like '%'||substr(<column>,2,1)||'%'
...

That is an annoying hassle, but that's the constant price you must pay for poorly designed/poorly constrained data...You must implement data discipline for such expressions via convoluted, more-complex-than-necessary WHERE clauses.

So, this side of cleaning up your data, I've "given you a (not-so-quick) run down on what you should do."



Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[Thargy]

Sonny,

Mufasa-man is obviously quite correct. I've only just read this thread (I'm in a different time zone to you and Mufasa), and I spotted the trailing white spaces issue in about post 2 or 3. That's not because I'm clever, but because I work daily with a legacy system, which is full of such poor data as Santa is mentioning.

This can arise for many reasons, but one that often occurs is the use of CHAR columns in a table instead of VARCHAR2. If you say CHAR(10) and the form in the application (in my case VB6) only adds 8 characters, oracle will obligingly pad the string to 10 with two trailing blanks. It's gotten so bad for me, that I am intending a global change across the whole database, to replace CHAR with VARCHAR2. I am also very good at putting on constraints which check that MY_VALUE=UPPER(TRIM(MY_VALUE), which guarantees that you get rid of white spaces and aberrant case text.

<mounts soap box>
You simply can't go on with such terrible data. Your coding to "work around" it will become ever more onerous, until one day you will just have had enough!

throw some duplicates into the mix (which you appear to have) and the plot thickens at a truly nasty pace.

Mufasa has the right of it - take out the trash, and then code well against known-good data.
<descends from soap box>

Regards

Tharg

Grinding away at things Oracular