Menu de navegação

[SamantaSParticipante]

PESSOAL,TENHO A SEGUINTE QUERY ABAIXO:

SELECT F.CHAPA,
F.SALARIO,
A.DATA,
CASE WHEN MAX(A.BATIDA) >'1065' THEN '17:45'
WHEN MAX(A.BATIDA) <='1065' THEN TO_CHAR(TRUNC(MOD(MAX(A.BATIDA),3600)/60),'09') || ':' || TO_CHAR(MOD(MOD(MAX(A.BATIDA),3600),60),'09') END AS HR_NORMAL, CASE WHEN MAX(BATIDA) <= '1065' THEN '0' WHEN MAX((A.BATIDA)-1066) >= '120' THEN '2'
WHEN MAX(A.BATIDA) >=' 1066' AND MAX(A.BATIDA) <= '1187' THEN TO_CHAR(TRUNC(MOD(MAX((A.BATIDA)-1050),3600)/60),'09') || ':' || TO_CHAR(MOD(MOD(MAX((A.BATIDA)-1050),3600),60),'09') ELSE '0' END AS HR_EXTRA_60, CASE WHEN MAX(A.BATIDA) BETWEEN '1188' AND '1319' THEN TO_CHAR(TRUNC(MOD(MAX((A.BATIDA)-1172),3600)/60),'09') || ':' || TO_CHAR(MOD(MOD(MAX((A.BATIDA)-1172),3600),60),'09') WHEN MAX(A.BATIDA) >= '1320' THEN '2'
ELSE '0'
END AS HR_EXTRA_80,

CASE WHEN MAX(A.BATIDA) BETWEEN '1320' AND '1440' THEN TO_CHAR(TRUNC(MOD((MAX(A.BATIDA)-1305),3600)/60),'09') || ':' || TO_CHAR(MOD(MOD((MAX(A.BATIDA)-1305),3600),60),'09')
ELSE '0'
END AS NOTURNO,

CASE WHEN MAX(A.BATIDA) >= '1066' THEN TO_CHAR(TRUNC(MOD(MAX((A.BATIDA)-1050),3600)/60),'09') || ':' || TO_CHAR(MOD(MOD(MAX((A.BATIDA)-1050),3600),60),'09')
ELSE '0'
END AS TOTAL,

CASE WHEN MAX(A.BATIDA) >= '1066' AND MAX(A.BATIDA)<='1187' THEN TO_CHAR(CAST((MAX(A.BATIDA)-1050)/60 AS DECIMAL(10,2))) WHEN MAX(A.BATIDA) >= '1188' AND MAX(A.BATIDA)<='1319' THEN TO_CHAR(CAST(MAX((A.BATIDA)-1172+120)/60 AS DECIMAL (10,2))) WHEN MAX(A.BATIDA) >= '1320' AND MAX(A.BATIDA)<='1440' THEN TO_CHAR(CAST(MAX((A.BATIDA)-1305+440)/60 AS DECIMAL(10,2))) END AS CONVERSAO, CASE WHEN MAX(A.BATIDA) >= '1065' AND MAX(A.BATIDA)<= '1187' THEN ((TRUNC((F.SALARIO) /220)*(16/10)) * TO_CHAR(TRUNC((MAX(A.BATIDA)-1065)/60),'999D99')) WHEN MAX(A.BATIDA) >= '1188' AND MAX(A.BATIDA)<= '1319' THEN ((TRUNC((F.SALARIO)/220)*(18/10)) * TO_CHAR(TRUNC((MAX(A.BATIDA)-1065)/60),'999D99')) END AS VALOR FROM PFUNC F JOIN ABATFUN A ON F.CODCOLIGADA = A.CODCOLIGADA AND F.CHAPA = A.CHAPA WHERE F.CODCOLIGADA = '1100' AND A.DATA = '28/04/2011' AND F.CODSITUACAO <>'D'

GROUP BY F.SALARIO,
F.CHAPA,
A.DATA

ORDER BY F.CHAPA

PRECISO CONVERTER PARA DECIMAL, DEIXANDO 2 CASAS DECIMAISA PÓS A VIRGULA, E ANTES DA VIRGULA, SE NAUM TIVER NENHUM NUMERO, DEIXAR O ‘0’, COM A QUERY Q CRIEI,QND MANDO RODAR, SÓ APARECE UM NUMERO DPS DA VIRGULA.
EX.: O RESULDADO ‘0,80’ SAI SOMENTE ‘,8’. ALGUÉM ME AJUDA, PLEASE?
OBRIGADA

[anrcardosoParticipante]

Tente implementar o script abaixo, deve resolver:

SELECT TO_CHAR(0.8,’FM999999999990D00′,
‘NLS_NUMERIC_CHARACTERS = ”,.”’) valor
FROM DUAL

---

Anderson Cardoso
Desenvolvedor Oracle

[Autor]

Posts