Problem joining CHAR <> VARCHAR2

[axelo]

Hey,
I work with spatial data in an Oracle 9i database in a German municipality and have a problem as follows:
I have to join data from the land parcels [ALK] with the corresponding data of the owner of the parcels [ALB]. I will do this with the 'parcel_id' that is differently distinct:

ALK: VARCHAR2 (33Bytes) - example: 0304220210001100700
ALB: CHAR (23Bytes) - example: 030422-021-00011/007.00

until now I did as follows:
- "deleted" the special characters by UPDATE..SET..REPLACE
- tried a join that was not successful
- changed the datatype of the ALB column by ALTER TABLE..MODIFY to VARCHAR2 (33Bytes) <- no success
- created a new table + a new column with the needed datatype VARCHAR2 (33Bytes) and added the data by INSERT INTO ..SELECT.. <- no success
- created a new column in the ALB and added two datasets manually: UPDATE..SET NEW_ID='0304220210001100700'WHERE ID=0304220210001100700 <- success

So to me it looks like it is not sufficient just to assume the data. Instead you have add "new characters" to make it work. How can this be done without writing the UPDATE..SET NEW... commands manually[more than 320000 data sets]? What about a function? I have just basic knowledge of SQL so I need help. So I hope somebody understood what my problem is all about.

TIA
Axel

 

[SantaMufasa]

Axel,

Consider the following code:

declare
    a VARCHAR2(33);
    b CHAR(33);
begin
    a := 'ABCDEFG';
    b := 'ABCDEFG';
end;
/

Can you explain any differences in the contents of "a" and "b" at the end of the execution of the code?

So I hope somebody understood what my problem is all about.

Do you understand what your probelm is all about? Could you please state clearly and concisely your question(s)?

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

 

[karluk]

It looks to me as if your joins are failing because of the extra spaces that exist at the end of the char(23) column. I would try altering the column to varchar2(33), as you did, and then updating the column to trim spaces. Then your join should work.

alter table ALB modify (parcel_id varchar2(33));
update ALB set parcel_id = trim(parcel_id);

 

[axelo]

Hey karluk,
thank you for this hint. I checked the length() of 'parcel_id' in ALK and ALB:

in ALK:19
in ALB:23

So I trimmed the parcel_id in the ALB and checked again:
the length has been reduced to 19.

But although the characters and the lenght are now the same there is still no join possible.

Are there any other differences like the length that can not directly be seen but have to be checked and corrected by a function?

 

[karluk]

Hmm, are you saying that the following single-table selects each return one row

select parcel_id from alb
  where parcel_id = '0304220210001100700';
select parcel_id from alk
  where parcel_id = '0304220210001100700';

but that the following two-table join doesn't return any rows at all?

select alb.parcel_id from alb, alk
  where alb.parcel_id = alk.parcel_id
    and alb.parcel_id = '0304220210001100700';

 

[blom0344]

Trim BOTH fields in both tables to make sure that no spaces precede the actual value (instead of trailing spaces).

Ties Blom

 

[axelo]

Have been offline for a while - so sorry for the delay.

@karluk:
yes, that's what I'm saying - except that I have to query without the ''. Could this be for any relevance?

@blom0344:
to trim I used the command as follows:
update ALB_ADMIN.ALPHA_FLST_T set FLURSTUECK_KENNZ = trim(' ' from FLURSTUECK_KENNZ);
so any blank space before and after should be terminated.
the reduction of the lenght() from 23 to 19 shows that it worked fine since I deleted the 4 special characters in the beginning.
the lenght in the alk is 19 - so what should be trimmed. I don't like to modify the alk because a lot of things need the data to work correctly and I can't estimate the consequences.
I will create an dummy table on Monday ...


Regards
Axel

 

[Welshbird]

I would maybe test by creating a duplicate column in each table, copying the column to the new column, and then doing a simple

update table
set column = replace(column,' ','')
/

in order to remove all spaces.

Just a thought.


Fee

The question should be [red]Is it worth trying to do?[/red] not [blue] Can it be done?[/blue]

 

[karluk]

Leaving off the quotes can definitely make a difference. However, I can't think of a situation where it will make a difference when the length of parcel_id is 19 in both alb and alk and there are no leading or trailing spaces.

Consider the situation with the following data:
alb.parcel_id = '304220210001100700'
alk.parcel_id = '0304220210001100700'

Evaluated as strings, alb.parcel_id <> alk.parcel_id because one has a leading zero and the other doesn't. However, if you evaluate them as numbers, then the two are equal. That means that the queries

select parcel_id from alb
  where parcel_id = 0304220210001100700;
select parcel_id from alk
  where parcel_id = 0304220210001100700;

will both return one row but the join

select alb.parcel_id from alb, alk
  where alb.parcel_id = alk.parcel_id
    and alb.parcel_id = 0304220210001100700;

doesn't return any rows. The problem with the join is that the first condition in the where clause, "alb.parcel_id = alk.parcel_id", is evaluated as a string comparision of two varchar2 columns, but the second condition, "alb.parcel_id = 0304220210001100700", is evaluated as a number with an implicit data type conversion. That leads to the apparently contradictory results.

I'm not sure I've identified the specific problem you're having, since you say that both columns are 19 bytes in length. However, I suspect it's something along this line.

 

[axelo]

Hey willif, Hey Karluk,
thanks for your efforts.

@ willif:
although I am not sure if there is a difference between removing spaces by trim() or replace() I did as you suggested. But I did not have any success.

@Karluk:
My query to join the two table won't include an special parcel_id - just something like:

select alk.parcel_id, alb.name from alk, alb
where alk.parcel_id = alb.parcel_id


Regards
Axel

So there is no problem with considering the data firstly as string and later as number, is there?

 

[SantaMufasa]

Axel,

"trim()" removes any leading and trailing spaces from a character expression. "replace()" (depending upon the arguments you pass to the function) removes any spaces (either leading, trailing, or intermingled) from your character expression.

There is no problem "considering the data firstly as string and later as number", but you must be aware of how Oracle carries out implicit data conversions so that you get the results you want/expect.

So, Axel, could you please restate where you are now with your problem and your most recent version of your code?

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

 

[axelo]

OK.
to match my alb data to the alk data I
-deleted the special characters by replacing them with ''
-altered the datatype of the alb
-removed spaces in alb so that the length is equal to the length of the alk

So the datatype is the same, the length is the same and the data 'looks' the same but I still can not join the two tables.


Looks like we can't solve the problem by working on an 'abstract level'. So are you willing to examine the problem by working with extracts of my two datasets?


Regards
Axel

 

[SantaMufasa]

Absolutely.

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

 

[SantaMufasa]

...and to facilitate our comparing data on an equal footing, it would be most helpful for us (as helpers) if you post your sample data in the form of "CREATE TABLE..." statements and "INSERT INTO..." statements.

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

 

[axelo]

Hey Dave,
once again sorry for the delayed answers. I can't deal with this topic as constantly as I want to ...

Since the statement are quite long and it looks like you are the only one willing to help - can I send it to the mail address given on the site of your company?


Regards
Axel

 

[SantaMufasa]

That's fine. Make sure that you put the phrase "Tek-Tips" somewhere in the subject line of the e-mail.

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