Data Type Join

[informer30]

Hi,

please can someone advise, how do I join datatype varchar to number
in the where clause?

Many Thanks

 

[IanWaterman]

where to_number(varcharfield) = numberfield

Query will fail if it encounters a non numeric string.

Ian

 

[informer30]

Hi Ian,

I have done what you have said and I keep getting 'Invalid Number' message.

My sql code

select *
from table1 a, table2 b
where
to_number (test_id) = test_id
and b.date >= '16-Aug-2010'
and b.date < sysdate

Please can you help...

Thanks

 

[informer30]

Anyone any ideas?

 

[IanWaterman]

I did say if field held no numeric strings it would fail.

You need to find out why non nummeric data is present

Try

select *
from table1 a
test_id desc

this should bring non numerics to top so that they can either be corrected or excluded from your query.

Ian

 

[informer30]

Ian,

is it not easier converting a number to a varchar?

Please can you advise...

Thanks

 

[Thargy]

informer,

why don't you try converting a number to a varchar and see if it's "easier", then post your results here.

Meanwhile, may I ask what's the point of asking for help, and then when someone tells you how to find the problem, ignoring their advice?

Regards

T

 

[informer30]

Hi All,

I have tried the help advised and noticed there are more than a few rows of varchars from table 1, hence rather than harcoding I thought turn the query around number to varchar.

I know this is not route cause fixig and I need to speak to the dbas, I am new to oracle hence the questions.

Please can you advise.

Many Thanks

 

[Thargy]

informer,

can you please post create table statements for the tables in question, and some insert statements to give an example of the data involved.

The problem appears to be that you have non-numeric data and are attempting to handle it as numeric.

The create table statements and the data will enable us to assist.

Regards

T

 

[informer30]

Thanks Thargy,

just found out from the dba the column is encrypted...so trying to find out why as this is the pk and affecting query performance....

Will let you know how it goes...


Thanks