ERPstuff - Oracle EBS Community

Dear all,

How to print the Total amount/salary at the bottom of line without using break on and compute commands.

Hi there

This is the simplest example but it'll give you some idea:

SELECT ename, sal
FROM emp
UNION
SELECT '__Total -> ' , sum(sal)
FROM emp;

Have fun.

Dear Amirtai,

Thanks for given idea. But, in my query date column is present at this scenario how to write a query. Kindly suggest to me. For more clarification i mentioned one simple query in below.

select ename,sal,job,hiredate from emp
union
select 'Total',sum(sal),'', <font color="red">DATE Column </font id="red"> from emp

In above example, how can a write same date expressioin(above red color).

What and where you want to achieve exactly like reports builder or what. tks

Hi there

I believe that should work fine for your scenario:

SELECT ename, sal, job, hiredate
FROM emp
UNION
SELECT '__Total -> ', sum(sal), ' ',hiredate
FROM emp
group by hiredate


Amir

Dear Amirtai,

I will agree with u. but in my scenario more than 15 columns are present including date columns. So that i want to display sum of amount at the last row without using GROUP BY clause.

Hello

You could use NULL for each column in the second query, for example:

select first_name,last_name,hiredate,sal
from l_employees
union
select 'total ->',null,null,sum(sal)
from l_employees

Amir

Dear Amirtai,

Thanks for prompt response. But, In my scenario <b>query takes a long time for execution</b>. Please let me know, how can reduce the execution time. And also i want to present a number data type instead of varchar type at the bottom of result row. I mentioned one example below

select ename,sal,hiredate from emp
union
select <font color="red">sum(case when sal<0 then 1 else 0 end)</font id="red">,sum(sal),sysdate from emp
In above example, the red color mentioned result is number data type but ENAME column is varchar type. Is possible to print the number data type in varchar type.

Hello

Please post the execution plan for your query and we'll see what could be done to optimize it.

The pre-condition for using UNION clause is, column type must match for each corresponding column in the second query or it can be NULL. You could convert the column type in your second query, for instance:

select ename,sal,hiredate
from emp
union
select to_char(count(case when sal > 0 then 1 else null end)),sum(sal),sysdate
from emp

Amir

Dear Amirtai,

I print the sum of salary at the last row. But my query takes a long time. Can u give me any suggestion regarding, how to reduce the execution time.(In my scenario more than 3000 rows selected)