duvida funcao oracle

[maomelov]

Boa Noite

Precisava de escrever um query para determinar, para cada empregado da tabela EMP, quanto é que ele ganha a menos que o empregado que mais ganha. Por exemplo, se o empregado ganha 100 e o máximo salário é 1000, então o query deve listar 900.

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

7369 SMITH CLERK 7902 1980-12-17 800 20

7499 ALLEN SALESMAN 7698 1981-02-20 1600 300 30

7521 WARD SALESMAN 7698 1981-02-22 1250 500 30

7566 JONES MANAGER 7839 1981-04-02 2975 20

7654 MARTIN SALESMAN 7698 1981-09-28 1250 1400 30

7698 BLAKE MANAGER 7839 1981-05-01 2850 30

7782 CLARK MANAGER 7839 1981-06-09 2450 10

7788 SCOTT ANALYST 7566 1982-12-09 3000 20

7839 KING PRESIDENT 1981-11-17 5000 10

7844 TURNER SALESMAN 7698 1981-09-08 1500 0 30

7876 ADAMS CLERK 7788 1983-01-12 1100 20

7900 JAMES CLERK 7698 1981-12-03 950 30

7902 FORD ANALYST 7566 1981-12-03 3000 20

7934 MILLER CLERK 7782 1982-01-23 1300 10

Penso que terei de usar uma função analitica, mas nao faço ideia como.

Podem ajudar pf?

obrigado

[KTachyon]

Como não tenho praticamente nenhuma experiência em Oracle, não te posso ajudar com as funções analíticas.

A minha sugestão seria criar uma stored function que obtém o max(sal) e que é chamada pela query para ser feita a subtracção.

[rumbafum]

assim à partida e de forma mais simples poderias usar um with. Não é a forma mais prática nem com melhor performance mas faz o trabalho.

Não testei a query e remove as colunas que estiverem a mais

with maxEmpVal as (
 select * from (
   select EMPNO, SAL as MAXSAL
   from   EMP
   order by SAL desc)
 where rownum = 1
)
select cur.EMPNO,
	   cur.SAL,
	   mev.EMPNO,
	   mev.MAXSAL,
	   mev.MAXSAL - cur.SAL as SALDIF
from    EMP cur, maxEmpVal mev
where cur.EMPNO <> mev.EMPNO

[maomelov]

Como não tenho praticamente nenhuma experiência em Oracle, não te posso ajudar com as funções analíticas.

A minha sugestão seria criar uma stored function que obtém o max(sal) e que é chamada pela query para ser feita a subtracção.

Podes ajudar na query, tem erros, nao sei como resolver!!!

SET SERVEROUTPUT ON

DECLARE

CURSOR C1 IS

SELECT ENAME,SAL

FROM EMP

ORDER BY ENAME;

VMAXSAL EMP.SAL%TYPE;

RESULTADO EMP.SAL%TYPE;

VEMP EMP%ROWTYPE;

BEGIN

OPEN C1;

FETCH C1

INTO VEMP.ENAME,

VEMP.SAL;

CLOSE C1;

SELECT MAX(SAL)

INTO VMAXSAL

FROM EMP;

RESULTADO := (VMAXSAL)-(VEMP.SAL);

DBMS_OUTPUT.PUT_LINE(VEMP.ENAME||-||RESULTADO);

--DBMS_OUTPUT.PUT_LINE(VEMP.ENAME||-||VEMP.SAL);

END;

assim à partida e de forma mais simples poderias usar um with. Não é a forma mais prática nem com melhor performance mas faz o trabalho.

Não testei a query e remove as colunas que estiverem a mais

with maxEmpVal as (
select * from (
select EMPNO, SAL as MAXSAL
from EMP
order by SAL desc)
where rownum = 1
)
select cur.EMPNO,
	 cur.SAL,
	 mev.EMPNO,
	 mev.MAXSAL,
	 mev.MAXSAL - cur.SAL as SALDIF
from EMP cur, maxEmpVal mev
where cur.EMPNO <> mev.EMPNO

tentei fazer de outra forma mas nao está a funcionar, podes ajudar?

SET SERVEROUTPUT ON

DECLARE

CURSOR C1 IS

SELECT ENAME,SAL

FROM EMP

ORDER BY ENAME;

VMAXSAL EMP.SAL%TYPE;

RESULTADO EMP.SAL%TYPE;

VEMP EMP%ROWTYPE;

BEGIN

OPEN C1;

FETCH C1

INTO VEMP.ENAME,

VEMP.SAL;

CLOSE C1;

SELECT MAX(SAL)

INTO VMAXSAL

FROM EMP;

RESULTADO := (VMAXSAL)-(VEMP.SAL);

DBMS_OUTPUT.PUT_LINE(VEMP.ENAME||-||RESULTADO);

--DBMS_OUTPUT.PUT_LINE(VEMP.ENAME||-||VEMP.SAL);

END;

[rumbafum]

Não era mais fácil usar a query?

esse procedure é para quê? Se é para usar cursor tens que fazer um loop sobre o cursor.

Mas parece-me que o que quererias é uma função que te dá o valor do salário mais alto e usar essa função na query onde queres fazer diferenças. Posso-te dizer que esse método seria bem mais lento que usar o que te mostrei antes com o with (também pode ser uma inner query no where se preferires, o resultado é o mesmo só os planos de execução é que são diferentes dependendo da versão do Oracle)

[maomelov]

Não era mais fácil usar a query?

esse procedure é para quê? Se é para usar cursor tens que fazer um loop sobre o cursor.

Mas parece-me que o que quererias é uma função que te dá o valor do salário mais alto e usar essa função na query onde queres fazer diferenças. Posso-te dizer que esse método seria bem mais lento que usar o que te mostrei antes com o with (também pode ser uma inner query no where se preferires, o resultado é o mesmo só os planos de execução é que são diferentes dependendo da versão do Oracle)

O que eu precisava mesmo era de chegar ao resultado final usando a função first_value, especificamente.

Podes ajudar nesse sentido?

[rumbafum]

se o requisito é usar a função FIRST_VALUE podes simplificar a query que dei para:

select  EMPNO,
	   SAL,
	   FIRST_VALUE(SAL) over (order by SAL DESC ROWS UNBOUNDED PRECEDING) - SAL as SALDIF
from    EMP
order by EMPNO

As colunas do select EMPNO e SAL podem ser retiradas claro, bem como a ordenação pelo EMPNO

[maomelov]

se o requisito é usar a função FIRST_VALUE podes simplificar a query que dei para:

select EMPNO,
	 SAL,
	 FIRST_VALUE(SAL) over (order by SAL DESC ROWS UNBOUNDED PRECEDING) - SAL as SALDIF
from EMP
order by EMPNO

As colunas do select EMPNO e SAL podem ser retiradas claro, bem como a ordenação pelo EMPNO

Era exactamente isso!!! Já fiquei a perceber! Obrigado pela ajuda.

[rumbafum]

Na boa 😉

[jsWizard]

Parece-me que isto chega:

select a.*, (max(a.sal) over ()) - a.sal diff_value
from emp a
order by sal desc;

inté!