Home » SQL & PL/SQL » SQL & PL/SQL » case and Grouping
case and Grouping [message #198703] Wed, 18 October 2006 05:16 Go to next message
sanjit
Messages: 65
Registered: November 2001
Member
I am doing a report and have a union and grouping on the basis og column called grouping

I have to list all the data first from emp table and then a summary line based out of filter, I have used case statement it is giving me ERROR ORA-01790: expression must have same datatype as corresponding expression

any one assit me in correct way to use the case with Union
as in second query since dept 10 thus i am giving 1 as default

select  case when deptno in (10) then 1
			 when deptno in (20) then 2
            when deptno in (30,40)then 3
            else 7 end grouping, 
            empno,ename, job,mgr,hiredate, sal, comm,deptno
from scott.emp
union
select '1' grouping,null empno,null ename, null job, null mgr, null hiredate, sum , null comm,deptno from
 (
select sum(sal) sum,deptno
from scott.emp
where deptno=10
group by deptno
)
Re: case and Grouping [message #198723 is a reply to message #198703] Wed, 18 October 2006 06:12 Go to previous message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
While using UNION
the data types of the corresponding columns should be same in all the select statements.

So, you have to use "select 1 grouping" instead of "select '1' grouping"

By
Vamsi.
Previous Topic: best possibilities
Next Topic: In SQL/Oracle 'SELECT' command belongs to which category?
Goto Forum:
  


Current Time: Sat Dec 03 03:40:23 CST 2016

Total time taken to generate the page: 0.05524 seconds