ORA-01790: expression must have same datatype as corresponding expression

Hi All:

    I cannot find what is causing the following error.   It is supposed to return counts (e.g.
      {"COLUMNS":["SOLICIT","CONTACT","DECEASED","LOST"],"DATA":[[348,381,0,0]]})

===================  START ERROR  =============================
Detail
      [Macromedia][Oracle JDBC Driver][Oracle]ORA-01790: expression must have same datatype as corresponding expression
Extended Info
Tag Context
            
      R:\aihtc\ART_TEST\model\idListGeneration\idListGenerationDAO.cfc (144)<br>
      R:\aihtc\ART_TEST\views\cfm\exportReports\idListGeneration\getSolicitCount.cfm (54)<br>
      C:\inetpub\wwwroot\MachII\framework\ViewContext.cfc (107)<br>
      C:\inetpub\wwwroot\MachII\framework\EventContext.cfc (468)<br>
      C:\inetpub\wwwroot\MachII\framework\commands\ViewPageCommand.cfc (85)<br>
      C:\inetpub\wwwroot\MachII\framework\EventHandler.cfc (81)<br>
      C:\inetpub\wwwroot\MachII\framework\RequestHandler.cfc (379)<br>
      C:\inetpub\wwwroot\MachII\framework\RequestHandler.cfc (327)<br>
      C:\inetpub\wwwroot\MachII\framework\RequestHandler.cfc (271)<br>
      C:\inetpub\wwwroot\MachII\framework\RequestHandler.cfc (201)<br>
      C:\inetpub\wwwroot\MachII\mach-ii.cfm (123)<br>
      R:\aihtc\ART_TEST\index.cfm (9)<br>
===================  END ERROR  =============================

  Here's the trail:

    NOTE:  Sorry .  .  . I don't know how to "paste" so that the data scrolls.

    1.  Clicking on a form (once filled in)  - "getSolicitCount.cfm"   form below  .  .  .

    NOTE:  Line 54 is marked.

  =================  START  getSolicitCount.cfm Form ======================

<cfif url.event eq "generateIDLIst">
            <cfset db = StructNew() />
        <cfset  db.dbname = getProperty("dbName")>  <!--- getting dbname from machii.xml  --->
        <cfset db.dbUserName = getProperty("dbUserName")>
        <cfset  db.dbPassword = getProperty("dbPassword")>
        <cfset rootFolder = getProperty("rootFolder")>
        <cfset  queryToCSV = createObject("component","#rootFolder#.utils.queryToCSV").init()/>
        <cfoutput>
            <cfset  idListGenerationObj       =  createObject("component","#rootFolder#.model.idListGeneration.idListGenerationDAO").init(db.dbname,db.dbUserName,db.dbPassword)>
        </cfoutput>
        <cfset getSolicitCount = idListGenerationObj.getIDlist(url.appealOrProgCode,url.isSolicit,url.queryID,url.typeOfSolicit,url.hiIDObj,url.from,url.mailingList,url.contactType)>  
         <!--- <cfdump var="#getSolicitCount#"><cfabort>   RETURNS DATA  w/ myQuery --->
        <cfset columnList = ArraytoList(getSolicitCount.GetColumnNames())>
             <!--- <cfdump var="#columnList#"><cfabort> RETURNS COLUMN NAMES --->
        <cfset strOutput = queryToCSV.QueryToCSV(
                                               Query=getSolicitCount,  
                                               Fields=columnList
                                               ) />       
       <!--- <cfdump var="#strOutput#"><cfabort> WORKS  -  COMBINES DATA AND COLUMN NAMES --->                                                         
                                          
      <cfheader name="content-disposition" value="attachment; filename=contactList.CSV" />
      <cfcontent type="text/plain" /><cfoutput>#strOutput#</cfoutput>
      
 <cfelse>
 <!--- <cfdump var="#url.event#"><cfabort> --->
   <cfprocessingdirective suppresswhitespace="yes" >
        <cfsetting enablecfoutputonly="1" showdebugoutput="0">
        <cfcontent type="application/x-javascript">
        <cfsilent>
            <cfparam name="FORM.appealOrProgCode" default="" />  <!--- CHANGED from "appealCode" 4/26/2011 --->
            <cfparam name="FORM.isSolicit" default="" />
            <cfparam name="FORM.queryID" default="" />
            <cfparam name="FORM.typeOfSolicit" default="" />
            <cfparam name="FORM.hiIDObj" default="" />
            <cfparam name="FORM.from" default="" />
<<<<<<< .mine
            <!---<cfdump var="#form#"><cfabort>--->
=======
            <!---<cfdump var="#form#"><cfabort> --->
>>>>>>> .r941
            <cfset db = StructNew() />
            <cfset  db.dbname = getProperty("dbName")>  <!--- getting dbname from machii.xml  --->
            <cfset db.dbUserName = getProperty("dbUserName")>
            <cfset  db.dbPassword = getProperty("dbPassword")>
            <cfset rootFolder = getProperty("rootFolder")>
     
            <cfset tmpArray    = ArrayNew(1) > 
           <cfoutput>
           <cfset  idListGenerationObj             =  createObject("component","#rootFolder#.model.idListGeneration.idListGenerationDAO").init(db.dbname,db.dbUserName,db.dbPassword)>
            </cfoutput>
LINE 54    <cfset getSolicitCount = idListGenerationObj.getSolicitCount(FORM.appealOrProgCode,FORM.isSolicit,FORM.queryID,FORM.typeOfSolicit,FORM.hiIDObj,FORM.from)>
                   <!--- <cfdump var="#getSolicitCount#"><cfabort> --->  
                <cfset data =   serializeJson(getSolicitCount)>
                <cfcontent type="application/json"  reset="true">
        </cfsilent>
         <cfoutput>#data# </cfoutput>
    </cfprocessingdirective>
 </cfif>

====================== END "getSolocitCount.cfm" Form =======================

      retSolocitCount.cfm calls the following function (SHOWSTEP4):

==================== START  "SHOWSTEP4"  FUNCTION  ======================
function showStep4(queryID,hiIDObj,isGenerateList,from)      
                  {  
                        document.getElementById('countDetailsStep4').style.display = "block";
                        document.getElementById('countDetailsStep5').style.display = "block";
                         if(isGenerateList == 'Yes')
                         {
                              // alert(hiIDObj)
                               hiIDObj = document.getElementById(hiIDObj).checked
                         }
                        // alert(hiIDObj)
                         var appealOrProgCode = "";
                         var typeOfSolicit = "";
                         var isSolicit  = getCheckedValue('isSolicitYesNoDiv')
                        if(isSolicit == "Yes") {      
                              var typeOfSolicit = getCheckedValue('typeOfSolicitationsDiv')
                              //alert(typeOfSolicit)
                              var isSolicit = getCheckedValue('isSolicitYesNoDiv')
                              var appealOrProgCode = document.getElementById('solicitYesAppealCode').value
                              solicitCountDetails(queryID,typeOfSolicit,appealOrProgCode,isSolicit,hiIDObj,from)
                              document.cookie = "appealOrProgCode="+document.getElementById('solicitYesAppealCode').value;
                              document.cookie = "hiIDObj="+hiIDObj;
                              
                              if (reloadStep4Grid == 0)      {                              
                                    fillmyIDList(queryID,typeOfSolicit,appealOrProgCode,isSolicit,hiIDObj,from);
                                    $("#includeMailingList").multiSelect({ oneOrMoreSelected: '*' });      
                              } else {
                                    $("#list_idListGeneration").trigger("reloadGrid");
                              }
                              reloadStep4Grid = 1;
                              
                        }
                        else
                        {      
                              var typeOfSolicit = getCheckedValue('typdOfInfoNeeded')
                              var appealOrProgCode = document.getElementById('giftAppealsProgramDIList1_hdn').value  
                                          //var appealOrProgCode = document.getElementById('includeMailingList_hdn').value
                              var isSolicit = getCheckedValue('isSolicitYesNoDiv') ;
                              solicitCountDetails(queryID,typeOfSolicit,appealOrProgCode,isSolicit,hiIDObj,from);                                                                                    
                              //fillmyIDList(queryID,typeOfSolicit,appealOrProgCode,isSolicit,hiIDObj,from);
                              document.cookie = "appealOrProgCode="+document.getElementById('giftAppealsProgramDIList1_hdn').value;
                              document.cookie = "hiIDObj="+hiIDObj;
                              
                              if (reloadStep4Grid == 0)      {                              
                                    fillmyIDList(queryID,typeOfSolicit,appealOrProgCode,isSolicit,hiIDObj,from);
                                    $("#includeMailingList").multiSelect({ oneOrMoreSelected: '*' });      
                              } else {
                                    $("#list_idListGeneration").trigger("reloadGrid");
                              }
                              reloadStep4Grid = 1;
                              
                        }
                        if(isGenerateList == 'Yes') {
                        var mailingList  = "xyz";
                                 mailingList = document.getElementById('includeMailingList_hdn').value
                              url  = "index.cfm?event=generateIDLIst&queryID="+queryID+"&typeOfSolicit="+typeOfSolicit+"&appealOrProgCode="+appealOrProgCode+"&isSolicit="+isSolicit+"&hiIDObj="+hiIDObj
                                          +"&from="+from+"&mailingList="+mailingList
                              window.open(url)
                        }
                  
            }
            
            function includeHIIDInContactlist(hiIDObj)
                  {
                        //alert(hiIDObj.checked)
                        if(hiIDObj.checked)
                              {
                              //      alert('Include HiId')
                              }
                        else
                              {
                                    //alert('removeHiID')
                              }
                  }
            function validateAppealCode(fldObj)
                  {
                        var fldValue = fldObj.value;
                        var fldValuetoText = fldValue.substring(0,5)
                        if(fldValue.length > 5)
                              {
                                    // alert("Appeal code should be max 5 characters")
                                    //  document.getElementById('solicitYesAppealCode').value = fldValuetoText
                                      document.getElementById('generateCOunt').style.display = "none";  
                                      document.getElementById('idMaxLimitErrorDisplay').style.display = "block";
                              }
                              else if(fldValue.length == 5)
                              {
                                    document.getElementById('generateCOunt').style.display = "block";
                                    document.getElementById('idMaxLimitErrorDisplay').style.display = "none";
                              }
                              else if(fldValue.length < 5)
                              {
                                    document.getElementById('generateCOunt').style.display = "none";
                                    document.getElementById('idMaxLimitErrorDisplay').style.display = "none";
                              }
                         
                  }
     function  showListGenerateButton      ()
             {
                  document.getElementById('generateCOunt').style.display = "block";
            }
==================== END  "SHOWSTEP4"  FUNCTION  ======================

2..   SHOWSTEP4 calls function SOLICITCOUNT DETAILS below:

==================== START "SOLICITCOUNTDETAILS"  FUNCTION  ===============

function solicitCountDetails(queryID,typeOfSolicit,appealCode,isSolicit,hiIDObj,from) {       
      document.getElementById('solicit_generateCount').style.display = "none";
      document.getElementById('solicitCount_processing').style.display = "block";       
      //$("#"+type).html("");      
      //alert(appealCode); return;
      //var unicode=evt.keyCode? evt.keyCode : evt.charCode      
      // alert(unicode)
 
       $.ajax(
                  {
                        type: "post",
                  //      url:  "views/cfm/AJAXCalls/getFormData.cfm",
                         url:  "index.cfm?event=getSolicitCount",
                        data: {
                              appealCode : appealCode,
                              isSolicit : isSolicit,
                              queryID : queryID,
                              typeOfSolicit  :  typeOfSolicit ,
                              hiIDObj :hiIDObj,
                              from :from
                        },
                        dataType: "json",
                        success: function(objResponse)
                              {       
                                     //alert(objResponse); return;
                                     var solictCount = objResponse.DATA[0][0];
                                     var idListContactCount = objResponse.DATA[0][1];
                                     if(idListContactCount == null)
                                           {
                                                idListContactCount = 0;
                                          }
                                     var idListDeceCount = objResponse.DATA[0][2];
                                      if(idListDeceCount == null)
                                           {
                                                idListDeceCount = 0;
                                          }
                                     var idListLostEntity = objResponse.DATA[0][3];
                                      if(idListLostEntity == null)
                                           {
                                                idListLostEntity = 0;
                                          }
                                     $("#idListSolicitCount").html("");
                                    $("#idListSolicitCount").html(solictCount);
                                    $("#idListContactCount").html("");
                                    $("#idListContactCount").html(idListContactCount);
                                    $("#idListDeceCount").html("");
                                    $("#idListDeceCount").html(idListDeceCount);
                                    $("#idListLostEntity").html("");
                                    $("#idListLostEntity").html(idListLostEntity);
                              
                                    document.getElementById('solicit_generateCount').style.display = "block";
                                    document.getElementById('solicitCount_processing').style.display = "none";       
                              },
                        error: function(xhr, objRequest, strError){
                               //alert(xhr.responseText); return;
                               document.getElementById('solicit_generateCount').style.display = "block";
                               document.getElementById('solicitCount_processing').style.display = "none";       
                         
                              }
                  }
            );
      }
==================== END  "SOLICITCOUNTDETAILS"  FUNCTION  ===============

3.   DOLICITCOUNTDETAILS eventually points to a Coldfusion METHOD ("getSolicitCount"), via a Mach-ii event,  which ultimately displays the error above.  LINE 144 is maeked below.

===========  START "IDLISTGENERATIONDAO.CFC"   (GETDOLICITCOUNT)  =======  

<cffunction name="getSolicitCount" access="public" returntype="any"><!---   Populate city based on the state we have selected --->  
            <cfargument name="appealOrProgCode" type="string" required="yes">  <!--- CHANGE NAME ???? --->
        <cfargument name="isSolicit" type="string" required="yes">
            <cfargument name="queryID" type="string" required="yes">
        <cfargument name="solicitType" type="string" required="yes">
        <cfargument name="hiIDObj" type="string" required="yes">
        <cfargument name="from" type="string" required="yes">
        <cfset typecode = ""> 
       
            <cfif from eq "myQuery">
             <cfquery name="getIDListQueryForQueryID"  datasource="#db.dbName#"  username="#db.dbUserName#" password="#db.dbPassword#">
                          select  idList_query  from art_query where query_id = #queryID#
               </cfquery>  
              <cfloop query="getIDListQueryForQueryID" startrow="1">
                    <cfset queryFromCLOB = getIDListQueryForQueryID.idList_query>
              </cfloop>
              <cfset queryFromCLOB = #Replace(queryFromCLOB, "''", "'", "all")# >
            <!--- <cfdump var="#getIDListQueryForQueryID#"><cfabort> SHOWS TWO(2) DIFFERENT FORMATS--->
        <cfelse>
                <cftry>
                  <cfquery name="getIDsFromID"  datasource="#db.dbName#"  username="#db.dbUserName#" password="#db.dbPassword#">
                        SELECT
                          list_values
                    FROM
                          art_idlist
                    WHERE
                          idList_id = #queryID#
                  </cfquery>
                         
                          <cfset listID = getIDsFromID.list_values >
                          <!--- <cfdump var="#listID#"><cfabort> --->
                  <cfstoredproc procedure="addIdLists"    datasource="#db.dbName#"  username="#db.dbUserName#" password="#db.dbPassword#">
                        <cfprocparam type="In"   cfsqltype="cf_sql_varchar"  variable="id_no"  value="#getIDsFromID.list_values#">
                  </cfstoredproc>  
                 <cfcatch type="any" >
                    <cfset getProgCodeFromAppealCode = "#cfcatch.sql#">
                    <cfreturn getProgCodeFromAppealCode>
                 </cfcatch>
             </cftry>    
                 <!--- <cfset queryFromCLOB =     " select  lpad(vals,10,'0') id_number from tempIdlists "> --->
                <cfset queryFromCLOB =   "select distinct to_number(vals) as  id_number from tempIdlists ">
                        <cfset queryFromCLOB = #Replace(queryFromCLOB, "''", "'", "all")# >
        </cfif>      
           
          <cfif  isSolicit  eq "Yes">
           <cfif solicitType eq "email">
                <cfset typecode = "'EZZ','TEZ','TZZ','ZZZ' "> 
           <cfelseif solicitType eq "postal">
                <cfset typecode = "'MZZ','TMZ','TZZ','ZZZ' "> 
           <cfelseif solicitType eq "telemarketing">
                <cfset typecode = "'PZZ','TPZ','TZZ','ZZZ' ">
           </cfif>
           <cfquery name="getProgCodeFromAppealCode"  datasource="#db.dbName#"  username="#db.dbUserName#" password="#db.dbPassword#">
               select program_code from advance.tms_program
                where program_code in (select program_code from advance.appeal_header  where appeal_code in ('#appealOrProgCode#'))
            </cfquery>
            <cfset programCode = "#getProgCodeFromAppealCode.program_code#">
        <cfelse>
            <cfif solicitType eq "email">
                <cfset typecode = "'EZZ','ZZZ' "> 
           <cfelseif solicitType eq "postal">
                <cfset typecode = "'MZZ','ZZZ' "> 
           <cfelseif solicitType eq "telemarketing">
                <cfset typecode = "'PZZ','ZZZ' ">
           </cfif>    
           <cfset programCode = "#appealOrProgCode#">
        </cfif>  
               
        <cfset queryToGetIDFromSolicit = "
            select qry1.id_number from (#queryFromCLOB# ) qry1
             minus  
            SELECT distinct
                    advance.handling.id_number
                     from
                        (#queryFromCLOB# )msu_temp_ai_coni_hnd1,  
                     advance.handling
                     where
                        msu_temp_ai_coni_hnd1.id_number = advance.handling.id_number and
                          advance.handling.hnd_status_code = 'E'
                          and advance.handling.hnd_type_code in (#preserveSingleQuotes(typecode)#)
                          and (
                               advance.handling.lift_dt = '00000000'
                               or advance.handling.lift_dt = ' '
                               or advance.handling.lift_dt > '#dateFormat(now(),'YYYYMMDD')#'
                               )
                                and
                                (
                                rtrim(advance.handling.program_code) is null
                          or advance.handling.program_code in ('#programCode#')  ) "/>
                          <!--- JUST ADDED
                          <cfset queryToGetIDFromSolicit = #Replace(queryToGetIDFromSolicit, "''", "'", "all")# >  --->
                         
        <cfset hiIDUnionQuery = "  union select hi_id from
                                    ART_MANUSCRIPT where id_number in(
             select qry1.id_number from (#queryFromCLOB# ) qry1
            minus    
                           SELECT distinct
                    advance.handling.id_number
                     from
                        (#queryFromCLOB# )msu_temp_ai_coni_hnd1,  
                     advance.handling
                     where
                        msu_temp_ai_coni_hnd1.id_number = advance.handling.id_number and
                          advance.handling.hnd_status_code = 'E'
                          and advance.handling.hnd_type_code in (#preserveSingleQuotes(typecode)#)
                          and (
                               advance.handling.lift_dt = '00000000'
                               or advance.handling.lift_dt = ' '
                               or advance.handling.lift_dt > '#dateFormat(now(),'YYYYMMDD')#'
                               )
                                and
                                (
                                rtrim(advance.handling.program_code) is null
                          or advance.handling.program_code in ('#programCode#')  ) "/>
                         
                          <!--- JUST ADDED
                          <cfset hiIDUnionQuery = #Replace(hiIDUnionQuery, "''", "'", "all")# > --->
        <!--- <cftry> --->
            
            <cfquery name="resultSet"  datasource="#db.dbName#"  username="#db.dbUserName#" password="#db.dbPassword#">
                            <!--- select '23' as solicit, '24' as contact, '25' as deceased, '26' as Lost from dual --->
                            select  
                                     sum(decode(solicitation,'Y','1','0')) as solicit,
                                sum(decode(deceased||lost_status,'00','1','0'))as contact ,
                                sum(deceased) deceased ,  
                                sum(lost_status) Lost
                                  from ART_MANUSCRIPT
                                where id_number in  
                                       ((#preserveSingleQuotes(queryToGetIDFromSolicit)#)
                                        <cfif hiIDObj eq "true">
 LINE:  144                               #preserveSingleQuotes(hiIDUnionQuery)#)
                                      </cfif>
                                       )  
            </cfquery>
        <!--- <cfcatch type="any">
              <cfset error = "#cfcatch.sql#">
                 <cfreturn error/>
                  <cfcatch type="any">
              <cfrethrow>
        </cfcatch>
        </cftry>   --->      
            <cfreturn resultSet>
      </cffunction>

==============  START "IDLISTGENERATIONDAO.CFC"  (GETDOLICITCOUNT) ======

Any assistane is grestly appreciated!

Thanks!