Skip to main content
Inspiring
September 23, 2009
Answered

ORA-00911: invalid character

  • September 23, 2009
  • 1 reply
  • 1841 views

I need to what is wrong.. Thank u all.

---------

<cffunction name="get" hint="Auto Metric Importer.">
<cfargument name="DSN" required="true" type="string">
<cfargument name="startDate" required="true" type="date">
<cfargument name="endDate" required="true" type="date">
<cfargument name="qOper" required="true" type="query">
<cfargument name="config" type="array" required="true" hint="Can lists of lists values">
 
<cfquery name="getDAgent" datasource="#arguments.DSN#">
  SELECT EID,
   CASE
    WHEN #arguments.config[1]#(s.score) IS NULL THEN 0
    ELSE #arguments.config[1]#(s.score) 
   END As Value,
   CASE
    WHEN #arguments.config[1]#(s.score) IS NULL then 1
         ELSE 0
   END As Invalid
  FROM ops$rms.RESOURCE_SCORE s,  ops$rms.REF_METRIC m
  WHERE
   s.Metric_Code = m.Metric_Code
   And s.End_Date BETWEEN #createodbcdate(arguments.StartDate)#
   AND #createodbcdate(arguments.EndDate)#
   AND lower(trim(m.sc_metric_code)) = lower('#trim(arguments.config[2])#')
   AND s.EID IN (<cfqueryparam value="#ValueList(arguments.qOper.EID)#" cfsqltype="CF_SQL_CHAR" list="Yes">)
   GROUP BY s.EID
   ORDER BY s.EID;
  
  UPDATE ops$rms.RESOURCE_SCORE
  SET 
   sc_upload_by = upper('#trim(session.MyState.CURRENTLOGIN.EID)#'),
   sc_upload_on = SYSDATE
  WHERE
   End_Date BETWEEN #createodbcdate(arguments.StartDate)#
   AND #createodbcdate(arguments.EndDate)#
   AND metric_code =(
          select metric_code
          from ops$rms.REF_METRIC
                    where lower(trim(sc_metric_code)) = lower('#trim(arguments.config[2])#')
          )                         
   AND EID IN (<cfqueryparam value="#ValueList(arguments.qOper.EID)#" cfsqltype="CF_SQL_CHAR" list="Yes">)
</cfquery>

<cfreturn getDAgent>
</cffunction>

This topic has been closed for replies.
Correct answer Adam Cameron.

What is the actual resolved SQL string that is sent to the DB (after the CF variables are resolved)?

What happens if you put each query in a separate <cfquery> tag (and wrap 'em in <cftransaction> tags!)?

What happens if you use <cfqueryparam> tags for your parameters rather than hard-coding them in your SQL string?

--

Adam

1 reply

Adam Cameron.Correct answer
Inspiring
September 23, 2009

What is the actual resolved SQL string that is sent to the DB (after the CF variables are resolved)?

What happens if you put each query in a separate <cfquery> tag (and wrap 'em in <cftransaction> tags!)?

What happens if you use <cfqueryparam> tags for your parameters rather than hard-coding them in your SQL string?

--

Adam

emmim44Author
Inspiring
September 24, 2009

I resolved already,I separated them and then used the cfqueryparam. Thank u

Inspiring
September 24, 2009

Cool.

If you need these two queries to run as a unit, don't forget to put <cftransaction> around the two <cfquery> tags.

--

Adam