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

henderxe
henderxe used Ask the Experts™
on
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

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Staff IT Engineer
Commented:
This is what the error code explanation says

ORA-01790: expression must have same datatype as corresponding expression
Cause: A SELECT list item corresponds to a SELECT list item with a different datatype in another query of the same set expression.
Action: Check that all corresponding SELECT list items have the same datatypes. Use the TO_NUMBER, TO_CHAR, and TO_DATE functions to do explicit data conversions.

http://ora-01790.ora-code.com/
Brijesh ChauhanStaff IT Engineer

Commented:
Here is more explanation of the error.. is there a select in which you are using UNION ?
http://www.techonthenet.com/oracle/errors/ora01790.php 
Top Expert 2011
Commented:
as pointed by brijeshchauhan, this is a UNION error in Oracle. 

- i might read your code wrongly but kindly note that when using UNION, both queries need the same amount of columns. the datatype also must be the same match for columns between the two query.
- in your code, you are selecting only one column on "hi_id from ART_MANUSCRIPT" and six column on "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". 
- i'm not sure what you are trying to achieve in your query, but i would suggest you to open up a new question and concentrate on the query in Oracle zone. then restructure back your code based on that query solution.

- more on Oracle union if needed:
http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/queries004.htm
http://www.techonthenet.com/sql/union.php

Author

Commented:
All:

   Really appreciate your comments.   I had looked up the error, and had a fairly good idea of what the problem was, but could not determine "where" the differences in data types existed.  

I've tried manipulating both to_char, and to_number many times to no avail.

And to brijeshchauhan's question, yes, there are a few "unions", and "minus" in the code.  The error is right after the "minus", so that's where I was focused.

The query mentioned by OP_Zaharin merely gets counts in each of the categories based eventually on ID_NUMBER, which is identical to HI_ID, except HI_ID refers to joint (e.g. married), while ID_NUMBER is the unique identifier in the database.

   But you both seem to zero in on "unions", in particular, so I'll go back and take a closer look at them.

   I also apologize for not presenting it better.  The code looks a mess -- realized it after it was posted.

  But again, thank you both for a clue that may help me get this resolved.  If it turns out to be another issue, I'll re-post.

Take care!
Top Expert 2011

Commented:
"The code looks a mess -- realized it after it was posted."
- you can use the code tag to wrapping you code by selecting your whole code then click on the code button (above the textarea). this will give you the scroll within the code.

cheers!
OP

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial