Home » Other » Client Tools » SQL Select with variables (merged by CM) (10g express)
SQL Select with variables (merged by CM) [message #444562] Mon, 22 February 2010 10:13 Go to next message
jfavelle
Messages: 2
Registered: February 2010
Junior Member
Having trouble with my variables in this staement.

Thanks In Advance

SELECT     WP_USERS.F_NAME || ', ' || WP_USERS.L_NAME AS NAME, WP_PROJECTS.TITLE, WP_PROJECTS.CLIENT_MANAGER, 
                      WP_PROJECTS.PERSON_DAYS
FROM         WP_PROJECTS, WP_PROJECTS_USERS, WP_USERS
WHERE     WP_PROJECTS.PROJECT_ID = WP_PROJECTS_USERS.PROJECT_ID AND WP_PROJECTS_USERS.USER_ID = WP_USERS.USER_ID AND 
WP_PROJECTS_USERS.USER_ID = WP_USERS.USER_ID AND (WP_PROJECTS.SITE = "@SITE") AND (WP_PROJECTS."SECTION" = '@SECTION')

[Updated on: Mon, 22 February 2010 10:36] by Moderator

Report message to a moderator

Re: SQL select with variables [message #444566 is a reply to message #444562] Mon, 22 February 2010 10:20 Go to previous messageGo to next message
rahulvb
Messages: 924
Registered: October 2009
Location: Somewhere Near Equator.
Senior Member
jfavelle wrote on Mon, 22 February 2010 10:17
Having trouble with getting the variables to run. for some reason Visual Web is adding quotes around wp_projects.section and not wp_projects.site?



? some more info please , copy paste SQL* error messages
Re: SQL Select with variables (merged by CM) [message #444567 is a reply to message #444562] Mon, 22 February 2010 10:28 Go to previous messageGo to next message
jfavelle
Messages: 2
Registered: February 2010
Junior Member
SQL EXECUTION ERROR

Error Source: System.Data.OracleClient
Error Message: ORA-00904: "@SITE": Invalid Identifier
Re: SQL Select with variables (merged by CM) [message #444568 is a reply to message #444567] Mon, 22 February 2010 10:32 Go to previous messageGo to next message
rahulvb
Messages: 924
Registered: October 2009
Location: Somewhere Near Equator.
Senior Member
jfavelle wrote on Mon, 22 February 2010 10:28
SQL EXECUTION ERROR

Error Source: System.Data.OracleClient
Error Message: ORA-00904: "@SITE": Invalid Identifier



there you go ORA-00904

Quote:

ORA-00904: string: invalid identifier
Cause: The column name entered is either missing or invalid.
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, then it must be enclosed in double quotation marks. It may not be a reserved word.



copy paste your complete code
Re: SQL Select with variables (merged by CM) [message #444569 is a reply to message #444562] Mon, 22 February 2010 10:34 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
I very much doubt this is an oracle problem. Looks like your client code hasn't substituted @SITE with the correct value before submitting the query to the database.
Re: SQL Select with variables (merged by CM) [message #444570 is a reply to message #444562] Mon, 22 February 2010 10:37 Go to previous messageGo to next message
rahulvb
Messages: 924
Registered: October 2009
Location: Somewhere Near Equator.
Senior Member
Quote:


SQL EXECUTION ERROR

Error Source: System.Data.OracleClient
Error Message: ORA-00904: "@SITE": Invalid Identifier


This is not oracle SQL plus error , I presume !!!
Re: SQL Select with variables (merged by CM) [message #444571 is a reply to message #444562] Mon, 22 February 2010 10:44 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>Having trouble with my variables in this staement.
Classic case of
Garbage In & Error Out

If "@NAME" are your variables, they need to be properly handled prior to passing the SQL statement to Oracle.
Otherwise Oracle's only recourse is to throw error when it detects invalid SQL.
Re: SQL Select with variables (merged by CM) [message #444576 is a reply to message #444562] Mon, 22 February 2010 11:43 Go to previous message
Frank
Messages: 7901
Registered: March 2000
Senior Member
Double quotes means Oracle treats the contents as a db-object.
You probably meant '@SITE', not "@SITE"
Previous Topic: Sql Plus and unicode.
Next Topic: Export dump of synonyms in a DB link
Goto Forum:
  


Current Time: Thu Mar 28 07:47:26 CDT 2024