PC Easy Weblog

mai 18, 2009

SQL Server – UPDATE de lignes d’une table par un SELECT

Filed under: Développement, Utilitaires — Étiquettes : , , , — trucmuche92 @ 9:26

Pour ceux qui comme moi ne se rappelaient plus comment on mettait à jour une table à partir d’un SELECT voici un mémo :

Le principe est de se représenter dans un premier temps un SELECT (multi-table) qui contient la table que l’on veut mettre à jour.

L’exemple ci-dessous mettra a jour le champ-flag ACTIF d’une table PRODUIT en fonction du champ VISIBLE du d’une table d’association CATALOGUE_PRODUIT dans lequel il est.

1 – Représentation en SELECT

select p.pro_actif, c.pro_visible, etc…
from PRODUIT p
inner join  CATALOGUE_PRODUIT c
on p.pro_codesap = c.pro_codesap

2 – Pour la commande d’UPDATE, on dégage la partie SELECT pour la remplacer par la syntaxe UPDATE

update p set p.pro_actif = c.pro_visible, p.datmaj = getdate()
from PRODUIT p
inner join  CATALOGUE_PRODUIT c
on p.pro_codesap = c.pro_codesap

On remarquera l’importance du raccourci (alias) p pour la table PRODUIT qui est représentée à la fois pour la table du SELECT et la table de l’UPDATE.

Remarque : on ne peut mettre à jour que les champs d’une table.

Publicités

mars 3, 2009

SQL Server – ROW_NUMBER(), l’équivalent du ROWNUM d’Oracle

Filed under: Développement — Étiquettes : — trucmuche92 @ 8:54

Avec SQL Server 2005, on cette nouvelle fonction qui permet de numéroté d’une façon distinct chaque ligne : ROW_NUMBER()

Prenons par exemple le contenu de la table suivante :

select codex, commune from villes
codex commune
----- --------------------
02300 ABBECOURT
20243 ABBAZIA
25320 ABBANS DESSOUS
25440 ABBANS DESSUS
44170 ABBARETZ
54610 ABAUCOURT
55130 ABAINVILLE
55400 ABAUCOURT HAUTECOURT
59268 ABANCOURT
60220 ABANCOURT
60430 ABBECOURT
64460 AAST

Il s’agit donc de numéroter chaque ligne, mais ROW_NUMBER() nécessite de préciser sur quelle champ on défini l’ordre de numérotation.
Aussi la syntaxe exacte pour le champ qui reçoit la numérotation est  :

ROW_NUMBER() over(order by champ)

Avec le jeu de données ci-dessus, on pourra numéroter les lignes par ordre croissant de commune :

select codex, commune, row_number() over(order by commune) as num
from villes 
codex commune              num
----- -------------------- --------------------
64460 AAST                 1
55130 ABAINVILLE           2
59268 ABANCOURT            3
60220 ABANCOURT            4
54610 ABAUCOURT            5
55400 ABAUCOURT HAUTECOURT 6
25320 ABBANS DESSOUS       7
25440 ABBANS DESSUS        8
44170 ABBARETZ             9
20243 ABBAZIA              10
02300 ABBECOURT            11
60430 ABBECOURT            12

On remarquera la fonction ROW_NUMBER() filera toujours un numéro distinct, même si le champ sur lequel est poser la numérotation est le même d’une ligne à l’autre (commune ABANCOURT), il s’agit donc bien d’un numéro de ligne.

On pourra conserver la numérotation ci dessus, mais en présentant les données ordonnées par code postale

select codex, commune, row_number() over(order by commune) as num
from villes order by codex
codex commune              num
----- -------------------- --------------------
02300 ABBECOURT            11
20243 ABBAZIA              10
25320 ABBANS DESSOUS       7
25440 ABBANS DESSUS        8
44170 ABBARETZ             9
54610 ABAUCOURT            5
55130 ABAINVILLE           2
55400 ABAUCOURT HAUTECOURT 6
59268 ABANCOURT            3
60220 ABANCOURT            4
60430 ABBECOURT            12
64460 AAST                 1

Ca c’est interressant !

Pour aller plus loin :

– on peut compléter la partie order by par des champs complémentaires :

select codex, dpt, commune,
row_number() over(order by Dpt asc, commune desc) as num
from villes

– ROW_NUMBER() a aussi son petit frère RANK() qui effectue un classement :

select codex, commune, rank() over(order by commune) as num
from villes order by commune
codex commune              num
----- -------------------- --------------------
64460 AAST                 1
55130 ABAINVILLE           2
59268 ABANCOURT            3
60220 ABANCOURT            3
54610 ABAUCOURT            5
55400 ABAUCOURT HAUTECOURT 6
25320 ABBANS DESSOUS       7
25440 ABBANS DESSUS        8
44170 ABBARETZ             9
20243 ABBAZIA              10
02300 ABBECOURT            11
60430 ABBECOURT            11

février 27, 2009

SQL Server – Tableau croisé oui, dynamique non

Filed under: Développement — Étiquettes : , — trucmuche92 @ 9:12

MS Access fournie une commande SQL qui permet de faire des requêtes SELECT de type tableau croisé dynamique (via la commande TRANSFORM). Dans ce cas, on est bien présence d’un tableau dynamiqe car le nombre de colonnes généré est dynamique en fonction des enregistrements du jeu de résultat.

Pour SQL Server, depuis sa version 2005, on a la syntaxe PIVOT qui permet de créer un tableau croisé à partir d’une table, en revanche pour ce qui est du dynamique, c’est pas encore ça.

En effet, il faut préciser les valeurs que prendront les colonnes, illustration :

On a une table cde_entete

3 champs-propriété : cde_liv_civilite, cde_status, cde_mode_liv

un champs numérique : cde_mnt_ht (celui qui aura l’agregat)

On veut un tableau croisé avec les données de la colonne « Mode livraison » (cde_mode_liv) en colonne :

SELECT
cde_liv_civilite,
cde_status,
SUM([1]) AS "avion",
SUM([2]) AS "train"
FROM  cde_entete
PIVOT ( SUM(cde_mnt_ht)
        FOR cde_mode_liv IN ([1], [2])
      ) AS xxx
GROUP BY cde_liv_civilite, cde_status

Pour comprendre, il faut prendre la syntaxe ci-dessus comme un SELECT traditionnel où xxx transforme la table cde_entete avec les valeurs du champ cde_mode_liv en tant que colonne.

Le blème, c’est que ces valeurs doivent être explicitement définies « IN ([1], [2])« , et c’est là qu’on perds l’aspect dynamique.

En revanche, là où ça peut être interressant c’est quand veut un resultset d’indicateurs très variés pour lesquels il nécessiterait d’effectuer de multiple jointures.
L’astuce résiderait à créer un resultset par une série de requêtes UNION dont 2 colonnes pivoteraient au final.

1 – Obtention d’indicateurs et de dimensions depuis plusieurs sourcing

        --1ere sourcing
        select
            year(dt_record) as ANNEE,
            'EMPLOYE' as DIMENSION,
            count(cd_employee) as VALEUR
            from RESOURCES group by year(dt_record)

    UNION
        --2ème sourcing
        select
            year(dt_planning) as ANNEE,
            'BUDGET' as DIMENSION,
            sum (AMOUNT) as VALEUR
            from BUD group by year(dt_planning)

    UNION
        --3ème sourcing
        select
            year(dt_sale) as ANNEE,
            'CA' as DIMENSION,
            sum (SALES) as VALEUR
            from PRODUCTS group by year(dt_sale)

2 – Le résultat de l’UNION sans le PIVOT :

ANNEE       DIMENSION VALEUR
----------- --------- -----------
2006        BUDGET    50000
2007        BUDGET    55000
2008        BUDGET    60000
2005        CA        100000
2006        CA        110000
2007        CA        55
2007        CA        66
2007        CA        120000
2005        EMPLOYE   80
2006        EMPLOYE   86
2007        EMPLOYE   90
2008        EMPLOYE   91

3 – En passant le PIVOT comme ceci…

SELECT
ANNEE,
SUM([EMPLOYE]) AS "Nbr Employés",
SUM([BUDGET]) AS "Budget planifié",
SUM([CA]) AS "Chiffre d'affaire"
FROM (

        --1ere sourcing
        select
            year(dt_record) as ANNEE,
            'EMPLOYE' as DIMENSION,
            count(cd_employee) as VALEUR
            from RESOURCES group by year(dt_record)

    UNION
        --2ème sourcing
        select
            year(dt_planning) as ANNEE,
            'BUDGET' as DIMENSION,
            sum (AMOUNT) as VALEUR
            from BUD group by year(dt_planning)

    UNION
        --3ème sourcing
        select
            year(dt_sale) as ANNEE,
            'CA' as DIMENSION,
            sum (SALES) as VALEUR
            from PRODUCTS group by year(dt_sale)

) MES_UNIONS
PIVOT ( SUM(VALEUR)
        FOR DIMENSION IN ([EMPLOYE], [BUDGET], [CA])
      ) AS bidon
GROUP BY ANNEE

4 – Résultat final :

ANNEE       Nbr Employés Budget planifié Chiffre d'affaire
----------- ------------ --------------- -----------------
2005        80                           100000
2006        86           50000           110000
2007        90           55000           120121
2008        91           60000

Cette méthode est très utile pour du reporting par exemple, et évite d’avoir recours à de multiples jointure externes.

SQL Server – Le cas du Case pour une comparaison

Filed under: Développement — Étiquettes : , — trucmuche92 @ 7:48

En SQL Server, l »utilisation de la syntaxe Case … When … When…else…end est bien utile quand on veut inclure un test dans une requête SELECT :

Exemple :

select nom, prenom, 
case civ 
when 1 then 'Monsieur'
when 2 then 'Madame'
else 'Non mentionné' end as civilite
from employes

Il arrive parfois que le test a effectuer ne s’appuie pas sur une égalité, mais sur une comparaison, la syntaxe est particulière et est la suivante :

select nom, prenom,
case 
        when LastActivityDate < '2008-01-01' then 'Très Longtemps'
        when LastActivityDate < '2008-07-01' then 'Longtemps'
        else 'Récement' end as "Dernère visite"
from aspnet_users

En fait la syntaxe peut paraître différente, mais pas pour tout le monde car les 2 commandes suivantes sont les mêmes et sont valides :

select nom, prenom, case civ 
                    when 1 then 'Monsieur'
                    when 2 then 'Madame'
                    else 'Non mentionné' 
                    end as civilite
from employes

select nom, prenom, case
                    when civ=1 then 'Monsieur' 
                    when civ=2 then 'Madame' 
                    else 'Non mentionné'
                    end as civilite
from employes

Propulsé par WordPress.com.