Bonjour,
ci-apres le sql
Je souhaite ne faire apparaitre que le debit_credit_mens ou debit_credit_hebdo
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
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
Partager