[MAX] - entre 2 tables

EFFLYINGJOKER · 23/09/2011, 16h13

Bonjour @ tous,

Contexte :
2 tables :
invoice_tab (nommé a)
et
approval_routing_tab (nommé b)
je peux avoir 1 enregistrement dans A et x dans B

Problème :
il me faut les lignes rouges uniquement ... soit
il me faut l'enregistrement max du champ "current_step_no" de la table B

Exemple:

Invoice ||| key|| Step || Current step
202660 COMPANY=622^INVOICE_ID=202660^ 10 10
202661 COMPANY=622^INVOICE_ID=202661^ 10 10
202797 COMPANY=622^INVOICE_ID=202797^ 30 30
202798 COMPANY=622^INVOICE_ID=202798^ 30 10
202798 COMPANY=622^INVOICE_ID=202798^ 10 10
202798 COMPANY=622^INVOICE_ID=202798^ 20 10
202799 COMPANY=622^INVOICE_ID=202799^ 30 20
202799 COMPANY=622^INVOICE_ID=202799^ 20 20

Ma requête actuelle :

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
SELECT 
       a.invoice_id,
       b.line_no, 
       b.key_ref, 
       b.step_no,
       b.app_date, 
       b.current_step_no, 
       b.approval_status
FROM
    invoice_tab a, approval_routing_tab b
WHERE 
      'COMPANY='|| a.company ||'^INVOICE_ID='|| a.invoice_id ||'^' =b.key_ref AND 
      b.approval_status IS NULL
ORDER BY
      a.invoice_id

Dajon · 23/09/2011, 20h42

Bonsoir,

peut-être en regardant du côté du

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

max(...) over (partition BY ....)

dans votre cas cela pourrait donner quelque chose comme:

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
 
SELECT * 
FROM 
(
	SELECT 
		   a.invoice_id,
		   b.line_no, 
		   b.key_ref, 
		   b.step_no,
		   b.app_date, 
		   b.current_step_no, 
		   b.approval_status
		   max(b.current_step_no) over (partition BY a.invoice_id) AS max_step_no
	FROM
		invoice_tab a, approval_routing_tab b
	WHERE 
		  'COMPANY='|| a.company ||'^INVOICE_ID='|| a.invoice_id ||'^' =b.key_ref AND 
		  b.approval_status IS NULL
	ORDER BY
		  a.invoice_id
)
WHERE current_step_no = max_step_no

EFFLYINGJOKER · 23/09/2011, 22h47

Euhhhh j'ai recopié la requete ... et ca donne le meme résultat !!!

En fait je me suis peut etre mal exprimé, mais dans les 8 enregistrements que j'ai , je dois n'en avoir que 5.

McM · 25/09/2011, 21h33

J'ai pas de base pour être sur du code.

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
SELECT invoice_id, line_no, key_ref, step_no, app_date, current_step_no, approval_status
FROM (
	SELECT a.invoice_id, 
				row_number() over (partition BY a.invoice_id ORDER BY b.current_step_no DESC) num,
				max(b.line_no) over (partition BY a.invoice_id ORDER BY b.current_step_no DESC) line_no,
				max(b.key_ref) over (partition BY a.invoice_id ORDER BY b.current_step_no DESC) key_ref,
				max(b.step_no) over (partition BY a.invoice_id ORDER BY b.current_step_no DESC) step_no,
				max(b.app_date) over (partition BY a.invoice_id ORDER BY b.current_step_no DESC) app_date,
				max(b.current_step_no) over (partition BY a.invoice_id ORDER BY b.current_step_no DESC) current_step_no,
				max(b.approval_status) over (partition BY a.invoice_id ORDER BY b.current_step_no DESC) approval_status
	FROM invoice_tab a, approval_routing_tab b
	WHERE 'COMPANY='|| a.company ||'^INVOICE_ID='|| a.invoice_id ||'^' = b.key_ref 
	AND  b.approval_status IS NULL
	)
WHERE num = 1
ORDER BY 1

Dajon · 26/09/2011, 10h41

Bonjour,

Désolé pour le temps de réponse, je n'avais pas de base sous la main ce week-end pour regarder.

Alors vous indiquiez vouloir le max de current_step_no, mais j'ai l'impression que c'est plus le max de step_no, avec ces données.

En effet, ma requête contient une petite erreur. Avec les données dont je dispose, voila ce que cela donne:

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
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
CREATE TABLE ada_test_1
(
  invoice_id number,
  company number
);
 
CREATE TABLE ada_test_2
(
  key_ref varchar2(255),
  line_no number,
  step_no number,
  current_step_no number,
  app_date date, 
  approval_status varchar2(1)
);
 
INSERT INTO ADA_TEST_1 (INVOICE_ID,COMPANY) VALUES (202660,622);
INSERT INTO ADA_TEST_1 (INVOICE_ID,COMPANY) VALUES (202661,622);
INSERT INTO ADA_TEST_1 (INVOICE_ID,COMPANY) VALUES (202797,622);
INSERT INTO ADA_TEST_1 (INVOICE_ID,COMPANY) VALUES (202798,622);
INSERT INTO ADA_TEST_1 (INVOICE_ID,COMPANY) VALUES (202799,622);
 
REM INSERTING INTO ADA_TEST_2
INSERT INTO ADA_TEST_2 (KEY_REF,LINE_NO,STEP_NO,CURRENT_STEP_NO,APP_DATE,APPROVAL_STATUS) VALUES ('COMPANY=622^INVOICE_ID=202660^',1,10,10,to_date('26/09/2011 10:15:48','DD/MM/YYYY hh24:mi:ss'),NULL);
INSERT INTO ADA_TEST_2 (KEY_REF,LINE_NO,STEP_NO,CURRENT_STEP_NO,APP_DATE,APPROVAL_STATUS) VALUES ('COMPANY=622^INVOICE_ID=202661^',1,10,10,to_date('26/09/2011 10:15:55','DD/MM/YYYY hh24:mi:ss'),NULL);
INSERT INTO ADA_TEST_2 (KEY_REF,LINE_NO,STEP_NO,CURRENT_STEP_NO,APP_DATE,APPROVAL_STATUS) VALUES ('COMPANY=622^INVOICE_ID=202797^',1,30,30,to_date('26/09/2011 10:15:57','DD/MM/YYYY hh24:mi:ss'),NULL);
INSERT INTO ADA_TEST_2 (KEY_REF,LINE_NO,STEP_NO,CURRENT_STEP_NO,APP_DATE,APPROVAL_STATUS) VALUES ('COMPANY=622^INVOICE_ID=202798^',1,30,10,to_date('26/09/2011 10:15:59','DD/MM/YYYY hh24:mi:ss'),NULL);
INSERT INTO ADA_TEST_2 (KEY_REF,LINE_NO,STEP_NO,CURRENT_STEP_NO,APP_DATE,APPROVAL_STATUS) VALUES ('COMPANY=622^INVOICE_ID=202798^',2,10,10,to_date('26/09/2011 10:16:00','DD/MM/YYYY hh24:mi:ss'),NULL);
INSERT INTO ADA_TEST_2 (KEY_REF,LINE_NO,STEP_NO,CURRENT_STEP_NO,APP_DATE,APPROVAL_STATUS) VALUES ('COMPANY=622^INVOICE_ID=202798^',3,20,10,to_date('26/09/2011 10:16:02','DD/MM/YYYY hh24:mi:ss'),NULL);
INSERT INTO ADA_TEST_2 (KEY_REF,LINE_NO,STEP_NO,CURRENT_STEP_NO,APP_DATE,APPROVAL_STATUS) VALUES ('COMPANY=622^INVOICE_ID=202799^',1,30,20,to_date('26/09/2011 10:16:03','DD/MM/YYYY hh24:mi:ss'),NULL);
INSERT INTO ADA_TEST_2 (KEY_REF,LINE_NO,STEP_NO,CURRENT_STEP_NO,APP_DATE,APPROVAL_STATUS) VALUES ('COMPANY=622^INVOICE_ID=202799^',2,20,20,to_date('26/09/2011 10:16:06','DD/MM/YYYY hh24:mi:ss'),NULL);
 
commit;
 
 
SELECT * 
FROM 
(
	SELECT 
		   a.invoice_id,
		   b.line_no, 
		   b.key_ref, 
		   b.step_no,
		   b.app_date, 
		   b.current_step_no, 
		   b.approval_status,
		   max(b.step_no) over (partition BY a.invoice_id) AS max_step_no
	FROM
		ada_test_1 a, ada_test_2 b
	WHERE 
		  'COMPANY='|| a.company ||'^INVOICE_ID='|| a.invoice_id ||'^' =b.key_ref AND 
		  b.approval_status IS NULL
	ORDER BY
		  a.invoice_id
)
WHERE step_no = max_step_no;
 
INVOICE_ID    LINE_NO KEY_REF                                               STEP_NO APP_DATE CURRENT_STEP_NO A MAX_STEP_NO
---------- ---------- -------------------------------------------------- ---------- -------- --------------- - -----------
    202660          1 COMPANY=622^INVOICE_ID=202660^                             10 26/09/11              10            10
    202661          1 COMPANY=622^INVOICE_ID=202661^                             10 26/09/11              10            10
    202797          1 COMPANY=622^INVOICE_ID=202797^                             30 26/09/11              30            30
    202798          1 COMPANY=622^INVOICE_ID=202798^                             30 26/09/11              10            30
    202799          1 COMPANY=622^INVOICE_ID=202799^                             30 26/09/11              20            30

NB: J'ai créé mes tables avec d'autres noms.

[edit: cette fois-ci c'est la bonne]

Waldar · 26/09/2011, 15h22

Il y a plusieurs de solutions à ce problème.
J'aime bien celle de l'agrégat FIRST - que j'ai apprise sur ce forum - qui permet d'éviter les sous-requêtes, mais il est probablement moins évident à comprendre.

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
  SELECT a.invoice_id
       , max(b.line_no)         keep (dense_rank first ORDER BY step_no DESC) AS line_no
       , max(b.key_ref)         keep (dense_rank first ORDER BY step_no DESC) AS key_ref
       , max(b.step_no)                                                       AS step_no
       , max(b.app_date)        keep (dense_rank first ORDER BY step_no DESC) AS app_date
       , max(b.current_step_no) keep (dense_rank first ORDER BY step_no DESC) AS current_step_no
       , max(b.approval_status) keep (dense_rank first ORDER BY step_no DESC) AS approval_status
    FROM ada_test_1 a
         INNER JOIN ada_test_2 b
           ON b.key_ref = 'COMPANY=' || a.company || '^INVOICE_ID=' || a.invoice_id || '^'
   WHERE b.approval_status IS NULL
GROUP BY a.invoice_id
ORDER BY a.invoice_id ASC;
 
INVOICE_ID    LINE_NO KEY_REF                           STEP_NO APP_DATE   CURRENT_STEP_NO
---------- ---------- ------------------------------ ---------- ---------- ---------------
    202660          1 COMPANY=622^INVOICE_ID=202660^         10 26/09/2011              10
    202661          1 COMPANY=622^INVOICE_ID=202661^         10 26/09/2011              10
    202797          1 COMPANY=622^INVOICE_ID=202797^         30 26/09/2011              30
    202798          1 COMPANY=622^INVOICE_ID=202798^         30 26/09/2011              10
    202799          1 COMPANY=622^INVOICE_ID=202799^         30 26/09/2011              20

Dajon · 26/09/2011, 16h11

Merci pour l'info Waldar, je n'avais jamais pensé à le faire comme ça

26/09/2011, 16h11	#7
Dajon Membre du Club Arnaud Inscription : octobre 2002 Messages : 56 Détails du profil Informations personnelles : Nom : Arnaud Âge : 32 Localisation : France, Paris (Île de France) Informations forums : Inscription : octobre 2002 Messages : 56 Points : 66 Points : 66	Merci pour l'info Waldar, je n'avais jamais pensé à le faire comme ça __________________ Tuning and optimization are not a fix to a bad design. A good design is a fix to a bad design.
	00

23/09/2011, 22h47	#3
EFFLYINGJOKER Candidat au titre de Membre du Club Eric Inscription : juin 2007 Messages : 60 Détails du profil Informations personnelles : Nom : Eric Localisation : France, Seine et Marne (Île de France) Informations forums : Inscription : juin 2007 Messages : 60 Points : 12 Points : 12	Euhhhh j'ai recopié la requete ... et ca donne le meme résultat !!! En fait je me suis peut etre mal exprimé, mais dans les 8 enregistrements que j'ai , je dois n'en avoir que 5.
	00

Outils de la discussion
Afficher une version imprimable Envoyer un lien vers cette page par email