Reserved word conflict!

Timmyboy · June 11th, 2004, 05:27 AM

Hello,

i have a db in Oracle that i'm trying to connect using Access.
To make these simple forms.

Only, now i have a problem with one of my tables.
There is a field that is called KEY.
And as far as i know it's a reserved word.

Here is my SQL:
SELECT STREET, POSTCODE, CITY
FROM ADDRESSES, ADDRLINKS, BORROWERS
WHERE ADDRESSES.ADDRNO = ADDRLINKS.ADDRNO AND ADDRLINKS.KEY = BORROWERS.BORROWER;

The renaming of the field is absolute not an option!
Does anybody know what i must do to make it work?

Thanks in advance!

greetz

Timmyboy

owain · June 11th, 2004, 05:35 AM

Wrap the reserved word in square brackets ([ and ]), e.g.:

Code:

SELECT STREET, POSTCODE, CITY
  FROM ADDRESSES, ADDRLINKS, BORROWERS
 WHERE ADDRESSES.ADDRNO = ADDRLINKS.ADDRNO
   AND ADDRLINKS.[KEY] = BORROWERS.BORROWER;

I hope this helps

Regards
Owain Williams

Timmyboy · June 11th, 2004, 06:02 AM

Thnx!

It works al though now i get an error that the types of expressions do not match.
ADDRLINKS.KEY = Text
BORROWERS.BORROWER = Number

greetz

Timmyboy

owain · June 11th, 2004, 11:01 AM

I am not sure how linked tables work in Access as far as SQL is concerned, but in Access SQL you can use the Val() or CLng() functions and in Oracle PL/SQL you can use the CAST function (I think, but I no hardly anything about Oracle, I only know about the SQL92 CAST function). Here is an example Access' Val() function:

Code:

SELECT STREET, POSTCODE, CITY
  FROM ADDRESSES, ADDRLINKS, BORROWERS
 WHERE ADDRESSES.ADDRNO = ADDRLINKS.ADDRNO
   AND Val(ADDRLINKS.[KEY]) = BORROWERS.BORROWER;

And here is an example of the CAST function

Code:

SELECT STREET, POSTCODE, CITY

Code:

  FROM ADDRESSES, ADDRLINKS, BORROWERS
 WHERE ADDRESSES.ADDRNO = ADDRLINKS.ADDRNO
   AND CAST(ADDRLINKS.[KEY] AS Int) = BORROWERS.BORROWER;

I hope this helps.

Regards
Owain Williams

Timmyboy · June 14th, 2004, 02:00 AM

Quote:

quote:Originally posted by owain
I am not sure how linked tables work in Access as far as SQL is concerned, but in Access SQL you can use the Val() or CLng() functions and in Oracle PL/SQL you can use the CAST function (I think, but I no hardly anything about Oracle, I only know about the SQL92 CAST function). Here is an example Access' Val() function:

Code:

SELECT STREET, POSTCODE, CITY

Code:

  FROM ADDRESSES, ADDRLINKS, BORROWERS
 WHERE ADDRESSES.ADDRNO = ADDRLINKS.ADDRNO
   AND Val(ADDRLINKS.[KEY]) = BORROWERS.BORROWER;

And here is an example of the CAST function

Code:

SELECT STREET, POSTCODE, CITY
  FROM ADDRESSES, ADDRLINKS, BORROWERS
 WHERE ADDRESSES.ADDRNO = ADDRLINKS.ADDRNO
   AND CAST(ADDRLINKS.[KEY] AS Int) = BORROWERS.BORROWER;

I hope this helps.

Regards
Owain Williams

Very very kind of you!
It works!
The VAL works but the other not.
So it's not a problem anymore!

kind regards,

timmyboy