3 Replies Latest reply: Jan 23, 2012 12:21 AM by BKBK RSS

    Help with CF & Oracle

    alecken Community Member

      Hi! I'm getting error on my query, I don't know why I got this error, been searching on the net and did not find any answer.

      Has any of you been working with Oracle? This query runs fine on MSSQL:

       

        <cfquery name="X" datasource="#Trim(arguments.DBSource)#">

                                    DELETE EmpTbl

                                    FROM EmpTbl, BioDataTbl

                                    WHERE BioDataTbl.BioId = EmpTbl.EmpId

                                    AND BioDataTbl.RegYear = EmpTbl.RegYear 

                                    AND BioDataTbl.Loc = EmpTbl.Loc

                                    AND BioDataTbl.RegYear = <cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(arguments.HRYear)#">

                                    AND BioDataTbl.Loc = <cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(session.groupLoc)#">

                                    AND Status IN (<cfqueryparam cfsqltype="cf_sql_varchar" value="0">,<cfqueryparam cfsqltype="cf_sql_varchar" value="x">)

                            </cfquery>

       

      The error I got was:

      The web site you are accessing has experienced an unexpected error.
      Please contact the website administrator.

      The following information is meant for the website developer for debugging purposes.
      Error Occurred While Processing Request

      Error Executing Database Query.

      [Macromedia][Oracle JDBC Driver][Oracle]ORA-00933: SQL command not properly ended

      The error occurred in C:\Inetpub\wwwroot\HREmp\components\HRNot.cfc: line 13
      Called from C:\Inetpub\wwwroot\HREmp\components\HRNot.cfc: line 87
      Called from C:\Inetpub\wwwroot\HREmp\emptab.cfm: line 67
      Called from C:\Inetpub\wwwroot\HREmp\components\HRNot.cfc: line 13
      Called from C:\Inetpub\wwwroot\HREmp\components\HRNot.cfc: line 87
      Called from C:\Inetpub\wwwroot\HREmp\emptab.cfm: line 67

       

      <cfquery name="X" datasource="#Trim(arguments.DBSource)#"> 14 :               

      DELETE EmpTbl 15 :               

      FROM EmpTbl, BioDataTbl

       

       

       

        • 1. Re: Help with CF & Oracle
          Dan Bracuk Community Member

          You can only specify one table in a delete query.

          • 2. Re: Help with CF & Oracle
            Owain North Community Member

            Plus you can't specify a column to delete from, it's just "DELETE FROM", not "DELETE EmpTbl FROM".

            • 3. Re: Help with CF & Oracle
              BKBK CommunityMVP

              <cfquery name="X" datasource="#Trim(arguments.DBSource)#">

                    DELETE FROM EmpTbl

                    WHERE EmpTbl.EmpId IN  

                    (

                        SELECT EmpTbl.EmpId   

                        FROM EmpTbl, BioDataTbl

                        WHERE EmpTbl.EmpId = BioDataTbl.BioId

                        AND EmpTbl.RegYear = BioDataTbl.RegYear 

                        AND EmpTbl.Loc = BioDataTbl.Loc

                        AND BioDataTbl.RegYear = <cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(arguments.HRYear)#">

                        AND BioDataTbl.Loc = <cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(session.groupLoc)#">

                        AND Status IN ('0','x')

                    )

              </cfquery>