Discussion :

[Passarinho44]

Bonjour à tous!

Je vous explique un peu mon contexte :

J'ai un ID(qui n'est pas ma clé primaire) et un Status(qui est égal à CL ou CNCL)
Pour une même clé, je peux avoir plusieurs status (plusieurs fois CL ou CNCL ou les 2 )

Je voudrais, gràce à Case When affiché un troisième champs : Is_Provider qui serait égal à 0 ou 1.

Il serait égal à 1 si Il y a au moins une fois CL dans les status pour un même ID.
En clair si j'ai :
ID | Status
21 | CL
21 | CNCL
21 | CL

Je voudrais affiché :
Is_Provider | ID
1 | 21

Et si j'ai
ID | Status
22 | CNCL

Je voudrais affiché :
Is_Provider | ID
0 | 22

Et si j'ai
ID | Status
23 | CL

Je voudrais affiché :
Is_Provider | ID
1 | 23

Pour cela je pensais utiliser une condition du genre :

Code :

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

1
2
3
4
select distinct (case when 
	/* ici la condition */
then 1 else 0 end) AS "Is_Provider",
ID

Quelqu'un a une idée de la condition?

Merci d'avance =)

[pacmann]

Salut !

Si j'ai bien compris, on peut donner la règle :
- s'il existe un "CL", alors provider
- s'il existe un CNCL, mais pas de CL, alors non provider.

C'est ça ?

Dans ce cas, on va supposer que tes Id Existent dans une autre table de référence, et on va choper ton résultat en deux fois :

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
 
SELECT id, 1
FROM TaTableDeReference a
WHERE EXISTS (SELECT 1
FROM TaTable b
WHERE a.id = b.id
  AND b.status = 'CL'
UNION ALL
SELECT id, 0
FROM TaTableDeReference a
WHERE NOT EXISTS (SELECT 1
FROM TaTable b
WHERE a.id = b.id
  AND b.status = 'CL')
  AND EXITS (SELECT 1
FROM TaTable b
WHERE a.id = b.id
  AND b.status = 'CNCL')

[Passarinho44]

Bonjour,

Je n'ai pas bien compris ton histoire de table de référence ...

En fait moi il faut que j'intègre ça dans une requête de création d'une table à partir de plein de tables différentes. (mais la création se fait par un select des autres tables)

Il faut qu'il n'y ai qu'une ligne par ID.

Il faut que, selon le status, je mette 1 ou 0 dans le champ Is_Provider.

Je n'ai pas compris ta requête

Il n'y a pas moyen de faire ça avec une requête de la forme case when ?
Il n'y a pas quelque chose qui permet de comparer le champs avec le champs suivant?

Merci de ton aide =)

[pacmann]

En fait, ton case devrait analyser le résutlats de plusieurs lignes... ce qui n'est pas possible.
Si je te parle d'une table de référence, c'est parce que les fameux id doivent venir de quelque part. Un endroit où cet Id est la clef primaire.

Par exemple, si ta table est s'appelle lespaysvisitespardesgens, l'id ferait référence à des gens. Et du coup, on pourrait espérer qu'il existe une table "gens".

Ensuite, la requête est assez simple :
Pour chaque Id, tu regardes dans ta table si tu trouves une ligne CL : si tu trouves, tu choisis 1.
Dans la deuxième partie de l'UNION ALL, tu cherches pour chaque Id si tu trouves un CNCL et que tu ne trouves aucun CL : tu affectes alors 0

Tu es sous quel SGBD ?

[Yanika_bzh]

Ce genre de requete peut vous aider

Code :

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

1
2
3
 
select id, case (sum (case status when 'CL' then 1 else 0 end)) when 0 then 0 else 1 end from matable
group by id

A tester et adapter ...

Bon courage

[pacmann]

[EDIT]
J'ai dit une connerie, je retire

[Passarinho44]

Pacmann en fait je ne pense pas que tu aies dit une connerie et je pense la même chose ^^

Yanika de Bretagne, ce que tu m'a donné en est la preuve :

Je l'ai testé et j'ai toujours le même soucis : il me met une ligne pour le status CL et une autre pour le statut CNCL.
Par contre il me met bien 1 pour la ligne avec CL et 0 pour la ligne avec le statut CNCL ^^ (mais ça je l'avais déjà )
Or la table ne doit contenir qu'une ligne par id puisqu'elle sert ensuite à des calculs de moyenne...

J'utilise Microsoft SQL Server 2005.

Merci pour l'explication Pacmann, ta requête est pas mal, j'aurais été incapable de penser à ça tout seul ^^"

Mais malheuresement je ne peux pas l'adapter à mon cas ...

[Yanika_bzh]

Pourrai je avoir votre jeu d'essai ?

Je viens de tester avec les quelques lignes sur mon serveur ASE et cela fonctionne. J'ai du mal comprendre votre demande.
Voila mon cas de test :

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
 
create table #matable (id int, status char(4))
insert into #matable values (21,'CL')
insert into #matable values (21,'CNCL')
insert into #matable values (21,'CL')
insert into #matable values (22,'CNCL')
 
select id, case (sum (case status when 'CL' then 1 else 0 end)) when 0 then 0 else 1 end from #matable
group by id
 
Resultats :
id    
--------
21	1
22	0

Bon courage

[Passarinho44]

Bon ben je sais pas ce que j'ai fait la première fois mais là, ça fonctionne ! =)

Je ne suis pas sûr que mon boss va l'accepter à cause du group by qui peut peut-être gêner le reste ...
Mais en tout cas merci beaucoup parce que ça fait un moment que je m'arrache les cheveux là-dessus...

Je ne pense pas que ça soit possible mais si vous trouvez autre chose qui n'oblige pas à faire un group by (parce que mon select récupère 26champs donc c'est un peu lourd de tout mettre dans le group by...

En tout cas merci beaucoup et @bientot =)

[Yanika_bzh]

Vous pouvez simplement faire une jointure avec votre requete et celle que je vous ai donnée, ca devrait simplifier la chose

Bon courage

[pacmann]

Euh, dans ton GROUP by, tu n'as besoin que de la "clef" de groupement.
Ca te prend vraiment 26 champs ?

Après, si c'est vraiment le cas, les autres solutions seront tout aussi lourdres (voire plus, à priori).

Mais si c'est vraiment juste l'expression GROUP BY qui fait peur au boss, il y a une autre solution :

Code :

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

1
2
3
 
SELECT id, row_number() over (partition by id order by status) as rk
FROM TaTable

Cela te donne toutes les lignes, qui sont numérotées par groupement de id, ordonnées par status : s'il y a un CL, il aura rk = 1, ...
Après, tu sur-requêtes ça pour ne prendre que la première ligne de chaque groupe :

Code :

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

1
2
3
4
5
6
 
SELECT id, CASE status WHEN CL THEN 1 ELSE 0 END
FROM (
SELECT id, status, row_number() over (partition by id order by status) as rk
FROM TaTable ) t
WHERE rk = 1

[Passarinho44]

Ca te prend vraiment 26 champs ?

>> Ben oui, tant que j'ai pas mis les 26champs dans le GROUP BY il me dit que le champ suivant n'est pas valide dans le SELECT car il n'est ni dans une fonction ni dans le GROUP BY

Sinon j'ai testé ta requête avec row_number(), cela me retourne encore 2lignes (une avec Is_Provider = 0 et l'autre où il est égal à 1)

[pacmann]

Hmmm, tu as essayé la première ou la deuxième requête ?
(c'est la deuxième qu'il faut prendre... et il n'est normalement pas possible d'avoir des doublons dans le résultat)

[Passarinho44]

Ben c'est bien la deuxième que j'ai essayé ^^

Mais j'ai quand même un doublon

Voilà comment je l'ai adapté : (dis moi si tu trouves une erreur )

Code :

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

1
2
3
(select (case when issue.status <> 'CNCL' and issue.zreference is not null then 1 else 0 end ) 
from ( select row_number() over (partition BY cr.persid ORDER BY issue.status) AS rk 
from issue) AS t where rk = 1 ) "Test"

[pacmann]

- Hmmm, c'est quoi cr.persid ?
- Mets un alias sur ta table issue (genre t), pour rendre les choses un peu plus claires...
- Inclus dans ton sélect le plus "profond" toutes les colonnes que tu utilises dans la sur-requête : à ce moment la, tu désigneras t.status au lieu de issue.status

Et réessaie pour voir...

[Passarinho44]

Alors ^^

- Hmmm, c'est quoi cr.persid ?

C'est l'id de la table principale que j'utilise : cr

- Mets un alias sur ta table issue (genre t), pour rendre les choses un peu plus claires...

Je trouve ça plus clair d'avoir le nom de la table en entier que d'avoir des t1, t2 etc ... Je fais appel à 19tables dans ma requête finale, c'est plus facile de s'y retrouver avec le nom complet

- Inclus dans ton sélect le plus "profond" toutes les colonnes que tu utilises dans la sur-requête : à ce moment la, tu désigneras t.status au lieu de issue.status

Le but est d'avoir une seule ligne par id (cr.persid ) et si j'intègre ces colonnes j'en ai au moins 2 pour mon exemple... (Si je met une colonne statut, il m'affichera forcément une ligne pour CL et une autre pour CNCL)

[EDIT] j'ai une question : il faut que je mette un group by sur quoi dans le select principal?
Parce qu'il me dit qu'il faut que je mette le status et le zreference dans un group by

[pacmann]

1) En fait, dans la requête que tu donnais, il n'y avait pas la table cr dans les clauses FROM. Si tu nous donne la requête complète, on pourra tenter de comprendre où ça coince

2)Les noms entiers, ça rend le texte de la requête plus épais... et en plus, il peut t'arriver d'avoir plusieurs fois la même table dans la jointure...

3) En fait, il n'y a pas de group by.
OVER (PARTITION BY) est un concept "relativement récent" au niveau des normes SQL (genre 2003, c'est ça ?).
Ca te permet d'appliquer des fonctions "de groupe", mais individuellement sur les lignes. C'est ce qui le rend plus fort que le GROUP BY dans certains cas...

En suite, par rapport à ta remarque Si je met une colonne statut, il m'affichera forcément une ligne pour CL et une autre pour CNCL :
le principe de la requête est de numéroter tes lignes par paquets définis par l'id.
Donc pour chaque id, tu auras une seule ligne portant le numéro 1.
Ensuite, quand tu fais une requête par dessus, tu ne gardes que ceux qui portent le numéro 1...

SELECT ... FROM (SELECT...) WHERE rk = 1

Et quand tu fais une manip de ce genre, il faut SELECTionner toutes les colonnes dont tu auras besoin à l'extérieur.

C'est un peu brouillon, ce que je raconte...

[Passarinho44]

Je viens de comprendre un truc ^^"
Le SELECT que tu m'a donné, je l'ai mis comme une colonne de mon SELECT alors qu'il devait le remplacer ...
Comment je peux mettre le deuxième SELECT (celui dans le FROM ) dans mon FROM sachant que dans le code qui suit, il n'y a qu'un left outer join alors qu'en vrai j'en ai 19 ??

Code :

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

1
2
3
4
5
6
7
8
9
10
SELECT
cr.persid	"persid" , 
(case (sum (case when issue.status <> 'CNCL' and issue.zreference is not null then 1 else 0 end)) when 0 then 0 else 1 end) "Iss_Provider_RL",
  /************ Ne fonctionne pas encore :	************/
 (select (case when issue.status <> 'CNCL' and issue.zreference is not null then 1 else 0 end ) from ( select row_number() over (partition BY cr.persid ORDER BY issue.status) AS rk from issue) AS t where rk = 1 ) "Test"
FROM
  dbo.call_req cr
LEFT OUTER JOIN dbo.issue issue ON issue.zincident = cr.persid
where cr.persid = 'cr:404804' /*Pour avoir un exemple*/
Group by issue.status, issue.zreference, cr.persid

(je ne t'ai mis que 3lignes sur les 27 au total dans le SELECT ^^
mais chacune de donne qu'une ligne par persid)

[pacmann]

Sacré pagaille !
Bon, je crois que tu as mélangé un peu certaines notions...

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
 
SELECT t.persid	"persid" , 
       case when t.STATUS <> 'CNCL' AND t.zreference IS NOT NULL then 1 else 0 end "Iss_Provider_RL"
FROM  
 (
 SELECT cr.persid, issue.status, issue.zreference
         row_number() over (partition BY cr.persid ORDER BY issue.STATUS) AS rk 
  FROM dbo.call_req cr LEFT OUTER JOIN dbo.issue issue 
    ON issue.zincident = cr.persid
 WHERE cr.persid = 'cr:404804' /*Pour avoir un exemple*/
 ) t
 
 WHERE t.rk = 1

La requête interne prend juste ta grosse jointure, et enrichit le select grâce à OVER (PARTITION...) : ce que tu vois là est la syntaxe exacte.
Tu n'as pas besoin d'encapsuler encore une fois dans des SELECT.

Code :

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

1
2
3
4
5
 SELECT cr.persid, issue.status, issue.zreference
         row_number() over (partition BY cr.persid ORDER BY issue.STATUS) AS rk 
  FROM dbo.call_req cr LEFT OUTER JOIN dbo.issue issue 
    ON issue.zincident = cr.persid
 WHERE cr.persid = 'cr:404804' /*Pour avoir un exemple*/

=> Tu n'as pas besoin de GROUP BY
=> Tu n'as pas besoin de faire des sum et des cases de sum et sum de case de cum de case de sum ...

[Passarinho44]

Oulala ... Je suis perdu là ...

Je vais te donner un exemple un peu plus large, tu peux m'expliquer mettre quoi où?
Je comprends pas dans quel FROM mettre le row_number(), dans quel SELECT mettre le case, s'il faut que je mette tous mes champs dans chacun des SELECT ...
Sachant qu'au final je ne veux pas afficher le issue.status ni le issue.zreference

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT
ct2.last_name "logAg_Grp" ,
cr.persid "persid" ,
ct6.last_name "ResAg_Grp" ,
FROM
dbo.call_req cr
LEFT OUTER JOIN dbo.ca_contact ct2	
ON cr.zgroup_creator = ct2.contact_uuid
LEFT OUTER JOIN dbo.ca_contact ct6
ON cr.zgroup_resolved = ct6.contact_uuid
LEFT OUTER JOIN dbo.issue issue
ON issue.zincident = cr.persid
WHERE cr.persid = 'cr:404804' /*Pour avoir un exemple*/

(J'ai enlever les conditions parce que je savais pas où les mettre ... )

Merci beaucoup de ton aide en tout cas

[EDIT]
Je viens de tester en mettant ça dans mon FROM principal :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
FROM
 (SELECT cr.persid, issue.status, issue.zreference,
	row_number() over (partition BY cr.persid ORDER BY issue.status) AS rk 
FROM dbo.call_req cr
LEFT OUTER JOIN dbo.ca_contact ct2	
ON cr.zgroup_creator = ct2.contact_uuid
LEFT OUTER JOIN dbo.ca_contact ct6	
ON cr.zgroup_resolved = ct6.contact_uuid
LEFT OUTER JOIN dbo.issue issue
ON issue.zincident = cr.persid
where cr.persid = 'cr:404804' ) t

Et du coup il ne reconnait plus tout mes champs (les last_name ) même quand je met t.last_name.

Pour qu'il les trouve, il faut que je les mette dans le SELECT qui est à l'intérieur du FROM.
C'est normal?

Parce que du coup ça reviendrait à un GROUP BY non?

[EDIT]
J'ai mis dans le SELECT du FROM tout les champs du SELECT principal et dans le SELECT principal je leur ai tous mis la table t.
Ca m'affiche bien une seule ligne par persid!
Pas de soucis donc, mais le défaut de mettre 2fois chaque champs du SELECT reste...