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

 

[SantaMufasa]

Sonny,

Can you please post the entire WHERE statement? Your "int_table.rvcr = substr(lot_nbr,2)" looks solid, so the culprit would appear to be some other part of the WHERE clause.

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

 

[sonny1974]

select *
from wod_table wod
left join int_table int on substr(wod.lot_nbr,2) = int.rcvr
where int.rcvr ='92484'

when i look at the int_table by itself i get data for that rcvr

my lot_nbr in wod is R92484

 

[SantaMufasa]

Not that is should make any difference, but please try out this code:

select *
  from wod_table wod, int_table int
 where substr(wod.lot_nbr,2) = int.rcvr
   and int.rcvr ='92484';

Let us know your findings.


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

 

[sonny1974]

nothing

 

[sonny1974]

select *
from int_table int

where int.rcvr ='92484';

when i look at int only i get the data from int
some reason the substring isnt linking right , but the data result from the substring looks like the rcvr number,

just doesnt seem to work when its linking

 

[SantaMufasa]

Okay, next thing I'd try is:

select count(*)
  from wod_table wod
 where substr(wod.lot_nbr,2) = '92484';

select count(*)
  from int_table int
 where int.rcvr ='92484';

Don't give up...We'll get this figured out.




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

 

[sonny1974]

i get 0 for the first one

and 3 for second

 

[sonny1974]

if i do this


select count(*)
from wod_table wod
where lot_nbr = 'R92484';


i get 2

 

[sonny1974]

if i do this i


select lot_nbr,substr(wod.lot_nbr,2)
from wod_table wod
where lot_nbr = 'R92484';


i get

LOT_NBR SUBSTR(WOD.LOT_NBR,2)

R92484 92484
R92484 92484

 

[SantaMufasa]

Here's the next try:

select count(*)
  from wod_table wod
 where to_number(substr(wod.lot_nbr,2)) = 92484;

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

 

[sonny1974]

invalid number error

 

[SantaMufasa]

Strange...try:

select '['||lot_nbr||']'
  from wod_table
 where length(translate(substr(log_nbr,2),'A0123456789','A')) > 0;

...then post the output here (unless there are so many rows that it is unreasonable). Any LOT_NBRs that are non-numeric in positions 2,3,4,5,or 6 will display, and those are ones that we need to identify.

Let us know your findings.

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

 

[sonny1974]

over 111,000 results here are a few

'['||LOT_NBR||']'

[R95845 ]
[R95845 ]
[R99006 ]
[R98801 ]
[R99672 ]
[R99590 ]
[R99822 ]
[R99303 ]
[R00088 ]
[R90461 ]
[R98802 ]

 

[sonny1974]

here are some results from this

select '['||rcvr||']'
from int_table
where length(translate(rcvr,'A0123456789','A')) > 0;

'['||RCVR||']'

[99448 ]
[99449 ]
[99525 ]
[99449 ]
[99448 ]
[99525 ]
[99525 ]

 

[SantaMufasa]

Ah-ha! There are totally extraneous trailing blank spaces...not a good thing in Oracle, and certainly not the default presumption. Therefore, to lay the groundwork for resolving your main issue, try this code (which should now return 0 rows):

select '['||lot_nbr||']'
  from wod_table
 where length(
          translate(
             substr[b](
                trim(log_nbr)[/b],2
                   )
                   ,'A0123456789','A'
                   )
             )
       > 0;

If there are, in fact, zero rows selected, then you should be able to use this code for success:

select *
  from wod_table wod, int_table int
 where substr(trim(wod.lot_nbr),2) = int.rcvr
   and int.rcvr ='92484';

Let us know your findings.



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

 

[SantaMufasa]

Sorry, Sonny, I just noticed that you have the same problem (totally extraneous trailing blank space) in your other table, as well. So, your revised code should read:

select *
  from wod_table wod, int_table int
 where substr(trim(wod.lot_nbr),2) = trim(int.rcvr)
   and trim(int.rcvr) = '92484';

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

 

[sonny1974]

i am getting records from the first query.

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.

second query i got 0 records

 

[SantaMufasa]

BTW, Sonny, how are extraneous trailing blanks becoming part of your data in the first place? Whatever is your front-end source for data, it should be ensuring that your data are properly trimmed before ever allowing the data to enter the database.

To get rid of your trailing-blank-spaces problem, you can issue the following command:

UPDATE <table_name>
   SET <column_to_trim> = TRIM(<column_to_trim>)
 WHERE length(<column_to_trim)
       <> LENGTH(TRIM(<column_to_trim>));

Let us know your thoughts.

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

 

[SantaMufasa]

Now that I have more specs from you <grin>, you can cause Oracle to consider just the records in which you are interested by saying, where applicable:

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

Update us on your findings, reconciled for possible cross-posts between you and me.

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