Discussion :

[i.chafai]

Bonjour,
je dois fournir une requetes à plusieurs de mes clients pour un reporting adhoc (qui va être effectué via PowerBI et un cube SSAS Tabulaire => donc je dois avoir un modèle générique et standard qui pourra être déployer chez tous les clients), certaines tables contienne des colonnes qui sont ajoutés à la volé au niveau du paramétrage de l'ERP donc impossible de savoir les colonnes qui sont ajoutés sur chaque base de données.
je peux avoir les colonnes qui ont été ajouté à partir d'une table de paramétrage. j'ai développé donc une requête pour retourner les 10 premières colonnes de chaque table.
- j'ai pensé à mettre ma requete dans une fonction table pour retourner une variable table avec mais malheureusement les udf n'accèptent pas le sql dynamique
- les tables peuvent avoir de 0 à 69 colonnes dynamique et je vais prendre seulement les 10 premières colonnes donc un petit problème pour les tables qui contiennent un nombre de colonne < 10

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
 
CREATE FUNCTION reporting.udfInfoLibre
(
  @file NVARCHAR(MAX)
)
RETURNS @Libre TABLE
(
		CT_Num VARCHAR(10), 
		 Info1  VARCHAR(69), 
		 Info2  VARCHAR(69), 
		 Info3  VARCHAR(69)
		--,
		--Info4 varchar(69),
		--Info5 varchar(69),
		--Info6 varchar(69),
		--Info7 varchar(69),
		--Info8 varchar(69),
		--Info9 varchar(69),
		--Info10 varchar(69)
)
AS
     BEGIN
         DECLARE @col NVARCHAR(MAX);
         DECLARE @sql NVARCHAR(MAX);
         SELECT @col = COALESCE(@col + ',[' + CAST(CB_Name AS VARCHAR) + ']', '[' + CAST(CB_Name AS VARCHAR) + ']') + ' as ' + t.alias
         FROM
         (
             SELECT CB_Name AS CB_Name, 
                    'Info' + CAST(ROW_NUMBER() OVER(
                    ORDER BY CB_Pos) AS VARCHAR(3)) AS alias
             FROM cbSysLibre
             WHERE CB_File = @file
                   AND CB_Pos <= 10
         ) AS t;
         SET @sql = ' SELECT CT_Num ,' + @col + ' FROM F_COMPTET WHERE CT_Type = 0';
         EXEC sp_executesql 
              @sql;
         RETURN;
     END;

[i.chafai]

je pense que la meilleure et la plus simple solution et d'utiliser mon script dynamique dans une procédure stocké, et cette dernière elle va me créé un view avec le nom et les alias de colonnes que je définie dans le code.
de toute les colonnes seront et situation quasi-statique après la phase du paramétrage de l'ERP, et en cas de besoin je re-execute la proc

[i.chafai]

Je me trouve juste bloqué au niveau du nombre de colonne, dans mon cas la table des clients contient uniquement 3 colonnes libres et j'ai besoin de créer une view avec 10 colonnes

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
 
 
IF OBJECT_ID('reporting.vInfoLibreCient', 'v') IS NOT NULL
    DROP VIEW reporting.vInfoLibreCient;
DECLARE @col NVARCHAR(MAX);
DECLARE @sql NVARCHAR(MAX);
SELECT @col = COALESCE(@col + ',[' + CAST(CB_Name AS VARCHAR) + ']', '[' + CAST(CB_Name AS VARCHAR) + ']') + ' as ' + t.alias
FROM
(
    SELECT CB_Name AS CB_Name, 
           'Info' + CAST(ROW_NUMBER() OVER(
           ORDER BY CB_Pos) AS VARCHAR(3)) AS alias
    FROM cbSysLibre
    WHERE CB_File = 'F_Comptet'
          AND CB_Pos <= 10
) AS t;
SET @sql = 'CREATE VIEW reporting.vInfoLibreCient (CT_Num,info1,info2,info3,info4) as  SELECT CT_Num ,' + @col + ' FROM F_COMPTET WHERE CT_Type = 0';
EXEC (@sql);

l'erreur bien sur :

Code :

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

Msg 8159, Niveau 16, État 1, Procédure vInfoLibreCient, Ligne 1'vInfoLibreCient' a moins de colonnes que celles spécifiées dans la liste des colonnes.

[i.chafai]

J'ai trouvé une solution à mon besoin mais surement il y aura une grande optimisation à faire

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
ALTER PROCEDURE reporting.InstallInfoLibreCLIAS
    BEGIN
        SET NOCOUNT ON;
        IF OBJECT_ID('reporting.vInfoLibreCient', 'v') IS NOT NULL
            DROP VIEW reporting.vInfoLibreCient;
        DECLARE @col NVARCHAR(MAX);
        DECLARE @sql NVARCHAR(MAX);
        WITH ml
             AS (SELECT '1' AS CB_Name, 
                        1 AS CB_Pos, 
                        'F_Comptet' AS CB_File
                 UNION
                 SELECT '1' AS CB_Name, 
                        2 AS CB_Pos, 
                        'F_Comptet' AS CB_File
                 UNION
                 SELECT '1' AS CB_Name, 
                        3 AS CB_Pos, 
                        'F_Comptet' AS CB_File
                 UNION
                 SELECT '1' AS CB_Name, 
                        4 AS CB_Pos, 
                        'F_Comptet' AS CB_File
                 UNION
                 SELECT '1' AS CB_Name, 
                        5 AS CB_Pos, 
                        'F_Comptet' AS CB_File
                 UNION
                 SELECT '1' AS CB_Name, 
                        6 AS CB_Pos, 
                        'F_Comptet' AS CB_File
                 UNION
                 SELECT '1' AS CB_Name, 
                        7 AS CB_Pos, 
                        'F_Comptet' AS CB_File
                 UNION
                 SELECT '1' AS CB_Name, 
                        8 AS CB_Pos, 
                        'F_Comptet' AS CB_File
                 UNION
                 SELECT '1' AS CB_Name, 
                        9 AS CB_Pos, 
                        'F_Comptet' AS CB_File
                 UNION
                 SELECT '1' AS CB_Name, 
                        10 AS CB_Pos, 
                        'F_Comptet' AS CB_File)
             SELECT @col = COALESCE(@col + ',' + CAST(CB_Name AS VARCHAR) + '', '' + CAST(CB_Name AS VARCHAR) + '') + ' as ' + t.alias
             FROM
             (
                 SELECT ISNULL(QUOTENAME(s.CB_Name),'''''') AS CB_Name, 
                        'Info' + CAST(ROW_NUMBER() OVER(
                        ORDER BY ISNULL(s.CB_Pos, ml.CB_Pos)) AS VARCHAR(3)) AS alias
                 FROM cbSysLibre s
                      RIGHT JOIN ml ON s.CB_Pos = ml.CB_Pos
                                       AND s.CB_File = ml.CB_File
             ) AS t;
        SET @sql = 'CREATE VIEW reporting.vInfoLibreCient (CT_Num,info1,info2,info3,info4,info5,info6,info7,info8,info9,info10) as  SELECT CT_Num ,' + @col + ' FROM F_COMPTET WHERE CT_Type = 0';
         EXEC (@sql);
        --PRINT @sql
    END;