pb sous-interrogation scalaire [Résolu]

[bb5477]

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

[McM]

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)

[orafrance]

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

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

[bb5477]

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

[McM]

Remplace le 0 par null

Et supprime ce DISTINCT (inutile sur un group by)