Discussion :

[amalch]

Bonjour,
j'utilise une base de données Oracle pour générer des rapports avec SQL Server Reporting Services.
en fait j'ai pas pu créer mon rapport à cause d'erreur de syntaxe pouvez vous m'aider pour remédier à ce problème?
Merci infiniment


Requête générée:

Code sql :

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
SELECT case a.succes 
           WHEN 100 THEN 'SUCCES'
        END as status ,                  
            COUNT (*) AS nombre,TRUNC( COUNT (*) *100/ ( select count(*) from  report_backup a, spec_dest b
          WHERE a.specification_n = b.specification_n
AND ( a.specification_n  LIKE 'Oracle%ONLINE%' or a.specification_n like 'Oracle%AREA%' )
                AND a.specification_n LIKE '%Oracle8%'
                AND a.TYPE_SESSION = 'Backup'
                and  (a.starttime >  (sysdate - 1))),2) || ' %'  as pourcentage
            FROM report_backup a, spec_dest b
          WHERE a.specification_n = b.specification_n
AND ( a.specification_n  LIKE 'Oracle%ONLINE%' or a.specification_n like 'Oracle%AREA%' )
                AND a.specification_n LIKE '%Oracle8%'
                            and succes = '100' 
                AND a.TYPE_SESSION = 'Backup'
                and  (a.starttime >  (sysdate - 1))
       GROUP BY  ( CASE a.succes
           WHEN 100 THEN 'SUCCES'
        END ) 
UNION        
SELECT 
  case a.STATUS 
           WHEN 'In Progress' THEN 'EN COURS'
           WHEN 'In Progress/Errors' THEN 'EN COURS'
           else 'ECHEC'
        END as status ,                  
            COUNT (*) AS nombre,TRUNC( COUNT (*) *100/ ( select count(*) from  report_backup a, spec_dest b
          WHERE a.specification_n = b.specification_n
AND ( a.specification_n  LIKE 'Oracle%ONLINE%' or a.specification_n like 'Oracle%AREA%' )
                AND a.specification_n LIKE '%Oracle8%'
                AND a.TYPE_SESSION = 'Backup'
                and  (a.starttime >  (sysdate - 1))),2) || ' %'  as pourcentage
            FROM report_backup a, spec_dest b
          WHERE a.specification_n = b.specification_n
AND ( a.specification_n  LIKE 'Oracle%ONLINE%' or a.specification_n like 'Oracle%AREA%' )
                AND a.specification_n LIKE '%Oracle8%'
                            and succes <> '100' 
                AND a.TYPE_SESSION = 'Backup'
                and  (a.starttime >  (sysdate - 1))
                and not exists (select * from report_backup t2 where succes = 100
and (t2.starttime >(SYSDATE -1))
and (a.specification_n=t2.specification_n) and (t2.starttime > a.starttime))
       GROUP BY  (   case a.STATUS 
           WHEN 'In Progress' THEN 'EN COURS'
           WHEN 'In Progress/Errors' THEN 'EN COURS'
           else 'ECHEC'
        END ) 
         UNION        
SELECT 
  case a.STATUS 
           WHEN 'In Progress' THEN 'EN COURS'
           WHEN 'In Progress/Errors' THEN 'EN COURS'
           else 'RELANCE AVEC SUCCES'
        END as status ,                  
            COUNT (*) AS nombre,TRUNC( COUNT (*) *100/ ( select count(*) from  report_backup a, spec_dest b
          WHERE a.specification_n = b.specification_n
AND ( a.specification_n  LIKE 'Oracle%ONLINE%' or a.specification_n like 'Oracle%AREA%' )
                AND a.specification_n LIKE '%Oracle8%'
                AND a.TYPE_SESSION = 'Backup'
                and  (a.starttime >  (sysdate - 1))),2) || ' %'  as pourcentage
            FROM report_backup a, spec_dest b
          WHERE a.specification_n = b.specification_n
AND ( a.specification_n  LIKE 'Oracle%ONLINE%' or a.specification_n like 'Oracle%AREA%' )
                AND a.specification_n LIKE '%Oracle8%'
                            and succes <> '100' 
                AND a.TYPE_SESSION = 'Backup'
                and  (a.starttime >  (sysdate - 1))
                and  exists (select * from report_backup t2 where succes = 100
and (t2.starttime >(SYSDATE -1))
and (a.specification_n=t2.specification_n) and (t2.starttime > a.starttime))
       GROUP BY  (   case a.STATUS 
           WHEN 'In Progress' THEN 'EN COURS'
           WHEN 'In Progress/Errors' THEN 'EN COURS'
           else 'RELANCE AVEC SUCCES'
        END );

Erreur obtenue:

Code :

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

1
2
3
4
5
6
Error in SELECT clause: expression near '.'.
Missing FROM clause.
Error in SELECT clause: expression near ','.
Error in list of function arguments: 'SELECT' not recognized.
Error in list of function arguments: 'FROM' not recognized.
Unable to parse query text.

[Waldar]

Commencez par avoir une mise en page de votre requête qui soit cohérente et donc lisible : c'est le plus simple pour repérer les erreurs de syntaxe - enfin quand on n'a pas le numéro de ligne voire même le mot-clef qui pose problème directement indiqué.