Anyway to join between two different data types?

F

foghat

Hi all,

I have a Access database that is linking to Oracle tables.

I need to join on two different data types. Number and Varchar2.

With a oracle query, it is easy: one can simply put a to_char on the number
data type. So:

select *
from invoice i, comment c
where to_char(i.invc_id) = c.reference_id

However, I can't see any way to do this with the Access query (in either
Design View or SQL View).

Is this possible?
 
J

John Spencer

This will be slow, but have you tried using one of the conversion functions

select *
from invoice i, comment c
where (i.invc_id) & "" = c.reference_id


In a join that could be.
SELECT *
FROM Invoice as I INNER JOIN Comment as C
ON I.Invc_ID & "" = C.Reference_ID

If you were sure that Invc_ID always has a value (never null)
SELECT *
FROM Invoice as I INNER JOIN Comment as C
ON CStr(I.Invc_ID) = C.Reference_ID

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
 
J

Jerry Whittle

Where Oracle uses to_ Access often uses C for Change

CDate = todate

Of course to_char for character isn't quite CStr for change string!

And don't get me started on Oracle vs Access wild cards and usage of single
and double quotation marks!
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


foghat said:
Jerry said:
select *
from invoice i, comment c
where CStr(i.invc_id) = c.reference_id ;
[quoted text clipped - 15 lines]

Thanks a lot. Works like a charm. I just don't use Access enough, but
figured there must be a to_char equivalent.

.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top