Home » SQL & PL/SQL » SQL & PL/SQL » Using a keyword in a query? (9i)
Using a keyword in a query? [message #354176] Thu, 16 October 2008 17:21 Go to next message
ferrethouse
Messages: 43
Registered: August 2006
Member
Some tool named one of the fields in a table 'online' so I'm having trouble running a query...

update aqmsites set aqmsites.online = 'offline' where site_name like '%PAMZ%'

What is the syntax I need to use?
Re: Using a keyword in a query? [message #354179 is a reply to message #354176] Thu, 16 October 2008 18:42 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
You need to enclosed the column name in double-quotes ("). The column should have never been created with a keyword.
Re: Using a keyword in a query? [message #354206 is a reply to message #354176] Fri, 17 October 2008 00:38 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Before naming a column with something that could be a keyword, check v$reserved_words

Regards
Michel
Re: Using a keyword in a query? [message #354337 is a reply to message #354179] Fri, 17 October 2008 16:35 Go to previous messageGo to next message
ferrethouse
Messages: 43
Registered: August 2006
Member
ebrian wrote on Thu, 16 October 2008 18:42
You need to enclosed the column name in double-quotes ("). The column should have never been created with a keyword.


Enclosing it in single quotes makes my select statement work but neither double quotes or single quotes makes my update statement work. I'm using SQL Navigator but I also tried using SQL Plus and neither worked.

update aqmsites set "online" = 'no' where site_name like '%PAMZ%'

Does not work unfortunately. It turns out I don't have to update that column after all so this isn't urgent anymore but I'm still curious...
Re: Using a keyword in a query? [message #354340 is a reply to message #354337] Fri, 17 October 2008 17:52 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
Not sure where you are having the problem:

SQL> desc aqmsites
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 SITE_NAME                                          VARCHAR2(20)
 online                                             VARCHAR2(20)

SQL> update aqmsites set "online" = 'no' where site_name like '%PAMZ%';

1 row updated.

Are you sure the column name doesn't have a space before or after the name itself ?
Re: Using a keyword in a query? [message #354341 is a reply to message #354340] Fri, 17 October 2008 18:44 Go to previous messageGo to next message
ferrethouse
Messages: 43
Registered: August 2006
Member
I found the problem. Normally column names are case insensitive. But once you enclose them in double quotes they become case sensitive which explains why this didn't work...

SQL> update aqmsites set "online" = 1 where site_name like '%PAMZ%';
update aqmsites set "online" = 1 where site_name like '%PAMZ%'
                    *
ERROR at line 1:
ORA-00904: "online": invalid identifier


SQL> describe aqmsites
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------
 SITE_NUMBER                               NOT NULL NUMBER(3)
 SITE_NAME                                 NOT NULL VARCHAR2(50)
 ONLINE                                    NOT NULL NUMBER(1)
 EVENTFILE                                          VARCHAR2(255)
 TIMEFILE                                           VARCHAR2(255)
 LAST_TIME_READ                                     DATE
 LAST_EVENT_READ                                    DATE
 LAST_ALARM_CHECK                                   DATE
 POLLING_SERVER                                     VARCHAR2(255)
 IS_ONLINE                                 NOT NULL NUMBER(1)
 STATUS_CODESET                                     NUMBER(2)
 ALL_SITES_RPT                                      NUMBER(3)
 TIME_ZONE_DELTA                           NOT NULL NUMBER
 WATCHDOG_INTERVAL                                  NUMBER
 WATCHDOG_ENABLED                                   NUMBER
Re: Using a keyword in a query? [message #354342 is a reply to message #354341] Fri, 17 October 2008 18:47 Go to previous message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
ferrethouse wrote on Fri, 17 October 2008 19:44
I found the problem. Normally column names are case insensitive. But once you enclose them in double quotes they become case sensitive which explains why this didn't work...

Correct. Another good reason not to use quotes and/or keywords for object names.

[Updated on: Fri, 17 October 2008 18:54]

Report message to a moderator

Previous Topic: fill missing values
Next Topic: Select against function or procedure
Goto Forum:
  


Current Time: Thu Dec 08 22:00:45 CST 2016

Total time taken to generate the page: 0.13277 seconds