locked
ORA-00920: invalid relational operator 00920. 00000 - "invalid relational operator" RRS feed

  • Question

  • User442781244 posted

    here is my query....kindly suggest

    select * from Users where OFFICE_CODE "
                          + " IN(select OFFICE_CODE from OFFICES where DEPARTMENT_CODE IN "
                          + " (select DEPARTMENT_CODE from DEPARTMENTS1 where DEPARTMENT_NAME="home"

    Friday, January 10, 2014 1:45 AM

Answers

  • User269602965 posted

    Avoid punctuation errors by enclosing SQL query into an XML data element and pass VALUE to the Oracle connection

    then use oracle data readers, etc. to bind to your result set to data grid, etc.

    in the where clause, rememeber 'home' is case sensitive

     

    Imports System.Xml.Linq.XElement
      Dim connectionString As String = ConfigurationManager.ConnectionStrings("OraConnStr").ConnectionString
        Try
          Dim SQL =
          <SQL>
    				SELECT
    					*
    				FROM
    					USERS
    				WHERE
    					OFFICE_CODE IN
    					 (
    					 SELECT
    						 OFFICE_CODE 
    					 FROM
    						 OFFICES 
    					 WHERE
    						 DEPARTMENT_CODE IN 
    						 (
    						 SELECT
    							 DEPARTMENT_CODE 
    						 FROM
    							 DEPARTMENTS1 
    						 WHERE
    						 DEPARTMENT_NAME = 'home'
    						 )
    						)
          </SQL>
          Using conn As New OracleConnection(OraConnStr)
            Using cmd As New OracleCommand(SQL.Value, conn)
              conn.Open()
              ' then from here decide on what you need, like loading data into data reader for binding to data grid
            End Using
          End Using
        Catch ex As Exception
           ' handle your exceptions here
        End Try
    

     

     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, January 10, 2014 5:54 PM

All replies

  • User364663285 posted

    You have to use Exec immediate for this. Read

    http://docs.oracle.com/cd/B19306_01/appdev.102/b14261/executeimmediate_statement.htm

    Friday, January 10, 2014 2:06 AM
  • User269602965 posted

    Avoid punctuation errors by enclosing SQL query into an XML data element and pass VALUE to the Oracle connection

    then use oracle data readers, etc. to bind to your result set to data grid, etc.

    in the where clause, rememeber 'home' is case sensitive

     

    Imports System.Xml.Linq.XElement
      Dim connectionString As String = ConfigurationManager.ConnectionStrings("OraConnStr").ConnectionString
        Try
          Dim SQL =
          <SQL>
    				SELECT
    					*
    				FROM
    					USERS
    				WHERE
    					OFFICE_CODE IN
    					 (
    					 SELECT
    						 OFFICE_CODE 
    					 FROM
    						 OFFICES 
    					 WHERE
    						 DEPARTMENT_CODE IN 
    						 (
    						 SELECT
    							 DEPARTMENT_CODE 
    						 FROM
    							 DEPARTMENTS1 
    						 WHERE
    						 DEPARTMENT_NAME = 'home'
    						 )
    						)
          </SQL>
          Using conn As New OracleConnection(OraConnStr)
            Using cmd As New OracleCommand(SQL.Value, conn)
              conn.Open()
              ' then from here decide on what you need, like loading data into data reader for binding to data grid
            End Using
          End Using
        Catch ex As Exception
           ' handle your exceptions here
        End Try
    

     

     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, January 10, 2014 5:54 PM
  • User269602965 posted

    Also read about Oracle Parameters passed to BIND variable in the WHERE clause instead of using the hard-ccoded value 'home'

    example passing value to routine, passing to  Oracle parameter then use in WHERE clause as BIND variable.

    Public Shared Sub updateUnitsActiveFlag(ByVal decCustomerSeq As Decimal)
      ' Update the UNITS.ACTIVE_FLAG on Customer set to Inactive'
      Dim connectionString As String = ConfigurationManager.ConnectionStrings("OraConnStr").ConnectionString
      Try
        Dim SQL =
        <SQL>
        UPDATE {YOURSCHEMANAME}.UNITS
        SET ACTIVE_FLAG        = 'Inactive',
            SEND_CONTRACT_FLAG = 'Do not send'
        WHERE CUSTOMER_SEQ = :CUSTOMER_SEQ
        </SQL>
        Using conn As New OracleConnection(connectionString)
          Using cmd As New OracleCommand(SQL.Value, conn)
            cmd.Parameters.Clear()
            cmd.Parameters.Add("CUSTOMER_SEQ", OracleDbType.Decimal, decCustomerSeq, ParameterDirection.Input)
            conn.Open()
            cmd.ExecuteNonQuery()
          End Using
        End Using
      Catch ex As Exception
        AppCalls.WriteToEventLog(ex, "Updating UNITS.ACTIVE_FLAG from customer inactivation failed", "AppCalls.updateUnitsActiveFlag.vb")
      End Try
    End Sub

     

    Monday, January 13, 2014 8:51 PM