[MAX] - entre 2 tables

Version imprimable

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:

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

Bonsoir,

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

Code:

max(...) over (partition by ....)

dans votre cas cela pourrait donner quelque chose comme:

Code:

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

23/09/2011, 22h47
EFFLYINGJOKER

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.

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

Code:

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

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:

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]

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:

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

26/09/2011, 16h11
Dajon

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