IdentifiantMot de passe
Loading...
Mot de passe oublié ?Je m'inscris ! (gratuit)
Navigation

Inscrivez-vous gratuitement
pour pouvoir participer, suivre les réponses en temps réel, voter pour les messages, poser vos propres questions et recevoir la newsletter

DB2 Discussion :

SELECT EXISTS et sous-requête correlée


Sujet :

DB2

  1. #1
    Nouveau membre du Club
    Homme Profil pro
    Administrateur de base de données
    Inscrit en
    août 2017
    Messages
    34
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 29
    Localisation : Belgique

    Informations professionnelles :
    Activité : Administrateur de base de données
    Secteur : Bâtiment

    Informations forums :
    Inscription : août 2017
    Messages : 34
    Points : 36
    Points
    36
    Par défaut SELECT EXISTS et sous-requête correlée
    Bonjour les internautes,

    Je dispose d'une table, appelons là Subjects pour laquelle je voudrais vérifier l'existence d'une vente dans une table que nous appellerons Sales

    Voici le code de la création de mon exemple :
    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
    CREATE TABLE IF NOT EXISTS Subjects
    (
    	id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    	subject VARCHAR(50),
    	option1 NUMERIC,
    	option2 NUMERIC,
    	option3 NUMERIC,
    	PRIMARY KEY (ID)
    );
     
    CREATE TABLE IF NOT EXISTS Sales
    (
    	id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    	subject VARCHAR(50),
    	sale NUMERIC,
    	dateSale DATE,
    	PRIMARY KEY (ID)
    );
    Populons ces tables avec quelques données :
    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
    INSERT INTO Subjects
    VALUES
    (NULL, 'A', 1, 0, 0),
    (NULL, 'B', 2, 0, 0),
    (NULL, 'C', 0, 10, 0),
    (NULL, 'D', 0, 20, 0),
    (NULL, 'E', 0, 30, 0),
    (NULL, 'F', 0, 40, 0),
    (NULL, 'G', 0, 0, 100),
    (NULL, 'H', 0, 0, 200),
    (NULL, 'I', 0, 0, 300),
    (NULL, 'J', 1, 10, 0),
    (NULL, 'K', 1, 0, 100),
    (NULL, 'L', 0, 10, 100);
     
    INSERT INTO Sales
    VALUES
    (NULL, 'A', 25, '2019-02-12 00:00:00'),
    (NULL, 'A', 10, '2020-04-20 00:00:00'),
    (NULL, 'C', 15, '2019-03-15 00:00:00'),
    (NULL, 'C', 42, '2019-05-17 00:00:00'),
    (NULL, 'C', 75, '2019-10-10 00:00:00'),
    (NULL, 'D', 12, '2020-06-07 00:00:00'),
    (NULL, 'E', 29, '2019-02-12 00:00:00'),
    (NULL, 'F', 77, '2020-05-14 00:00:00'),
    (NULL, 'F', 88, '2020-11-12 00:00:00'),
    (NULL, 'K', 99, '2018-09-30 00:00:00');
    Mon but est de savoir, pour chaque Subject, quelle est l'option qu'on lui a choisis, et savoir si oui ou non on l'a vendu à partir d'une date donnée.
    Ensuite, j'utilise des fonctions d'aggrégation pour compter le nombre de Subjets de chaque option et son statut de vente.

    Le résultat doit ressemble à ça :
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    +---------+-------+---------+
    | choice  | saled | nbr_sub |
    +---------+-------+---------+
    | option1 |     1 |       1 |
    | option1 |     0 |       1 |
    | option2 |     0 |       2 |
    | option2 |     1 |       2 |
    | option3 |     0 |       3 |
    | unknown |     0 |       3 |
    +---------+-------+---------+
    Les unknown désignent les Subjects avec plusieurs options complétées.

    Pour arriver à ce résultat, j'ai utilisé la requête suivante :
    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
    SELECT
    s.opt,
    s.saled,
    COUNT(s.id) AS nbr_sub
     
    FROM
    (
    	SELECT
    	su.id,
    	su.subject,
    	CASE
    		WHEN su.option1 <> 0 AND su.option2 = 0 AND su.option3 = 0 THEN 'option1'
    		WHEN su.option1 = 0 AND su.option2 <> 0 AND su.option3 = 0 THEN 'option2'
    		WHEN su.option1 = 0 AND su.option2 = 0 AND su.option3 <> 0 THEN 'option3'
    		ELSE 'unknown'
    	END AS opt,
    	EXISTS
    	(
    		SELECT
    		1
     
    		FROM
    		Sales AS sa
     
    		WHERE
    		sa.subject = su.subject
    		AND YEAR(sa.dateSale) > 2019
    	) AS saled
     
    	FROM
    	Subjects AS su
    ) AS s
     
    GROUP BY
    s.opt,
    s.saled
    Celle-ci fonctionne à merveille sur serveur MySQL. Mais une fois adaptée à ma DB de production DB2, ça coince.
    Le soucis semble venir de la sous-requête du EXISTS dans le SELECT. En effet, j'y fait référence à une table de la requête parent. Cependant, le FROM se situe après le SELECT et je suspecte que ce soit la cause de mon malheur. Parce que la même requête avec le EXISTS dans la clause WHERE me donne un résultat.
    Quelque chose comme ceci :

    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
    SELECT
    s.opt,
    -- s.saled,
    COUNT(s.id) AS nbr_sub
     
    FROM
    (
    	SELECT
    	su.id,
    	su.subject,
    	CASE
    		WHEN su.option1 <> 0 AND su.option2 = 0 AND su.option3 = 0 THEN 'option1'
    		WHEN su.option1 = 0 AND su.option2 <> 0 AND su.option3 = 0 THEN 'option2'
    		WHEN su.option1 = 0 AND su.option2 = 0 AND su.option3 <> 0 THEN 'option3'
    		ELSE 'unknown'
    	END AS opt
     
    	FROM
    	Subjects AS su
    ) AS s
     
    WHERE
    EXISTS
    (
    	SELECT
    	1
     
    	FROM
    	Sales AS sa
     
    	WHERE
    	sa.subject = s.subject
    	AND YEAR(sa.dateSale) > 2019
    )
     
    GROUP BY
    s.opt
    Pouvez-vous m'aider à résoudre ce casse-tête ?

    Je vous remercie.

  2. #2
    Expert confirmé
    Profil pro
    Inscrit en
    août 2008
    Messages
    2 891
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : août 2008
    Messages : 2 891
    Points : 5 696
    Points
    5 696
    Par défaut
    Je ne connais pas cette syntaxe avec EXISTS dans le SELECT, j'imagine que c'est une syntaxe spécifique mysql, ou en tout cas que BD2 ne connais pas.

    Je rempacerais cette partie par :
    Un max pour ne ramener q'une ligne dans la sous-requête scalaire ce qui revient à faire une sorte de test d'existence.
    Un COALESCE pour transformer les NULL renvoyés par la non existence en 0.
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    coalesce(SELECT max(1)
              FROM Sales AS sa
             WHERE sa.subject = su.subject
               AND YEAR(sa.dateSale) > 2019
    	, 0) AS saled

  3. #3
    Membre du Club
    Profil pro
    Inscrit en
    octobre 2008
    Messages
    50
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : octobre 2008
    Messages : 50
    Points : 69
    Points
    69
    Par défaut
    Bonjour,

    Il y a une subtilité (voire un bug) dans l'utilisation du prédicat EXISTS dans les SELECT en DB2 comme BOOLEAN, l'optimisation fait que ça retourne une valeur nulle dans une colonne non nulle et le client qui ne sais pas quoi en faire le plus souvent considère TRUE.
    Il faut l'entourer d'une CASE expression pour que ça fonctionne bien,
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    case when exists(...) then true else false end
    Voir https://dbfiddle.uk/?rdbms=db2_11.1&...458e4c0c0f624e

  4. #4
    Nouveau membre du Club
    Homme Profil pro
    Administrateur de base de données
    Inscrit en
    août 2017
    Messages
    34
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 29
    Localisation : Belgique

    Informations professionnelles :
    Activité : Administrateur de base de données
    Secteur : Bâtiment

    Informations forums :
    Inscription : août 2017
    Messages : 34
    Points : 36
    Points
    36
    Par défaut
    Bonjour,

    Merci à vous deux pour vos interventions.

    vazymimil, j'ai tenté d'encapsuler mon EXISTS par un CASE mais rien n'y fait. J'ai toujours un message d'erreur.
    J'ai d'ailleurs essayé une version ultra simplifiée d'un SELECT EXISTS... (avec et sans le CASE) sur ma DB de production. Pas moyen d'obtenir un résultat.

    J'ai trouvé une autre solution, un peu moche mais qui fait le travail. Il s'agit de joindre deux requêtes par un UNION. La première avec les Subjects vendus, avec un EXISTS dans le WHERE et où je force Saled à 1, suivi de la seconde, avec les Subjects non vendus, avec un NOT EXISTS où je force Saled à 0.
    Comme ceci en somme :
    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
    65
    66
    67
    68
    69
    70
    71
    72
    73
    74
    75
    76
    77
    SELECT
    s.opt,
    1 AS saled,
    COUNT(s.id) AS nbr_sub
     
    FROM
    (
    	SELECT
    	su.id,
    	su.subject,
    	CASE
    		WHEN su.option1 <> 0 AND su.option2 = 0 AND su.option3 = 0 THEN 'option1'
    		WHEN su.option1 = 0 AND su.option2 <> 0 AND su.option3 = 0 THEN 'option2'
    		WHEN su.option1 = 0 AND su.option2 = 0 AND su.option3 <> 0 THEN 'option3'
    		ELSE 'unknown'
    	END AS opt
     
    	FROM
    	Subjects AS su
    ) AS s
     
    WHERE
    EXISTS
    (
    	SELECT
    	1
     
    	FROM
    	Sales AS sa
     
    	WHERE
    	sa.subject = s.subject
    	AND YEAR(sa.dateSale) > 2019
    )
     
    GROUP BY
    s.opt
     
    UNION
     
    SELECT
    s.opt,
    0 AS saled,
    COUNT(s.id) AS nbr_sub
     
    FROM
    (
    	SELECT
    	su.id,
    	su.subject,
    	CASE
    		WHEN su.option1 <> 0 AND su.option2 = 0 AND su.option3 = 0 THEN 'option1'
    		WHEN su.option1 = 0 AND su.option2 <> 0 AND su.option3 = 0 THEN 'option2'
    		WHEN su.option1 = 0 AND su.option2 = 0 AND su.option3 <> 0 THEN 'option3'
    		ELSE 'unknown'
    	END AS opt
     
    	FROM
    	Subjects AS su
    ) AS s
     
    WHERE
    NOT EXISTS
    (
    	SELECT
    	1
     
    	FROM
    	Sales AS sa
     
    	WHERE
    	sa.subject = s.subject
    	AND YEAR(sa.dateSale) > 2019
    )
     
    GROUP BY
    s.opt
    Et ça fait le travail. J'aimerais avoir le temps d'investiguer plus la question mais ce n'est pas le cas.
    Je vous remercie pour votre aide.

    Catab

+ Répondre à la discussion
Cette discussion est résolue.

Discussions similaires

  1. Select avec une "sous-requête"
    Par flipflip dans le forum SQL
    Réponses: 2
    Dernier message: 04/09/2017, 13h45
  2. SELECT, JOIN et sous-requêtes dans une même colonne
    Par Alcorak dans le forum Langage SQL
    Réponses: 2
    Dernier message: 09/06/2011, 09h04
  3. [Débutant] Requête SELECT avec max et sous-requête
    Par joefou dans le forum Langage SQL
    Réponses: 2
    Dernier message: 27/07/2005, 14h28
  4. Sous-requête dans la clause Select
    Par Danger dans le forum WinDev
    Réponses: 2
    Dernier message: 24/05/2005, 17h33
  5. Insertion multiple à base de sous requête SELECT
    Par drinkmilk dans le forum Langage SQL
    Réponses: 8
    Dernier message: 14/04/2005, 16h34

Partager

Partager
  • Envoyer la discussion sur Viadeo
  • Envoyer la discussion sur Twitter
  • Envoyer la discussion sur Google
  • Envoyer la discussion sur Facebook
  • Envoyer la discussion sur Digg
  • Envoyer la discussion sur Delicious
  • Envoyer la discussion sur MySpace
  • Envoyer la discussion sur Yahoo