conditional query in coldfusion

Question

I need to provide some status on items in my table which I do in the last column of my table. First I go and query one table to see if I have a confirmation for the item .

<cfquery name="focnotice" datasource="******" result="FocResult">
    SELECT ecspc  
    FROM   tbl_CNR_H 
    WHERE  icsc = '#myarray[i].ICSC#' 
    AND    asr_no = '#myarray[i].ASR#'
</cfquery>

The ECSPC is a field in my Table, so logic is see if there is a record. If so, see if the ECSPC value is something other then "". If so, query another table to see if there is a matching record for this ECSPC.

<cfset ISUPStatus = "#focnotice.ecspc#">
<cfif ISUPStatus NEQ "">
    <cfquery name="isupStatus" datasource="******" result="ISUPResult">
        select * 
        from   tbl_ISUP 
        where  dpc = '#ISUPStatus#'
    </cfquery>

    <cfset isupcount = #ISUPResult.RecordCount#>
    <cfif #isupcount# GT 0>
        <cfset ISUPorder = "Yes">
    <cfelse>
        <cfset ISUPorder = "No">
    </cfif>

<cfelse>
    <cfset ISUPorder = "No">
</cfif>

I get the following error in my debug

Complex object types cannot be converted to simple values.

The expression has requested a variable or an intermediate expression result as a simple value. However, the result cannot be converted to a simple value. Simple values are strings, numbers, boolean values, and date/time values. Queries, arrays, and COM objects are examples of complex values. The most likely cause of the error is that you tried to use a complex value as a simple one. For example, you tried to use a query variable in a cfif tag.

What am I missing here ?

Exactly which line is throwing the error? Why not just use a sql JOIN instead of all the if/else logic? — Leigh
I'm looking at your question, but some glaring coding issues make me want to comment on them: 1. <cfset ISUPStatus = focnotice.ecspc> (notice quotes and hash) 5. use <cfqueryparam>, don't use "*" - instead use column names 9. <cfset isupcount = ISUPResult.RecordCount> (notice hash) 10. <cfif isupcount> (notice shorthand and hash) — Chris Tierney
Ok, I have found the problem, which was due to crappy codding on my side. I used <cfset ISUPStatus = "#focnotice.ecspc#"> as well gave the new query the name cfquery name="isupStatus" which was the issue. Original it was either "" or had a value but once I used the same name for query name it became complex I could no longer just display it. So I change queryname and that did the job and works as expected — NoSoup4you
A few other things you should look into for improving the code 1) Using a JOIN instead of all that if/else logic would simplify the code a lot 2) Always use cfqueryparam on variable query parameters. Especially on queries executed multiple times. It uses bind variables to improve performance, and more importantly protects your database against sql injection. — Leigh

Ronnie Kumar Ronnie Kumar · Accepted Answer · 2014-12-08T20:07:43

You are passing invalid parameter into the Query "myarray[i].ICSC",'#myarray[i].ASR#'. You need to specify what index of array you are using.

<cfquery name="focnotice" datasource="*******" result="FocResult">
 Select ecspc
 From tbl_CNR_H
 Where icsc = <cfqueryparam cfsqltype="cf_sql_varchar" value="#myarray[1].ICSC#">
        AND
      asr_no = <cfqueryparam cfsqltype="cf_sql_varchar" value="#myarray[1].ASR#"> 
</cfquery>

conditional query in coldfusion

2 Answers