Problem with Linked Oracle Tables 1

[scottyjohn]

Hi all,
I have two tables in an oracle instance that I am trying to query using access 2000. The first table PERSON should be able to be joined to the second table named STATUS in the following way...
SELECT PERSON.EMPLOYEE_ID, STATUS.STATUS
FROM PERSON INNER JOIN STATUS ON PERSON.DBID = STATUS.AGENTDBID;

Problem is that although on my oracle SQL gui it shows these two fields as NUMBER fields, when they have been linked in access, it shows one as a number and the other as a text field. Therefore the join becomes invalid and the query fails. Is there anyway to force access to see both tables field types properly when creating the join? Or is there some reason why its having this problem?

Any help is appreciated!

John

 

[IanWaterman]

You could create a view in Oracle based upon the select query you give above. An then get access to just query that view.

CREATE OR REPLACE VIEW EMPLOYEE_STATUS as
SELECT PERSON.EMPLOYEE_ID, STATUS.STATUS
FROM PERSON INNER JOIN STATUS ON PERSON.DBID = STATUS.AGENTDBID


Ian

 

[PWise]

try

SELECT PERSON.EMPLOYEE_ID, STATUS.STATUS
FROM PERSON INNER JOIN STATUS ON PERSON.DBID = val(STATUS.AGENTDBID);

 

[scottyjohn]

Thanks Pwise,
That did the trick! have a star

John