CODEWITHSUNDEEP
coldfusioncoldfusion-10
NoSoup4you
NoSoup4you

Reputation: 668

conditional query in coldfusion

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 ?

Upvotes: 0

Views: 778

Answers (2)

Ronnie Kumar
Ronnie Kumar

Reputation: 635

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>

Upvotes: 1

Chris Tierney
Chris Tierney

Reputation: 1549

I believe the error causing you the issue lies in:

<cfset isupcount = #ISUPResult.RecordCount#>

From a quick look of your code, try using instead:

<cfset isUpCount = isUpStatus.recordCount>

But in addition please look at the comments above, especially joins.

Upvotes: 0

Related Questions