We help IT Professionals succeed at work.
Get Started

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

henderxe
henderxe asked
on
2,277 Views
Last Modified: 2012-05-11
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!
Comment
Watch Question
Staff IT Engineer
Commented:
This problem has been solved!
Unlock 2 Answers and 5 Comments.
See Answers
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE