• Global community
    • Language:
      • Deutsch
      • English
      • Español
      • Français
      • Português
  • 日本語コミュニティ
    Dedicated community for Japanese speakers
  • 한국 커뮤니티
    Dedicated community for Korean speakers
Exit
0

Help with CF & Oracle

Community Beginner ,
Jan 17, 2012 Jan 17, 2012

Copy link to clipboard

Copied

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

TOPICS
Getting started

Views

995

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
Jan 17, 2012 Jan 17, 2012

Copy link to clipboard

Copied

You can only specify one table in a delete query.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Guide ,
Jan 17, 2012 Jan 17, 2012

Copy link to clipboard

Copied

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

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Jan 23, 2012 Jan 23, 2012

Copy link to clipboard

Copied

LATEST

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

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Resources
Documentation