Retour conditionnel d'une colonne

**velo83** · 17/04/2014, 16h48

Bonjour,

ci-apres le sql

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
SELECT  p.MATRICULE,
                  p.NOM,
                  p.PRENOM,
                  c.DATEJ,
           CASE WHEN TRIM(TO_CHAR(c.DATEJ,'DAY')) = 'MONDAY' 
           THEN CASE      WHEN ((ABS(c.CPT005) - trunc(ABS(c.CPT005)))*60) < 10
                 THEN
                    TRUNC(c.CPT005)||' h 0'||
                    TRUNC(ROUND((c.CPT005 - trunc(c.CPT005))*60))||' min'
                 ELSE
                    TRUNC(c.CPT005)||' '||TRUNC(ROUND((c.CPT005 - TRUNC(c.CPT005))*60))
                          ||' min' 
                 END                                                     
           ELSE 
                 CASE     WHEN ((ABS(c.CPT005 + c.CPT006) - trunc(ABS(c.CPT005 + 
                          c.CPT006)))*60) < 10
                 THEN
                          TRUNC(c.CPT005 + c.CPT006)||' h 0'||TRUNC(ROUND(((c.CPT005 + 
                          c.CPT006) - trunc(c.CPT005 + c.CPT006))*60))||' min'
                 ELSE
                          TRUNC(c.CPT005 + c.CPT006)||' h '||TRUNC(ROUND(((c.CPT005 
                          + c.CPT006) - trunc(c.CPT005 + c.CPT006))*60))||' min'
                 END                                                     
           END    CUMUL_HEBDO_COMPTA,
           CASE WHEN TRIM(TO_CHAR(c.DATEJ,'DAY')) = 'MONDAY' 
                     THEN 
                 CASE     WHEN ((ABS(c.CPT011) - trunc(ABS(c.CPT011)))*60) < 10
                 THEN
                 trunc(c.CPT011)||' h 0'||TRUNC(ROUND((c.CPT011 - trunc(c.CPT011))*60))||' min' 
                 ELSE
                 trunc(c.CPT011)||' h '||TRUNC(ROUND((c.CPT011 - trunc(c.CPT011))*60))||' min' 
                 END
                  ELSE 
                 CASE     WHEN ((ABS(c.CPT011 + c.CPT012) - trunc(ABS(c.CPT011 + c.CPT012)))*60) < 10
                 THEN
                 trunc(c.CPT011 + c.CPT012)||' h 0'||TRUNC(ROUND((c.CPT011 + c.CPT012 - trunc(c.CPT011 + c.CPT012))*60))||' min' 
                 ELSE
                 trunc(c.CPT011 + c.CPT012)||' h '||TRUNC(ROUND((c.CPT011 + c.CPT012 - trunc(c.CPT011 + c.CPT012))*60))||' min' 
                 END
           END    CUMUL_HEBDO_REAL,
           CASE WHEN ((ABS(c.CPT024) - trunc(ABS(c.CPT024)))*60) < 10
                     THEN
                          DECODE(SIGN(c.CPT024),-1,'- '||trunc(ABS(c.CPT024))||' h 0'||TRUNC(ROUND((ABS(c.CPT024) - trunc(ABS(c.CPT024)))*60))||' min',trunc(ABS(c.CPT024))||' h 0'||TRUNC(ROUND((ABS(c.CPT024) - trunc(ABS(c.CPT024)))*60))||' min')
                 ELSE
                 CASE WHEN ROUND((ABS(c.CPT024) - trunc(ABS(c.CPT024)))*60) = 60
                 THEN
                  DECODE(SIGN(c.CPT024),-1,'- '||trunc(ABS(c.CPT024+1))||' h 00 min',trunc(ABS(c.CPT024+1))||' h 00 min')
                 ELSE
                 DECODE(SIGN(c.CPT024),-1,'- '||trunc(ABS(c.CPT024))||' h '||TRUNC(ROUND((ABS(c.CPT024) - trunc(ABS(c.CPT024)))*60))||' min',trunc(ABS(c.CPT024))||' h '||TRUNC(ROUND((ABS(c.CPT024) - trunc(ABS(c.CPT024)))*60))||' min')
                 END
         END      DEBIT_CREDIT_HEBDO,
         CASE WHEN ((ABS(c.CPT026) - trunc(ABS(c.CPT026)))*60) < 10
                     THEN
                          DECODE(SIGN(c.CPT026),-1,'- '||trunc(ABS(c.CPT026))||' h 0'||TRUNC(ROUND((ABS(c.CPT026) - trunc(ABS(c.CPT024)))*60))||' min',trunc(ABS(c.CPT026))||' h 0'||TRUNC(ROUND((ABS(c.CPT026) - trunc(ABS(c.CPT026)))*60))||' min')
                 ELSE
                 CASE WHEN ROUND((ABS(c.CPT026) - trunc(ABS(c.CPT026)))*60) = 60
                 THEN
                  DECODE(SIGN(c.CPT026),-1,'- '||trunc(ABS(c.CPT026+1))||' h 00 min',trunc(ABS(c.CPT026+1))||' h 00 min')
                 ELSE
                 DECODE(SIGN(c.CPT026),-1,'- '||trunc(ABS(c.CPT026))||' h '||TRUNC(ROUND((ABS(c.CPT026) - trunc(ABS(c.CPT026)))*60))||' min',trunc(ABS(c.CPT026))||' h '||TRUNC(ROUND((ABS(c.CPT026) - trunc(ABS(c.CPT026)))*60))||' min')
                 END
         END      DEBIT_CREDIT_MENS
FROM
         PERSONNEL p,
         COMPTEURJ c
WHERE
          (1=1)
AND       c.NUMMAT = p.NUMMAT
AND       TO_CHAR(c.DATEJ,'YYYYMMDD') = TO_CHAR(SYSDATE,'YYYYMMDD')
AND       (p.MATRICULE = '00763429' )
ORDER BY p.MATRICULE, c.DATEJ

Je souhaite ne faire apparaitre que le debit_credit_mens ou debit_credit_hebdo
en fonction de la valeur de la colonne p.cycle
Est-il possible de ne selectionner que 7 sur 8 colonnes
J'ai essayé sans succès

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
78
79
80
81
82
SELECT  p.MATRICULE,
                  p.NOM,
                  p.PRENOM,
                  c.DATEJ,
           CASE WHEN TRIM(TO_CHAR(c.DATEJ,'DAY')) = 'MONDAY' 
           THEN CASE      WHEN ((ABS(c.CPT005) - trunc(ABS(c.CPT005)))*60) < 10
                 THEN
                    TRUNC(c.CPT005)||' h 0'||
                    TRUNC(ROUND((c.CPT005 - trunc(c.CPT005))*60))||' min'
                 ELSE
                    TRUNC(c.CPT005)||' '||TRUNC(ROUND((c.CPT005 - TRUNC(c.CPT005))*60))
                          ||' min' 
                 END                                                     
           ELSE 
                 CASE     WHEN ((ABS(c.CPT005 + c.CPT006) - trunc(ABS(c.CPT005 + 
                          c.CPT006)))*60) < 10
                 THEN
                          TRUNC(c.CPT005 + c.CPT006)||' h 0'||TRUNC(ROUND(((c.CPT005 + 
                          c.CPT006) - trunc(c.CPT005 + c.CPT006))*60))||' min'
                 ELSE
                          TRUNC(c.CPT005 + c.CPT006)||' h '||TRUNC(ROUND(((c.CPT005 
                          + c.CPT006) - trunc(c.CPT005 + c.CPT006))*60))||' min'
                 END                                                     
           END    CUMUL_HEBDO_COMPTA,
           CASE WHEN TRIM(TO_CHAR(c.DATEJ,'DAY')) = 'MONDAY' 
                     THEN 
                 CASE     WHEN ((ABS(c.CPT011) - trunc(ABS(c.CPT011)))*60) < 10
                 THEN
                 trunc(c.CPT011)||' h 0'||TRUNC(ROUND((c.CPT011 - trunc(c.CPT011))*60))||' min' 
                 ELSE
                 trunc(c.CPT011)||' h '||TRUNC(ROUND((c.CPT011 - trunc(c.CPT011))*60))||' min' 
                 END
                  ELSE 
                 CASE     WHEN ((ABS(c.CPT011 + c.CPT012) - trunc(ABS(c.CPT011 + c.CPT012)))*60) < 10
                 THEN
                 trunc(c.CPT011 + c.CPT012)||' h 0'||TRUNC(ROUND((c.CPT011 + c.CPT012 - trunc(c.CPT011 + c.CPT012))*60))||' min' 
                 ELSE
                 trunc(c.CPT011 + c.CPT012)||' h '||TRUNC(ROUND((c.CPT011 + c.CPT012 - trunc(c.CPT011 + c.CPT012))*60))||' min' 
                 END
           END    CUMUL_HEBDO_REAL,

           if p.cycle like '%TPLEIN%' 

           then
                    
CASE WHEN ((ABS(c.CPT024) - trunc(ABS(c.CPT024)))*60) < 10
                     THEN          
                DECODE(SIGN(c.CPT024),-1,'- '||trunc(ABS(c.CPT024))||' h 0'||TRUNC(ROUND((ABS(c.CPT024) - trunc(ABS(c.CPT024)))*60))||' min',trunc(ABS(c.CPT024))||' h 0'||TRUNC(ROUND((ABS(c.CPT024) - trunc(ABS(c.CPT024)))*60))||' min')
                 ELSE
                 CASE WHEN ROUND((ABS(c.CPT024) - trunc(ABS(c.CPT024)))*60) = 60
                 THEN
                  DECODE(SIGN(c.CPT024),-1,'- '||trunc(ABS(c.CPT024+1))||' h 00 min',trunc(ABS(c.CPT024+1))||' h 00 min')
                 ELSE
                 DECODE(SIGN(c.CPT024),-1,'- '||trunc(ABS(c.CPT024))||' h '||TRUNC(ROUND((ABS(c.CPT024) - trunc(ABS(c.CPT024)))*60))||' min',trunc(ABS(c.CPT024))||' h '||TRUNC(ROUND((ABS(c.CPT024) - trunc(ABS(c.CPT024)))*60))||' min')
                 END
               END      DEBIT_CREDIT_HEBDO

        Else   

      	CASE WHEN ((ABS(c.CPT026) - trunc(ABS(c.CPT026)))*60) < 10
                     THEN
                          DECODE(SIGN(c.CPT026),-1,'- '||trunc(ABS(c.CPT026))||' h 0'||TRUNC(ROUND((ABS(c.CPT026) - trunc(ABS(c.CPT024)))*60))||' min',trunc(ABS(c.CPT026))||' h 0'||TRUNC(ROUND((ABS(c.CPT026) - trunc(ABS(c.CPT026)))*60))||' min')
                 ELSE
                 CASE WHEN ROUND((ABS(c.CPT026) - trunc(ABS(c.CPT026)))*60) = 60
                 THEN
                  DECODE(SIGN(c.CPT026),-1,'- '||trunc(ABS(c.CPT026+1))||' h 00 min',trunc(ABS(c.CPT026+1))||' h 00 min')
                 ELSE
                 DECODE(SIGN(c.CPT026),-1,'- '||trunc(ABS(c.CPT026))||' h '||TRUNC(ROUND((ABS(c.CPT026) - trunc(ABS(c.CPT026)))*60))||' min',trunc(ABS(c.CPT026))||' h '||TRUNC(ROUND((ABS(c.CPT026) - trunc(ABS(c.CPT026)))*60))||' min')
                 END
         	END      DEBIT_CREDIT_MENS

      End if

FROM
         PERSONNEL p,
         COMPTEURJ c
WHERE
          (1=1)
AND       c.NUMMAT = p.NUMMAT
AND       TO_CHAR(c.DATEJ,'YYYYMMDD') = TO_CHAR(SYSDATE,'YYYYMMDD')
AND       (p.MATRICULE = '00763429' )
ORDER BY p.MATRICULE, c.DATEJ

Pourriez-vous m'aider

Merci d'avance

**mnitu** · 18/04/2014, 09h41

Envoyé par velo83

...
Je souhaite ne faire apparaitre que le debit_credit_mens ou debit_credit_hebdo
en fonction de la valeur de la colonne p.cycle
Est-il possible de ne selectionner que 7 sur 8 colonnes
...

En SQL statique non. Mais il est possible d'afficher Null pour la colonne qui ne doit pas apparaitre.

**velo83** · 18/04/2014, 10h23

Merci beaucoup pour votre réponse

Retour conditionnel d'une colonne

SQL Oracle

Discussions similaires

Partager

Partager