4 Replies Latest reply: Nov 18, 2012 11:27 PM by jeneesh RSS

    unpivot query error

    gauty
      Hi,

      I am running an oracle 11gR2 Database.

      I have a table of the structure..
      CREATE TABLE "RP_RESOLUTION_MASTER"
      ( "RM_ID" NUMBER,
      "SR_ID" NUMBER,
      "REQUEST_STATUS" VARCHAR2(200 BYTE),
      "COMMENTS" VARCHAR2(4000 BYTE),
      "UPDATED_ON" DATE,
      "UPDATED_BY" VARCHAR2(500 BYTE),
      "INTERNAL_COMMUNICATION" VARCHAR2(1 BYTE) DEFAULT 'N'
      );

      I am running an unpivot query on this.

      SELECT *
      From Rp_Resolution_Master
      Unpivot INCLUDE NULLS (
      value For measures In ( rm_id, request_status, Comments
      , updated_by, internal_communication)
      )
      where sr_id = 1004707
      And Updated_On = ( Select Max(Updated_On)
      From Rp_Resolution_Master
      Where Sr_Id = 1004707);

      But I get an error

      ORA-01790: expression must have same datatype as corresponding expression
      01790. 00000 - "expression must have same datatype as corresponding expression" *Cause:
      *Action: Error at Line: 3 Column: 51

      What is it that I am doing wrong here?
        • 1. Re: unpivot query error
          odie_63
          The columns you're trying to unpivot are not of the same datatype, hence the error.

          You have to convert them to a common datatype first :
          SQL> select empno, measure, val
            2  from scott.emp
            3  unpivot ( val for measure in (ename, sal) )
            4  where mgr = 7698
            5  ;
          unpivot ( val for measure in (ename, sal) )
                                               *
          ERROR at line 3:
          ORA-01790: expression must have same datatype as corresponding expression
          
          
          SQL> with emp2 as (
            2    select empno
            3         , mgr
            4         , ename
            5         , to_char(sal) as sal
            6    from scott.emp
            7  )
            8  select empno, measure, val
            9  from emp2
           10  unpivot ( val for measure in (ename, sal) )
           11  where mgr = 7698
           12  ;
          
               EMPNO MEASU VAL
          ---------- ----- ----------------------------------------
                7499 ENAME ALLEN
                7499 SAL   1600
                7521 ENAME WARD
                7521 SAL   1250
                7654 ENAME MARTIN
                7654 SAL   1250
                7844 ENAME TURNER
                7844 SAL   1500
                7900 ENAME JAMES
                7900 SAL   950
          
          10 rows selected.
          • 2. Re: unpivot query error
            gauty
            Thanks for that info..
            I was not knowing that..

            This leaves my question answered :) :)
            • 3. Re: unpivot query error
              831198
              hi,

              I'v a similar question, could someone help ??

              when i run the below code

              with T as (select to_char(sr_id) sr_id,(select ss_id from rp_resolve_sections where rt_id = (select rt_id from rp_sr_master where sr_id = 1011159)
              and lower(sub_section) = lower('Resolve Request')) ss_id,'Resolution Summary' sub_section,REQUEST_STATUS,COMMENTS,updated_by,updated_on
              from Rp_Resolution_Master where sr_id = 1011159)

              SELECT *
              From t
              UNPIVOT INCLUDE nulls (
              answer For field_label In ( request_status, Comments,updated_by,updated_on)
              )
              where sr_id = 1011159
              And Updated_On = ( Select Max(Updated_On)
              from RP_RESOLUTION_MASTER
              Where Sr_Id = 1011159 );

              i get this error
              ORA-00904: "UPDATED_ON": invalid identifier
              00904. 00000 - "%s: invalid identifier"

              wat's wrong in above code??
              • 4. Re: unpivot query error
                jeneesh
                Open a new thread.
                Provide sample data and expected output
                Use {noformat}
                {noformat} tags to format your code