Bonjour,
Je débute sur SQL Oracle et je travaille sur un schema que je ne connais pas beaucoup. Je dois extraire des enregistrements pour un emailing, voici ma requête :
Code : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
 
SELECT DISTINCT (NAME.title || ' ' || reservation_name.sguest_firstname || ' '|| reservation_name.sguest_name) AS client_nom,
	DECODE (LANGUAGE, 'F', 'FRANCE','E', 'ANGLETERRE') AS client_pays,
	   (CASE
		   WHEN opera.reservation_name.resort = 'OPERA'
			  THEN 'Best Western Premier Opera Diamond'
		   WHEN opera.reservation_name.resort = 'BATIGN'
			  THEN 'Opera Batignolles Hotel'
		   WHEN opera.reservation_name.resort = 'PPN'
			  THEN 'Comfort et Quality Hotel Centre del Mon'
		END
   ) AS client_hotel,
   TO_CHAR (trunc_actual_check_in_date,'DD/MM/YYYY') AS hotel_date_entree,
   TO_CHAR (trunc_actual_check_out_date,'DD/MM/YYYY') AS hotel_date_sortie,
   LOWER (phone_number) AS client_email,
   RESORT_ORIGINS_OF_BOOKING.DESCRIPTION AS origine_reservation,
   TRUNC (SYSDATE - TO_DATE (trunc_actual_check_out_date)) AS nb_jours
FROM opera.reservation_name 
 
JOIN OPERA.NAME ON RESERVATION_NAME.NAME_ID = NAME.NAME_ID 
JOIN OPERA.NAME_PHONE ON RESERVATION_NAME.NAME_ID = NAME_PHONE.NAME_ID 
JOIN OPERA.RESERVATION_SUMMARY ON RESERVATION_NAME.RESV_NAME_ID = RESERVATION_SUMMARY.EVENT_ID 
JOIN OPERA.RESORT_ORIGINS_OF_BOOKING ON (RESERVATION_SUMMARY.RESORT = RESORT_ORIGINS_OF_BOOKING.RESORT AND RESERVATION_SUMMARY.SOURCE_CODE = RESORT_ORIGINS_OF_BOOKING.SOURCE_CODE) 
 
WHERE resv_status = 'CHECKED OUT'
AND TRUNC( SYSDATE - TO_DATE(TRUNC_ACTUAL_CHECK_OUT_DATE)) > 0 
AND REGEXP_LIKE (LOWER(phone_number), '^[a-zA-Z0-9\-_]+[a-zA-Z0-9\.\-_]*@[a-zA-Z0-9\-_]+\.[a-zA-Z\.\-_]{1,}[a-zA-Z\-_]+')
AND TRUNC_ACTUAL_CHECK_OUT_DATE >= to_date('01/09/2010', 'dd/mm/yyyy')
ORDER BY NB_JOURS DESC;
J'obtiens environs 400 lignes, mais quand je fais cette requête :
Code : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
 
SELECT DISTINCT (NAME.title || ' ' || reservation_name.sguest_firstname || ' '|| reservation_name.sguest_name) AS client_nom,
	DECODE (LANGUAGE, 'F', 'FRANCE','E', 'ANGLETERRE') AS client_pays,
	   (CASE
		   WHEN opera.reservation_name.resort = 'OPERA'
			  THEN 'Best Western Premier Opera Diamond'
		   WHEN opera.reservation_name.resort = 'BATIGN'
			  THEN 'Opera Batignolles Hotel'
		   WHEN opera.reservation_name.resort = 'PPN'
			  THEN 'Comfort et Quality Hotel Centre del Mon'
		END
   ) AS client_hotel,
   TO_CHAR (trunc_actual_check_in_date,'DD/MM/YYYY') AS hotel_date_entree,
   TO_CHAR (trunc_actual_check_out_date,'DD/MM/YYYY') AS hotel_date_sortie,
   LOWER (phone_number) AS client_email,
   --RESORT_ORIGINS_OF_BOOKING.DESCRIPTION AS origine_reservation,
   TRUNC (SYSDATE - TO_DATE (trunc_actual_check_out_date)) AS nb_jours
FROM opera.reservation_name 
 
JOIN OPERA.NAME ON RESERVATION_NAME.NAME_ID = NAME.NAME_ID 
JOIN OPERA.NAME_PHONE ON RESERVATION_NAME.NAME_ID = NAME_PHONE.NAME_ID 
--JOIN OPERA.RESERVATION_SUMMARY ON RESERVATION_NAME.RESV_NAME_ID = RESERVATION_SUMMARY.EVENT_ID 
--JOIN OPERA.RESORT_ORIGINS_OF_BOOKING ON (RESERVATION_SUMMARY.RESORT = RESORT_ORIGINS_OF_BOOKING.RESORT AND RESERVATION_SUMMARY.SOURCE_CODE = RESORT_ORIGINS_OF_BOOKING.SOURCE_CODE) 
 
WHERE resv_status = 'CHECKED OUT'
AND TRUNC( SYSDATE - TO_DATE(TRUNC_ACTUAL_CHECK_OUT_DATE)) > 0 
AND REGEXP_LIKE (LOWER(phone_number), '^[a-zA-Z0-9\-_]+[a-zA-Z0-9\.\-_]*@[a-zA-Z0-9\-_]+\.[a-zA-Z\.\-_]{1,}[a-zA-Z\-_]+')
AND TRUNC_ACTUAL_CHECK_OUT_DATE >= to_date('01/09/2010', 'dd/mm/yyyy')
ORDER BY NB_JOURS DESC;
j'en obtiens plus de 4000. Le problème est que j'ai besoin des infos mis en commentaires, j'ai besoin d'une aide pour utiliser la données en commentaires donc d'utiliser les deux jointures, sans que cela réduise mon nombre de réponse (comme avec un left join ou right join) mais j'ai du mal à les utiliser.

D'avance merci pour votre aide.