Selection DISCTINCT avec substring

Version imprimable

13/09/2007, 11h42
KaryBoo

Selection DISCTINCT avec substring

bonjour,

j'ai un problème à créer un sélection de projet dans une table qui peut ressembler à ceci

NoProjet.........Nom Projet...............................autre champs...

105160.80......Eaux-Vives zone 80 proto............xxxxx
105160.70......Eaux-Vives zone 70 vitrage..........xxxxx
105160.40......Eaux-Vives zone 40 escal............xxxxx
103820.10......Diax zone 10 gauche..................xxxxx
103820.60......Diax zone 60 droite....................xxxxx
104950.10......Kingsplace zone 10 verre.............xxxxx
109440.60......Rolex zone 60 escal................... xxxxx
...

Il me faudrait une requête qui me retourne un substring du noProjet pour avoir juste le numéro de chantier (6 premier caractère) et comme désignation le nom du chantier sans la zone et sans répétition (DISTINCT). Je pense qu'il serait impossible de faire une substring pour le nom de chantier car il ne fait pas toujours la même taille et donc certain nom serait coupé. Serait il possible de faire un genre de substring pour le premier mot d'une chaine ??

Ex de retour voulus:

105160 Eaux-Vives
103820 Diax
104950 Kingsplace
...

Cela serait facile si le champ "nom du projet" serait sans indication de la zone, mais ce n'est pas le cas... Je travaille sur Oracle 8i. J'espère que quelqu'un peut m'aider sur ce cas.

Merci d'avance

Karyboo

easy man :yaisse2:

Code:

1
2
3
4
SELECT DISTINCT
       Substr(No_Proj, 1, 6),
       Substr(Nom_Proj, 1, Instr(Nom_Proj, 'zone') - 2)
FROM   table

13/09/2007, 12h07
Loyd1974
D'ailleurs, si tu es sûr que le nom de ton chantier est toujours composé d'un seul mot ou bien regroupé par des '-' (par exemple pas de TRIFOUILLY LES OIES, mais bien des TRIFOUILLY-LES-OIES), alors tu peux rechercher aussi le premier caractère blanc au lieu de rechercher 'zone', ce qui donne :
Code:

1 2 3 4 5 SELECT DISTINCT Substr(No_Proj, 1, 6), Substr(Nom_Proj, 1, Instr(Nom_Proj, ' ') - 1) FROM table
et dans le cas où ton numéro de chantier ne soit pas sur 6 caractères, mais que le '.' soit toujours là, tu peux aussi écrire :
Code:

1 2 3 4 5 SELECT DISTINCT Substr(No_Proj, 1, Instr(No_Proj,'.')-1), Substr(Nom_Proj, 1, Instr(Nom_Proj, ' ') - 1) FROM table
13/09/2007, 12h20
laurentschneider
essaye avec
Code:

1 2 select distinct substr(noprojet,1,6), substr(nomprojet,1,instr(nomprojet,' ')) from t;
[edit]je vois que je suis en retard pour ma réponse...[/edit]

bon, dans le cas où tu cherches la plus longue chaine commune à tous tes projets

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
create or replace function common_string(
  s1 varchar2,s2 varchar2)
return varchar2 is
begin
  for i in 1..4000 loop
    if (substr(s1,i,1)=substr(s2,i,1)) then
      null;
    else
      if (i=1) then
        return null;
      else
        return substr(s1,1,i-1);
      end if;
    end if;
  end loop;
end;
/

Code:

1
2
3
4
5
6
7
8
9
10
11
 
select substr(noprojet,1,6) noprojet,
  common_string(min(nomprojet),max(nomprojet)) nomprojet
from t
group by substr(noprojet,1,6);
NOPROJ NOMPROJET
------ ------------------------------
109440 Rolex zone 60 escal
105160 Eaux-Vives zone
104950 Kingsplace zone 10 verre
103820 Diax zone

Hey Merci les gars pour toutes ces réponses... j'ai bien pu avancer dans ma réflexion...

Pour ce qui est de la solution Substr(KTXT, 1, Instr(KTXT, ' ') - 1) .. ca fonctionne MAIS .. j'ai certain nom de projet qui ont qu'un mot .. et dans ce cas, il est supprimé .. et j'ai un champ vide...

Je pense qu'il faut que je corrige un peu les nom des chantiers, car il est super difficile d'en faire une règle..
Parfois, il n'y a qu'un mot, parfois un chantier peut s'appeler "LES EAUX VIVES Zone Sud" , "IMD Bellerive 34 N/S 1ier Etage" ou encore "BAT 43 E/O", "BAT 43 "N/S"

Le problème est que cette base est utilisé par d'autre application et je ne peux pas commencer à mettre des caractère spéciaux pour séparer le nom du chantier qui est commune à plusieurs zone et le nom de la zone. Verstanden? :?

voici une petite partie de la table pour constater que c'est un peu disparate..
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 ... 102070.70,EAUX VIVES 102070.80,EAUX VIVES compléments 102515.10,Ludgate 102515.20,Ludgate Zone 20 102515.30,Ludgate Zone 30 102515.40,Ludgate Zone 40 102515.60,Ludgate Zone 60 - Rez-de-Chaussée 102515.98,Ludgate - Proto 103000.10,RTFG 103000.20,RTFG 103000.30,Rolex Cuisine 103000.40,Rolex 1 zone 40 103000.98,RTFG PROTO 103140.10,Rolex 3+7 103140.20,Rolex - Remplacement verre 103140.40,Rolex 3+7 Verrières 103140.98,Rolex 3+7 Proto 103160.10,Rue de Lausanne FACADE 103160.20,Rue de Lausanne PAROIS PROV. 103160.30,Rue de Lausanne REV. BUTINI 103160.40,Rue de Lausanne REV. ATTIQUE 103160.50,Rue de Lausanne TRAVAUX COMP. 103160.98,Rue de Lausanne PROTO 103210.10,OMS OnuSida 103210.20,OMS ONUSIDA Tunnel de liaison 103210.30,OMS ONUSIDA Vitr+Portes acier 103605.10,Chiswell street 103620.10,Chesham House 103620.98,Chesham House Mock-up 104015.10,Winterthur 104020.10,LES OUCHES vec 104020.20,LES OUCHES Thermolux 104020.30,LES OUCHES rue couverte 104020.98,LES OUCHES Proto 104040.10,Nagravision 104040.20,Nagravision-grille 104040.30,Nagravision-2èpeau 104040.40,Nagravision modification 2ème peau 104040.98,Nagravision Proto 104055.10,Centre Patronal 104060.10,Ferring 104060.20,Ferring zone 20 104090,Statues Parc Genève 104135.10,Villa Bojano 104180.10,PMI 104180.20,PMI Z.20 ...
Par exemple, pour le 104180, il faudrait écrire 104180....PMI, mais pour le 104055, ça serait Centre Patronale.. Peut-être faudrait-il que je lie tous les mot formant le nom du projet par des "_". A moins que vous, génie, me proposiez une solution encore meilleur :D

Voilà encore un bon défi...

Merci

Citation:
Envoyé par KaryBoo
Hey Merci les gars pour toutes ces réponses... j'ai bien pu avancer dans ma réflexion...

Pour ce qui est de la solution Substr(KTXT, 1, Instr(KTXT, ' ') - 1) .. ca fonctionne MAIS .. j'ai certain nom de projet qui ont qu'un mot .. et dans ce cas, il est supprimé .. et j'ai un champ vide...

Je pense qu'il faut que je corrige un peu les nom des chantiers, car il est super difficile d'en faire une règle..
Parfois, il n'y a qu'un mot, parfois un chantier peut s'appeler "LES EAUX VIVES Zone Sud" , "IMD Bellerive 34 N/S 1ier Etage" ou encore "BAT 43 E/O", "BAT 43 "N/S"

Le problème est que cette base est utilisé par d'autre application et je ne peux pas commencer à mettre des caractère spéciaux pour séparer le nom du chantier qui est commune à plusieurs zone et le nom de la zone. Verstanden? :?

voici une petite partie de la table pour constater que c'est un peu disparate..
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 ... 102070.70,EAUX VIVES 102070.80,EAUX VIVES compléments 102515.10,Ludgate 102515.20,Ludgate Zone 20 102515.30,Ludgate Zone 30 102515.40,Ludgate Zone 40 102515.60,Ludgate Zone 60 - Rez-de-Chaussée 102515.98,Ludgate - Proto 103000.10,RTFG 103000.20,RTFG 103000.30,Rolex Cuisine 103000.40,Rolex 1 zone 40 103000.98,RTFG PROTO 103140.10,Rolex 3+7 103140.20,Rolex - Remplacement verre 103140.40,Rolex 3+7 Verrières 103140.98,Rolex 3+7 Proto 103160.10,Rue de Lausanne FACADE 103160.20,Rue de Lausanne PAROIS PROV. 103160.30,Rue de Lausanne REV. BUTINI 103160.40,Rue de Lausanne REV. ATTIQUE 103160.50,Rue de Lausanne TRAVAUX COMP. 103160.98,Rue de Lausanne PROTO 103210.10,OMS OnuSida 103210.20,OMS ONUSIDA Tunnel de liaison 103210.30,OMS ONUSIDA Vitr+Portes acier 103605.10,Chiswell street 103620.10,Chesham House 103620.98,Chesham House Mock-up 104015.10,Winterthur 104020.10,LES OUCHES vec 104020.20,LES OUCHES Thermolux 104020.30,LES OUCHES rue couverte 104020.98,LES OUCHES Proto 104040.10,Nagravision 104040.20,Nagravision-grille 104040.30,Nagravision-2èpeau 104040.40,Nagravision modification 2ème peau 104040.98,Nagravision Proto 104055.10,Centre Patronal 104060.10,Ferring 104060.20,Ferring zone 20 104090,Statues Parc Genève 104135.10,Villa Bojano 104180.10,PMI 104180.20,PMI Z.20 ...
Par exemple, pour le 104180, il faudrait écrire 104180....PMI, mais pour le 104055, ça serait Centre Patronale.. Peut-être faudrait-il que je lie tous les mot formant le nom du projet par des "_". A moins que vous, génie, me proposiez une solution encore meilleur :D

Voilà encore un bon défi...

Merci
Utilise la fonction proposé par Laurent Schneider, au vu de ton jeu de données, elle fera 90% du travail, ensuite, cela risque de passer par des ajustements en dur

Une solution est, si tes noms de projets évolue peu, de passer par une table ou tu stockeras définitivement le bon libellé de projet en fonction de ton code sur 6 caractères basé sur le numéro de projet, mais il faut pas oublier de la mettre à jour

13/09/2007, 16h15
KaryBoo

merci

J'ai modifié les enregistrements de la base afin d'ajouter un "|" entre le nom du projet et la zone ensuite j'ai utilisé la fonction "TRIM(Substr(KTXT, 1, Instr(KTXT, '|') - 1))" .. c'est plus clair et ça m'a permis de nettoyer la table..

merci beaucoup !:yaisse2: