Board index » delphi » SQL field name problem

SQL field name problem

Hi,

I have the problem that I'm trying to access and test for a field name
that is the same as an SQL reserved word. The name is "Year". I get an
"Invalid token" error if I try to test the field with something like
this:
SELECT field1, Year, field3, field4, field5
FROM Table WHERE Year LIKE '1999'
I get the same if I send:
SELECT field1, Year, field3, field4, field5
FROM Table WHERE Table.Year LIKE '1999'
I cannot change this field name because it's embedded into a database
that I didn't build. Is there any way at all to access this field?

Thanks in advance,
Robert.

 

Re:SQL field name problem


Quote
Robert Bassett wrote in message <38EBD484.29321...@buysell.com>...

>I have the problem that I'm trying to access and test for a field name
>that is the same as an SQL reserved word. The name is "Year". I get an
>"Invalid token" error if I try to test the field with something like
>this:
>SELECT field1, Year, field3, field4, field5
>FROM Table WHERE Year LIKE '1999'

Use double quotes around such field names:
SELECT field1, "Year", field3, field4, field5
FROM Table WHERE "Year" LIKE '1999'

As an aside, you shouldn't use LIKE unless you need a partial match on a
field - in which case you also need to specify a wildcard, e.g. ... LIKE
'199%'. If you want an exact match then use the equal operator.

--
Wayne Niddery - WinWright Consulting
RADBooks - http://members.home.net/wniddery/
You have a Right to Free Speech, but not the right to make me listen, nor to
use my property as a soapbox.

Re:SQL field name problem


On Wed, 05 Apr 2000 17:04:20 -0700, Robert Bassett <r...@buysell.com>
wrote:

Quote
>I have the problem that I'm trying to access and test for a field name
>that is the same as an SQL reserved word. The name is "Year". I get an
>"Invalid token" error if I try to test the field with something like
>this:
>SELECT field1, Year, field3, field4, field5
>FROM Table WHERE Year LIKE '1999'
>I get the same if I send:
>SELECT field1, Year, field3, field4, field5
>FROM Table WHERE Table.Year LIKE '1999'

>I cannot change this field name because it's embedded into a database
>that I didn't build. Is there any way at all to access this field?

Try doing two things. First, enclose the offending column name in
quotation marks. Second, prefix that quoted name with the table's name
or a table correlation name.

  SELECT T.field1, T.'Year', T.field3, T.field4, T.field5
  FROM Table T
  WHERE T.'Year' LIKE '%1999%'

\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\
Steve Koterski                    "If you aren't fired with
Technical Publications            enthusiasm, you will be
Borland                           fired with enthusiasm."
                              -- Vince Lombardi (1913-1970)

Re:SQL field name problem


Thanks Wayne,

But I'm finding that using quotation marks ("") doesn't work either. It seems to
be treating the string as a literal rather than a fieldname. ie. here is my
syntax:

SELECT "Year", ID FROM IMGLIB WHERE "Year" = "1999"

This gives me 0 records every time. If I test with "Year" = "Year" it gives me
all records!

Quote
>>As an aside, you shouldn't use LIKE unless you need a partial match on a

field - in which case you also need to specify a wildcard, e.g. ... LIKE
'199%'. If you want an exact match then use the equal operator.

Yes, thanks Wayne. I was in fact using the '%' operator. I just tidied up my
syntax (rather carelessly) for the message, to focus on my problem.

Thanks again,
Robert.

Quote
"Wayne Niddery (TeamB)" wrote:
> Robert Bassett wrote in message <38EBD484.29321...@buysell.com>...

> >I have the problem that I'm trying to access and test for a field name
> >that is the same as an SQL reserved word. The name is "Year". I get an
> >"Invalid token" error if I try to test the field with something like
> >this:
> >SELECT field1, Year, field3, field4, field5
> >FROM Table WHERE Year LIKE '1999'

> Use double quotes around such field names:
> SELECT field1, "Year", field3, field4, field5
> FROM Table WHERE "Year" LIKE '1999'

> As an aside, you shouldn't use LIKE unless you need a partial match on a
> field - in which case you also need to specify a wildcard, e.g. ... LIKE
> '199%'. If you want an exact match then use the equal operator.

> --
> Wayne Niddery - WinWright Consulting
> RADBooks - http://members.home.net/wniddery/
> You have a Right to Free Speech, but not the right to make me listen, nor to
> use my property as a soapbox.

Re:SQL field name problem


Thanks, Steve. This did it!

Robert.

Quote
skoter...@NOSPAMborland.com wrote:
> On Wed, 05 Apr 2000 17:04:20 -0700, Robert Bassett <r...@buysell.com>
> wrote:

> >I have the problem that I'm trying to access and test for a field name
> >that is the same as an SQL reserved word. The name is "Year". I get an
> >"Invalid token" error if I try to test the field with something like
> >this:
> >SELECT field1, Year, field3, field4, field5
> >FROM Table WHERE Year LIKE '1999'
> >I get the same if I send:
> >SELECT field1, Year, field3, field4, field5
> >FROM Table WHERE Table.Year LIKE '1999'

> >I cannot change this field name because it's embedded into a database
> >that I didn't build. Is there any way at all to access this field?

> Try doing two things. First, enclose the offending column name in
> quotation marks. Second, prefix that quoted name with the table's name
> or a table correlation name.

>   SELECT T.field1, T.'Year', T.field3, T.field4, T.field5
>   FROM Table T
>   WHERE T.'Year' LIKE '%1999%'

> \_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\
> Steve Koterski                    "If you aren't fired with
> Technical Publications            enthusiasm, you will be
> Borland                           fired with enthusiasm."
>                               -- Vince Lombardi (1913-1970)

Other Threads