Searching on char field

Hi,

I have a commerical database which have five tables keyed off on a char column which is 17 bytes long. When I promote such table to IJC I noticed that my normally working SQL in SQLPlus would not work at all. Such as following query works fine in SQLPlus

select count(*) from catalog where catid='ABCD1234',

but when I do '= ABCD1234' on this table's grid view in IJC I do not retrieve the record, instead I have to do 'like ABCD1234%'

I know we suppose to use varchar2 instead of the char these days, but this is the commerical database I do not want to change their schema.

Is there a way to get around the problem? I am gald to do further test to verify the problem.

Thanks

Dong

Hi there Dong,

 

I am assigned to assist in the resolution of your issue.

It would be helpful if you could say the type of entity you promoted to, I believe there are 4 choices:

Standard, Standard + index, JChem, JChem + index.

Did you choose your key field as the primary key - presumably yes?

If you can say the suppplier source of your data it might subsequently help if my mock up data does not manage to replicate your issue.

 

Also the exact version of IJC that you use would be good found from IJC.help >> about.

 

Thanks,

 

Daniel.

Hi Dong,

 

I have been able to replicate your observation with a JChem table so we can probably assume the same behaviour stands for all entity type i.e. any Oracle table.

Thanks for reporting this issue, we will log the problem and work on the issue.

As you say, in the meantime a work around could be to use a VARCHAR2 data type in Oracle.

 

Thanks,

Daniel.

Hi Daniel,

Yes the database is Oracle. I believe this bug is really severe, if truly confirmed, it should set high priority for fixing. Since the database is distributed by a commercial vendors, the char column is the primary key to many tables, I am hesitate to change it to varchar2. I do not know if it will break the auto-updating process of the database.

Thanks again

Dong

Hi Dong,

 

Please see the "relatively hot" article on Oracle on this topic :

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:2668391900346844476

I think the conclusion is VARCHAR2 is recommended by Oracle but it seems somewhat debatable.

With CHAR data type perhaps the consideration of white space / padding is part of the issue you reported - to be determined...

 

Thanks we will update the post soon with the progress,

 

Daniel.

In original input to database there is no trailing space. Since this is the char  I can search with trailing space and without space to find the record such as

select bbb from table where prop='ABCD'

or

select bbb from table were prop='ABCD        '

notice that the 'ABCD     '  has to be exactly the length of the char field.

IJC should accommodate this

Yes, we will investigate this and try to find a solution.

Tim