troubleshooting Question

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

Avatar of henderxe
henderxe asked on
ColdFusion LanguageAJAX
5 Comments2 Solutions2282 ViewsLast Modified:
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!
ASKER CERTIFIED SOLUTION
Brijesh Chauhan
Staff IT Engineer

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 2 Answers and 5 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 2 Answers and 5 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros