This discussion is archived
4 Replies Latest reply: Nov 18, 2012 9:27 PM by jeneesh RSS

unpivot query error

gauty Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    Thanks for that info..
    I was not knowing that..

    This leaves my question answered :) :)
  • 3. Re: unpivot query error
    831198 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    Open a new thread.
    Provide sample data and expected output
    Use {noformat}
    {noformat} tags to format your code                                                                                                                                                                                                                                        

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points