Impossible d'utiliser un alias dans une opération arithmétique avec une fonction analytique?

Bonjour,

Sur une base de tests je fais le SELECT suivant :

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

SQL> select employee_id, salary, department_id, sum(salary) over (partition by department_id) TOTAL from employees order by 3 EMPLOYEE_ID SALARY DEPARTMENT_ID TOTAL ----------- ---------- ------------- ---------- 200 35200 10 35200 201 104000 20 152000 202 48000 20 152000 114 88000 30 199200 115 24800 30 199200 116 23200 30 199200 117 22400 30 199200 118 20800 30 199200 119 20000 30 199200 203 52000 40 52000 120 64000 50 2125600 ...

---

Ce que je voudrais maintenant c'est obtenir le pourcentage du salaire de chaque employé par rapport à son département.
Je fais le SELECT suivant avec un alias mais ça plante.

Code:

---

1 2 3

select employee_id, salary, department_id, sum(salary) over (partition by department_id) TOTAL, (salary/TOTAL)*100 from employees order by 3; ERROR at line 1: ORA-00904: "TOTAL": invalid identifier

---

La requête marche avec duplication du calcul de la somme mais c'est moche comme code et je pense que le calcul du total par département est alors effectué deux fois.

Code:

---

select employee_id, salary, department_id, sum(salary) over (partition by department_id) TOTAL, (salary/sum(salary) over (partition by department_id))*100 from employees order by 3;

---

Si vous pouviez m'expliquer le comment du pourquoi de ce problème... par avance merci.

Cette facon d'écrire en utilisant directement l'alias TOTAL au sein de la même requête n'est pas forcément disponible avec tous les SGBD.
En Teradata, tu n'aurais pas eu ce problème.

Tu dois pouvoir voir dans le plan si ca te calcule deux fois ton résultat je pense.
Si c'est le cas, tu peux essayer l'écriture suivante :

Code:

---

1 2 3

select employee_id, salary, department_id, TOTAL, (salary/TOTAL)*100 as prct from ( select employee_id, salary, department_id, sum(salary) over (partition by department_id) TOTAL from employees) order by 3;

---

EDIT: Autant eviter de laisser passer des erreurs.

Citation:

---

Envoyé par bstevy

Cette facon d'écrire en utilisant directement l'alias TOTAL au sein de la même requête est correcte d'un point de vue SQL, mais pas forcément disponible avec tous les SGBD.

---

Non elle est totalement incorrecte en SQL. Les alias de colonnes ne peuvent en aucun cas être utilisé dans le traitement relationnelle d'une requête (ce qui exclu de facto la clause ORDER BY qui n'est pas une clause relationnelle) sauf dans le cas de données résultantes d'une requêtes externe appliquée à une sous requête en table dérivée ou en CTE et bien entendu à une requête appliquée à une vue ou une UDF de type table.

Effectivement certains SGBD le permette.....

A +

@Ikebukuro

Vous pouvez faire ainsi :

Code:

---

1 2 3 4 5

select employee_id, salary, department_id , sum(salary) over (partition by department_id) as total , ratio_to_report(salary) over (partition by department_id) * 100 as pct from employees order by department_id

---

Merci à vous tous pour vos réponses; visiblement mon problème n'était pas si simple que ça...

Je ne connaissais pas la fonction ratio_to_report, maintenant c'est fait :-)