2 Replies Latest reply: May 1, 2012 2:24 PM by Etbin RSS

    ora-01790 Expression must have same datatype

    896188
      select  'sal' as A, sal as B, sal as C, sal as D
      from
      (Select sum(sal) sal from emp)
      union all
      select
        'comm', comm, comm, comm
      from
      (Select sum(comm) comm from emp)
      union all
      select rpad('-',10,'-') ,rpad('-',10,'-'),rpad('-',10,'-'),rpad('-',10,'-') from emp;
      Getting following error when running the above query. I know that it is because of rpad('-',10,'-') what is the best way to acheive expected output

      I would like to have the result with underline. what is the best way to acheive it.

      Expected output:
      I am developing report.
      I need to have the following oupput.
      How can i put underline after the records.
       A              B        C             D        
      sal         29025       29025     29025
      comm     2200    2200     2200
      --------------------------------------------------
        • 1. Re: ora-01790 Expression must have same datatype
          Solomon Yakobson
          SQL> select  'sal' as A, sal as B, sal as C, sal as D
            2  from
            3  (Select sum(sal) sal from emp)
            4  union all
            5  select
            6    'comm', comm, comm, comm
            7  from
            8  (Select sum(comm) comm from emp)
            9  union all
           10  select rpad('-',10,'-') ,rpad('-',10,'-'),rpad('-',10,'-'),rpad('-',10,'-') from emp;
            'comm', comm, comm, comm
                    *
          ERROR at line 6:
          ORA-01790: expression must have same datatype as corresponding expression
          
          
          SQL> select  'sal' as A, to_char(sal) as B, to_char(sal) as C, to_char(sal) as D
            2  from
            3  (Select sum(sal) sal from emp)
            4  union all
            5  select
            6    'comm', to_char(comm), to_char(comm), to_char(comm)
            7  from
            8  (Select sum(comm) comm from emp)
            9  union all
           10  select rpad('-',10,'-') ,rpad('-',10,'-'),rpad('-',10,'-'),rpad('-',10,'-') from emp;
          
          A          B                                        C                                        D
          ---------- ---------------------------------------- ---------------------------------------- ------------------
          sal        29025                                    29025                                    29025
          comm       2200                                     2200                                     2200
          ---------- ----------                               ----------                               ----------
          ---------- ----------                               ----------                               ----------
          ---------- ----------                               ----------                               ----------
          ---------- ----------                               ----------                               ----------
          ---------- ----------                               ----------                               ----------
          ---------- ----------                               ----------                               ----------
          ---------- ----------                               ----------                               ----------
          ---------- ----------                               ----------                               ----------
          ---------- ----------                               ----------                               ----------
          
          A          B                                        C                                        D
          ---------- ---------------------------------------- ---------------------------------------- ------------------
          ---------- ----------                               ----------                               ----------
          ---------- ----------                               ----------                               ----------
          ---------- ----------                               ----------                               ----------
          ---------- ----------                               ----------                               ----------
          ---------- ----------                               ----------                               ----------
          
          16 rows selected.
          
          SQL> 
          SY.
          • 2. Re: ora-01790 Expression must have same datatype
            Etbin
            I would like to have the result with underline. what is the best way to acheive it.
            that's usually left to the client.

            Regards

            Etbin