Skip navigation
Currently Being Moderated

Help with CF & Oracle

Jan 17, 2012 10:06 AM

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

 

 

 

 
Replies
  • Currently Being Moderated
    Jan 17, 2012 11:25 AM   in reply to alecken

    You can only specify one table in a delete query.

     
    |
    Mark as:
  • Currently Being Moderated
    Jan 17, 2012 11:41 AM   in reply to Dan Bracuk

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

     
    |
    Mark as:
  • Currently Being Moderated
    Jan 23, 2012 12:21 AM   in reply to alecken

    <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>

     
    |
    Mark as:

More Like This

  • Retrieving data ...

Bookmarked By (0)

Answers + Points = Status

  • 10 points awarded for Correct Answers
  • 5 points awarded for Helpful Answers
  • 10,000+ points
  • 1,001-10,000 points
  • 501-1,000 points
  • 5-500 points