pb sous-interrogation scalaire

bonjour,
je souhaite faire une requête qui, dans la clause select, utilise comme colonne des sous-interrogation scalaire. Je retrouve les bonnes informations, seulement celles-ci sont dupliquées.
Il s'agit, à partir de ces 2 tables, afficher les ventes effectuées pour chaque client et pour chacun des 4 trimestres de l'année.

mes tables:
sales_order(ORDER_ID ,ORDER_DATE,CUSTOMER_ID,TOTAL)
customer(CUSTOMER_ID,CREDIT_LIMIT)

ma requête:

Code:

---

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

select distinct c.name as "name", so1.total as "1st", so2.total as "2nd", so3.total as "3rd", so4.total as "4th" from customer c, sales_order s,sales_order so1,sales_order so2,sales_order so3,sales_order so4 where c.customer_id=s.customer_id and so1.total in (select so1.total from sales_order so1 where so1.customer_id=c.customer_id and to_char(so1.order_date, 'Q')=1) and so2.total in (select so2.total from sales_order so2 where so2.customer_id=c.customer_id and to_char(so2.order_date, 'Q')=2) and so3.total in (select so3.total from sales_order so3 where so3.customer_id=c.customer_id and to_char(so3.order_date, 'Q')=3) and so4.total in (select so4.total from sales_order so4 where so4.customer_id=c.customer_id and to_char(so4.order_date, 'Q')=4) order by c.name;

---

merci pour votre aide

Il y a plus simple, en utilisant DECODE

Code:

---

1 2 3 4 5 6 7 8

SELECT c.NAME AS "name", SUM( DECODE( TO_CHAR(s.order_date, 'Q'), '1', s.total, 0)) AS "1st", SUM( DECODE( TO_CHAR(s.order_date, 'Q'), '2', s.total, 0)) AS "2nd", SUM( DECODE( TO_CHAR(s.order_date, 'Q'), '3', s.total, 0)) AS "3rd", SUM( DECODE( TO_CHAR(s.order_date, 'Q'), '4', s.total, 0)) AS "4th" FROM CUSTOMER c, SALES_ORDER s WHERE c.customer_id=s.customer_id GROUP BY c.NAME

---

J'ai fait une somme, mais ce n'est pas dit que c'est ce que tu veuilles.

Attention au DISTINCT : Si tu as 2 lignes identiques mais que c'est normal, la seconde n'est pas prise en compte.
=> DISTINCT : A n'utiliser que dans des cas particuliers.

EDIT : On peut rajouter le group par customer_id (s'ils 2 customer_id ont le même nom)

Code:

---

so1.total IN (SELECT so1.total FROM sales_order so1 WHERE so1.customer_id=c.customer_id AND to_char(so1.order_date, 'Q')=1)

---

c'est pas équivalent à :

Code:

---

so1.customer_id=c.customer_id AND to_char(so1.order_date, 'Q')=1

---

j'ai l'impression que les sous-requêtes sont inutiles :koi:

Ca donne quoi ça ?

Code:

---

1 2 3 4 5 6 7 8 9

SELECT DISTINCT c.name AS "name", DECODE(to_char(so1.order_date, 'Q'),1,so.total) AS "1st", DECODE(to_char(so1.order_date, 'Q'),2,so.total) AS "2nd", DECODE(to_char(so1.order_date, 'Q'),3,so.total) AS "3rd", DECODE(to_char(so1.order_date, 'Q'),4,s o.total) AS "4th" FROM customer c, sales_order so WHERE c.customer_id=so.customer_id ORDER BY c.name;

---

Sinon, regarde du coté de WITH :

Code:

---

1 2 3 4

WITH my_so AS SELECT to_char(order_date, 'Q'), total FROM customer c, sales_order so ... etc

---

sur le même principe mais pour calculer le montant moyen des ventes par client et par trimestre:
ma requête:

Code:

---

1 2 3 4 5 6 7 8

select distinct c.name as "name", (select To_char(AVG(s.total),'L999G999D99') from sales_order s where s.customer_id=c.customer_id and to_char(s.order_date, 'Q')=1) as "1st", (select To_char(AVG(s.total),'L999G999D99') from sales_order s where s.customer_id=c.customer_id and to_char(s.order_date, 'Q')=2) as "2nd", (select To_char(AVG(s.total),'L999G999D99') from sales_order s where s.customer_id=c.customer_id and to_char(s.order_date, 'Q')=3) as "3rd", (select To_char(AVG(s.total),'L999G999D99') from sales_order s where s.customer_id=c.customer_id and to_char(s.order_date, 'Q')=4) as "4th" from customer c, sales_order s where c.customer_id=s.customer_id;

---

mais en utilisant decode, je ne retombe pas sur le même résultat !!

Code:

---

1 2 3 4 5 6 7 8 9 10

SELECT Distinct c.NAME AS "name", AVG( DECODE( TO_CHAR(s.order_date, 'Q'), '1', s.total, 0)) AS "1st", AVG( DECODE( TO_CHAR(s.order_date, 'Q'), '2', s.total, 0)) AS "2nd", AVG( DECODE( TO_CHAR(s.order_date, 'Q'), '3', s.total, 0)) AS "3rd", AVG( DECODE( TO_CHAR(s.order_date, 'Q'), '4', s.total, 0)) AS "4th" FROM CUSTOMER c, SALES_ORDER s WHERE c.customer_id=s.customer_id GROUP BY c.NAME ORDER BY c.NAME;

---

je ne vois pas très bien comment fonctionne decode() ici :cry:

Remplace le 0 par null

Et supprime ce DISTINCT (inutile sur un group by)