Home » SQL & PL/SQL » SQL & PL/SQL » need a dummy column
need a dummy column [message #222946] Wed, 07 March 2007 00:42 Go to next message
Messages: 47
Registered: March 2007
I need to calculate the % value for share out of emp table.
The only additional info I have is total amount, ie I need to pass within the query.

my formula should be amount/sal and want to display % in another column.

how to pass the value '10000' from query.

select empno,ename,job,mgr,hiredate,sal,comm,deptno, '10000' total from scott.emp

Re: need a dummy column [message #222974 is a reply to message #222946] Wed, 07 March 2007 01:47 Go to previous messageGo to next message
Messages: 913
Registered: March 2005
Location: Netherlands
Senior Member
It's not clear to me what you need exactly, but let's take one line at a time:

I need to calculate the % value for share out of emp table.

This can be done using analytics, probably you mean ratio_to_report:
SQL> SELECT t.last_name
  2        ,t.salary
  3        ,ratio_to_report(t.salary) over() perc_of_total
  4  FROM   employees t
  5  where t.department_id = 100
  6  /

LAST_NAME                     SALARY PERC_OF_TOTAL
------------------------- ---------- -------------
Greenberg                   12000,00 0,23255813953
Faviet                       9000,00 0,17441860465
Chen                         8200,00 0,15891472868
Sciarra                      7700,00 0,14922480620
Urman                        7800,00 0,15116279069
Popp                         6900,00 0,13372093023

6 rows selected

(added the department_id = 100 to create a nice small example but principle works the same for any number of records).

You can play with the over() part of this calculation, add partition by department_id for example and see what happens.

The rest of the requirement I don't understand. You want the salary as a percentage of 1000? Why?
Re: need a dummy column [message #223012 is a reply to message #222946] Wed, 07 March 2007 03:17 Go to previous message
Messages: 5
Registered: February 2007
Location: Pune
Junior Member
I didn't get what you exactly required.

But I think u can use substitution variable.

select last_name,salary,&x from employees;

after executing query, values of x will be asked by environment. you can write salary/1000 in text box. It will display following columns

last_name salary salary/1000
King 2000 2
and so on.

You can use this, If this is not your required answer please ignore this reply.
Previous Topic: PL/SQL - Getting counts after Merge Statement completion.
Next Topic: Error while using UTL_FILE in Oracle10g
Goto Forum:

Current Time: Wed Aug 23 10:50:13 CDT 2017

Total time taken to generate the page: 0.15352 seconds