Hi, The application has a link with an Oracle database. In the SQL table the field is defined like this : DATE_NAI_IND DATE with NULL When I try to use this field in a SQL request, for example : select DATE_NAI_IND, COD_ETU from VUE_TRAV_EXT_RVM_RES where TO_CHAR(DATE_NAI_IND,'dd/mm/yy')= "08/08/1997" or DATE_NAI_IND = "08/08/1997" I always receive the same error : Error, dco2.DatabaseError: (904, 'ORA-00904: Nom de colonne non valide') Thanks for your help
champier2002 wrote:
Hi,
The application has a link with an Oracle database. In the SQL table the field is defined like this : DATE_NAI_IND DATE with NULL
When I try to use this field in a SQL request, for example : select DATE_NAI_IND, COD_ETU from VUE_TRAV_EXT_RVM_RES where TO_CHAR(DATE_NAI_IND,'dd/mm/yy')= "08/08/1997" or DATE_NAI_IND = "08/08/1997"
I always receive the same error : Error, dco2.DatabaseError: (904, 'ORA-00904: Nom de colonne non valide')
Thanks for your help
Here's the action from the Oracle error message guide:
Action: Enter a valid column name. A valid column name must begin with a letter, be less than or equal to 30 characters, and consist of only alphanumeric characters and the special characters $, _, and #. If it contains other characters, it must be enclosed in double quotation marks. It may not be a reserved word.
I read that as being that you can't use TO_CHAR() as a column name in your where clause; the TO_CHAR must be on the right-hand-side of the expression e.g. where DATE_NAI_IND = TO_DATE('08/08/1997', 'dd/mm/yyyy') although normally Oracle will do string to date conversion for you automatically. -- Matt Kromer Zope Corporation http://www.zope.com/
Matthew T. Kromer writes:
Here's the action from the Oracle error message guide:
Action: Enter a valid column name. A valid column name must begin with a letter, be less than or equal to 30 characters, and consist of only alphanumeric characters and the special characters $, _, and #. If it contains other characters, it must be enclosed in double quotation marks. It may not be a reserved word.
I read that as being that you can't use TO_CHAR() as a column name in your where clause; the TO_CHAR must be on the right-hand-side of the expression e.g.
where DATE_NAI_IND = TO_DATE('08/08/1997', 'dd/mm/yyyy')
although normally Oracle will do string to date conversion for you automatically. I do not think, this is the problem::
in order to implement case insensitive search, I used: where ... lower(column_name) like/= lower(literal) ... and Oracle did not complain. I expect, the left side of "=/like" is not restricted to a column name. Dieter
Dieter Maurer wrote:
Matthew T. Kromer writes:
Here's the action from the Oracle error message guide:
Action: Enter a valid column name. A valid column name must begin with a letter, be less than or equal to 30 characters, and consist of only alphanumeric characters and the special characters $, _, and #. If it contains other characters, it must be enclosed in double quotation marks. It may not be a reserved word.
I read that as being that you can't use TO_CHAR() as a column name in your where clause; the TO_CHAR must be on the right-hand-side of the expression e.g.
where DATE_NAI_IND = TO_DATE('08/08/1997', 'dd/mm/yyyy')
although normally Oracle will do string to date conversion for you automatically. I do not think, this is the problem::
in order to implement case insensitive search, I used:
where ... lower(column_name) like/= lower(literal) ...
and Oracle did not complain. I expect, the left side of "=/like" is not restricted to a column name.
Dieter
Yep, I just re-read his original question... the problem is he was quoting the date wrong! He said: where TO_CHAR(DATE_NAI_IND,'dd/mm/yy')= "08/08/1997" The problem is he used double quotes! String literals need single quotes in SQL; using a double quote means referencing a schema name -- and of course, no fully qualified can include a slash. So, where TO_CHAR(DATE_NAI_IND,'dd/mm/yy')= '08/08/1997' should work. -- Matt Kromer Zope Corporation http://www.zope.com/
champier2002 writes:
The application has a link with an Oracle database. In the SQL table the field is defined like this : DATE_NAI_IND DATE with NULL
When I try to use this field in a SQL request, for example : select DATE_NAI_IND, COD_ETU from VUE_TRAV_EXT_RVM_RES where TO_CHAR(DATE_NAI_IND,'dd/mm/yy')= "08/08/1997" or DATE_NAI_IND = "08/08/1997"
I always receive the same error : Error, dco2.DatabaseError: (904, 'ORA-00904: Nom de colonne non valide') I like Oracle error messages: they always hide the essential information...
Dieter
participants (3)
-
champier2002 -
Dieter Maurer -
Matthew T. Kromer