Sortir la row qui va bien

Version imprimable

bonjour à toutes et tous,

je me retrouve coincé sur une query que j'arrive pas a résoudre.
Voici le contexte:
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 - create table TMP_PRD_TEST ( param_name VARCHAR2(40) not null, mag VARCHAR2(40) not null, prd VARCHAR2(40) not null, refe VARCHAR2(40) not null, ht FLOAT(38), ttc FLOAT(38) ); alter table TMP_PRD_TEST add constraint TMP_PRD_TEST_PK primary key (PARAM_NAME, MAG, PRD, REFE) using index;
et voici pour les données de la table
Code:

1 2 3 4 5 6 insert into TMP_PRD_TEST (param_name, mag, prd, refe, ht, ttc) values ('TITI', 'DEFAULT', 'DEFAULT', 'DEFAULT', 10, null); insert into TMP_PRD_TEST (param_name, mag, prd, refe, ht, ttc) values ('TITI', 'CARROUF', 'DEFAULT', 'DEFAULT', null, 14); insert into TMP_PRD_TEST (param_name, mag, prd, refe, ht, ttc) values ('TITI', 'DEFAULT', 'DEFAULT', 'ABC12ABC', 12, null);
Code:

1 2 3 4 5 select t.*, t.rowid from TMP_PRD_TEST t where param_name = 'TITI' and (mag like 'CARROUF' or mag = 'DEFAULT') and (prd like 'HARICOTS' OR PRD ='DEFAULT') and (refe like '%14%' or refe = 'DEFAULT')
je voudrais que si le mag match alors il prend le mag sinon il prend la valeur DEFAULT de même pour le produit et la ref.

dans mon test je devrais avoir uniquement la ligne 2

dans le test suivant
Code:

1 2 3 4 5 select t.*, t.rowid from TMP_PRD_TEST t where param_name = 'TITI' and (mag like 'CARROUF' or mag = 'DEFAULT') and (prd like 'HARICOTS' OR PRD ='DEFAULT') and (refe like '%12%' or refe = 'DEFAULT')
j'aimerai avoir que la ligne 3 la colonne la plus a droite ayant une plus forte priorité que ces précédentes.

Voila si quelqu'un a une astuce pour moi elle est la bienvenue....

Hésitez pas si vous avez besoin d'information...

N.

Quelque chose comme ça ?

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
WITH    rf1
    AS  (   SELECT  t.*
                ,   t.ROWID
            FROM    tmp_prd_test t
            WHERE   refe    LIKE '%14%'  
        )
    ,   rf2
    AS  (   SELECT  t.*
                ,   t.ROWID
            FROM    tmp_prd_test t
            WHERE   refe    = 'DEFAUT'
                AND NOT EXISTS
                    (   SELECT  NULL
                        FROM    rf1
                    )
        UNION ALL
            SELECT  *
            FROM    rf1
        )
    ,   pr1
    AS  (   SELECT  *
            FROM    rf2
            WHERE   prd LIKE 'HARICOTS'  
        )
    ,   pr2
    AS  (   SELECT  *
            FROM    rf2
            WHERE   prd = 'DEFAUT'
                AND NOT EXISTS
                    (   SELECT  NULL
                        FROM    pr1
                    )
        UNION ALL
            SELECT  *
            FROM    pr1
        )
    ,   mg1
    AS  (   SELECT  *
            FROM    pr2
            WHERE   mag LIKE 'CARROUF'  
        )
    SELECT  *
    FROM    pr2
    WHERE   mag = 'DEFAUT'
        AND NOT EXISTS
            (   SELECT  NULL
                FROM    mg1
            )
UNION ALL
    SELECT  *
    FROM    mg1
;

12/07/2013, 12h24
MadoshiNatsu

Merci alain,

mais comment je peux tester le with car il me dit "from not found" si je copie colle la query.
12/07/2013, 13h08
al1_24

En effet, il faut qualifier les noms de colonnes dans les premiers SELECT.

J'ai corrigé ma requête.

Salut,

Peut-être comme ça ?

Code:

1
2
3
4
5
6
7
8
9
 
SELECT * 
FROM (
  SELECT t.*, row_number() over(order by case when mag = 'CARROUF' then 1 else 0 end 
                                       + case when prd = 'HARICOTS' then 2 else 0 end 
                                       + case when refe like '%12%' then 4 else 0 end desc) rk                                     
  FROM TMP_PRD_TEST t
)
WHERE rk = 1

12/07/2013, 14h10
al1_24

:ave: Joli :ccool: ... mais ça ne prend pas en compte la valeur 'DEFAUT' ;)
12/07/2013, 14h19
pacmann

Ah ben en fait, j'avais compris que le 'DEFAULT' servait juste à ce que la ligne ne soit pas ignorée dans le cas où elle ne match pas le critère :)

En reprenant la (très) bonne idée de pacmann :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
WITH    sel
    AS  (   SELECT  s.refe
                ,   s.prd
                ,   s.mag
                ,   ROW_NUMBER() OVER(ORDER BY
                            CASE WHEN s.refe LIKE '%12%' THEN 0 WHEN s.refe = 'DEFAUT' THEN 1 ELSE 2 END
                        ,   CASE s.prd WHEN 'HARICOTS' THEN 0 WHEN 'DEFAUT' THEN 1 ELSE 2 END
                        ,   CASE s.mag WHEN 'CARROUF' THEN 0 WHEN 'DEFAUT' THEN 1 ELSE 2 END) rk                                     
            FROM    tmp_prd_test s
        )
SELECT  t.*
FROM    tmp_prd_test t
    INNER JOIN
        sel
        ON  t.refe  = sel.refe
        AND t.prd   = sel.prd
        AND t.mag   = sel.mag 
WHERE   sel.rk = 1
;

12/07/2013, 14h38
pacmann

Attention, il faut que ta distribution de points soit exponentielle si tu veux que le critère le plus à droite soit prioritaire au cumul des autres ;)
(Tu auras reconnu les 2^n dans ma requête :))
12/07/2013, 15h22
MadoshiNatsu

Merci à vous je vais regarder cela de près :) Je vais décortiquer cela.

Histoire de corser la demande, j'ai complété mon jeu de données sur ma première table et ai ajouté une table avec des données.

Code:

1
2
3
4
5
 
INSERT INTO TMP_PRD_TEST (param_name, mag, prd, refe, ht, ttc)
VALUES ('TITI', 'DEFAULT', 'DEFAULT', 'ABC15ABC', NULL, 11);
INSERT INTO TMP_PRD_TEST (param_name, mag, prd, refe, ht, ttc)
VALUES ('TITI', 'CARROUF', 'HARICOT', 'ABC15ABC', NULL, 13);

nouvelle table

Code:

1
2
3
4
5
6
7
8
9
10
11
 
create table TMP_LDR_PRD_TEST
(
  mag  VARCHAR2(40) not null,
  prd  VARCHAR2(40) not null,
  refe VARCHAR2(40) not null
);
-- Create/Recreate primary, unique and foreign key constraints 
alter table TMP_LDR_PRD_TEST
  add constraint TMP_LDR_PRD_TEST_PK primary key (MAG, PRD, REFE)
  using index ;

data de la nouvelle table

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
 
insert into TMP_LDR_PRD_TEST (mag, prd, refe)
values ('AUCHAN', 'HARICOT', 'ABC12ABC');
insert into TMP_LDR_PRD_TEST (mag, prd, refe)
values ('AUCHAN', 'HARICOT', 'CDE12CDE');
insert into TMP_LDR_PRD_TEST (mag, prd, refe)
values ('CARROUF', 'HARICOT', 'ABC12ABC');
insert into TMP_LDR_PRD_TEST (mag, prd, refe)
values ('CARROUF', 'HARICOT', 'CDE12CDE');
insert into TMP_LDR_PRD_TEST (mag, prd, refe)
values ('E.LECLERC', 'HARICOT', 'ABC12ABC');
insert into TMP_LDR_PRD_TEST (mag, prd, refe)
values ('E.LECLERC', 'HARICOT', 'CDE12CDE');
insert into TMP_LDR_PRD_TEST (mag, prd, refe)
values ('HYPERU', 'HARICOT', 'ABC12ABC');
insert into TMP_LDR_PRD_TEST (mag, prd, refe)
values ('HYPERU', 'HARICOT', 'CDE12CDE');
insert into TMP_LDR_PRD_TEST (mag, prd, refe)
values ('LIDL', 'HARICOT', 'ABC12ABC');
insert into TMP_LDR_PRD_TEST (mag, prd, refe)
values ('LIDL', 'HARICOT', 'CDE12CDE');
insert into TMP_LDR_PRD_TEST (mag, prd, refe)
values ('CARROUF', 'CAROTTE', 'CDE12CDE');
insert into TMP_LDR_PRD_TEST (mag, prd, refe)
values ('CARROUF', 'CAROTTE', 'FGH11FGH');

voici le résultat attendu :

MAG PRD REFE PRIX
1 CARROUF HARICOT ABC12ABC 12
2 AUCHAN HARICOT ABC12ABC 12
3 E.LECLERC HARICOT ABC12ABC 12
4 HYPERU HARICOT ABC12ABC 12
5 LIDL HARICOT ABC12ABC 12
6 CARROUF HARICOT CDE12CDE 12
7 AUCHAN HARICOT CDE12CDE 12
8 E.LECLERC HARICOT CDE12CDE 12
9 HYPERU HARICOT CDE12CDE 12
10 LIDL HARICOT CDE12CDE 12
11 CARROUF CAROTTE ABC15ABC 11
12 CARROUF CAROTTE FGH11FGH 14

En gros sortir le prix qui match le plus par rapport a un tableau d'entré. J'essaie de mon coté avec la fonction row_number mais j'ai bien peur qu'il faille passer surement par un dense rank. ou un rank over partition mais je sais pas le tourner :(

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
 
WITH  sel
    AS  (SELECT  lpt.refe
                ,   lpt.prd
                ,   lpt.mag
                ,   rank() OVER(ORDER BY
                            CASE WHEN lpt.refe LIKE psr.refe THEN 0 WHEN psr.refe = 'DEFAUT' THEN 1 ELSE 2 END
                        ,   CASE WHEN lpt.prd LIKE psr.prd THEN 0 WHEN psr.prd = 'DEFAUT' THEN 1 ELSE 2 END
                        ,   CASE WHEN lpt.mag LIKE psr.mag THEN 0 WHEN psr.mag = 'DEFAUT' THEN 1 ELSE 2 END) rk                                     
         FROM    tmp_prd_test psr,TMP_LDR_PRD_TEST lpt
         )
SELECT  t.*
FROM    tmp_prd_test t,sel
where t.refe  (+) = sel.refe
and t.prd (+)  = sel.prd
and t.mag  (+) = sel.mag 
and   sel.rk = 1
;

bon ça gratte dur. Je dois pas être trop loin du compte

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
 
 
select refe, prd, mag, min(rk) from(
SELECT lpt.refe, lpt.prd, lpt.mag,nvl(psr.ht, psr.ttc) as price, dense_rank() OVER(ORDER BY
                            CASE WHEN lpt.refe || lpt.prd || lpt.mag LIKE psr.refe || psr.prd || psr.mag THEN 0 end, 
                            CASE WHEN lpt.refe || lpt.prd || lpt.mag LIKE psr.refe || '%' || psr.mag THEN 1  end,
                            CASE WHEN lpt.refe || lpt.prd || lpt.mag LIKE psr.refe || psr.prd || '%' THEN 2 end,                             
                            CASE WHEN lpt.refe || lpt.prd || lpt.mag LIKE '%' || psr.prd || psr.mag then 3 end,
                            CASE WHEN lpt.refe || lpt.prd || lpt.mag LIKE '%' || psr.prd || '%' then 4 end,
                            CASE WHEN lpt.refe || lpt.prd || lpt.mag LIKE '%' || '%' || psr.mag then 5 end,
                            CASE WHEN lpt.refe || lpt.prd || lpt.mag LIKE '%' || '%' || '%' then 6 end) rk                                     
FROM    tmp_prd_test psr,TMP_LDR_PRD_TEST lpt
)
group by refe, prd, mag

En gros je prend la plus petite config retourné par son rang. Par contre par la suite j'ai du mal a retrouver le prix. Mais je continue dessus.

Code:

1
2
3
4
5
6
7
8
9
10
11
12
 
select * from (
select lpt.mag, lpt.prd, lpt.refe, nvl(pt.ht, pt.ttc) price, 
      decode(pt.refe,'DEFAULT', 0,100)+decode(pt.prd,'DEFAULT',0,10)+decode(pt.mag,'DEFAULT',0,1) matching,
      dense_rank() over(partition by lpt.mag, lpt.prd, lpt.refe order by decode(pt.refe,'DEFAULT', 0,100)+decode(pt.prd,'DEFAULT',0,10)+decode(pt.mag,'DEFAULT',0,1) desc) rk
      from TMP_LDR_PRD_TEST lpt, tmp_prd_test pt
      where (lpt.mag like pt.mag or pt.mag = 'DEFAULT')
      and (lpt.prd like pt.prd or pt.prd = 'DEFAULT')
      and (lpt.refe like pt.refe or pt.refe = 'DEFAULT')
      order by lpt.mag, lpt.prd, lpt.refe,matching desc
      )
     where rk=1

cela me permet de sortir du coup la meilleur row pour un groupe :)

:ccool: