[SOLVED] Comparing different type columns

[lemmin]

I have the following two tables:

funding(FundingID number, Name varchar);

programs(PID number, Funding varchar);

 

In MySQL I can use "WHERE p.Funding = f.FundingID" even though they are different types. This also works in Oracle as long as the varchar column actually holds a number, but if there is a string I get the error:"SQL error: [Oracle][ODBC][Ora]ORA-00918: column ambiguously defined , SQL state S1000 in SQLExecDirect"

 

This is the actual query:

"SELECT f.NAME FROM funding f LEFT JOIN programs p ON p.FUNDING = f.FUNDINGID WHERE p.PID = 7")

 

I tried to cast the varchar column as number and tried to_number function, but neither changed anything. Does anyone know of a way I can get around this? Even just suppressing the error would work because I want it to be null if there is no match, but it would have to actually return a result.

[lemmin]

Well, I figured out a way around it:

"SELECT DECODE(replace(translate(p.FUNDING,'1234567890','##########'),'#'),NULL,(SELECT f.NAME FROM funding f WHERE f.FUNDINGID = p.FUNDING),p.FUNDING) as testing FROM programs p"

 

It seems a bit ridiculous, but it works.