June 4, 2019 at 1:22 pm
Bom dia,
Estou apanhando desde ontem em uma sentenca. Quando eu faco o select sozinho da subquery os dados sao demonstrados corretamente, porem, na subselect me ocorre esse erro:
Multiple columns are specified in an aggregated expression containing an outer reference. If an expression being aggregated contains an outer reference, then that outer reference must be the only column referenced in the expression.
Gostaria de uma ajuda por gentileza, nao tenho mais o que fazer.
Segue o select:
SELECT
id_funcionario, CHAPA, dh_inicio_periodo, dh_fim_periodo, vl_saldo_ferias, DIAS_FERIAS, fg_ferias_perdidas, nu_dias_falta, CASE WHEN vl_saldo_ferias BETWEEN 1 AND 29 THEN 'Incompleto' WHEN vl_saldo_ferias >= 30 THEN 'Aberto' ELSE 'Fechado' END sg_status_periodo
FROM
(SELECT DISTINCT FUN.ID_FUNCIONARIO id_funcionario,FER.CHAPA, CONVERT(VARCHAR(10), FER.INICIOPERAQUIS, 103) as dh_inicio_periodo, CONVERT(VARCHAR(10), FER.FIMPERAQUIS, 103) as dh_fim_periodo,
CASE WHEN FU.CODSITUACAO IN ('A','E','F','G','L','M','N','O','P','Q','R','S','T','V','W','X','Y') AND FER.PERIODOABERTO = 1 THEN (DATEDIFF(day,FER.INICIOPERAQUIS,GETDATE ())/12) -(SELECT SUM(CASE WHEN PPP.NRODIASFERIAS IS NOT NULL AND FER.PERIODOABERTO = 1 THEN PPP.NRODIASFERIAS ELSE 0 END) FROM PFUFERIASPER PPP WHERE FER.CODCOLIGADA = PPP.CODCOLIGADA AND FER.CHAPA = PPP.CHAPA AND FER.FIMPERAQUIS = PPP.FIMPERAQUIS GROUP BY PPP.CHAPA)
WHEN FU.CODSITUACAO IN ('C','D','I','K','U','Z') THEN 0
ELSE FER.SALDO END vl_saldo_ferias,
DATEDIFF(day,FER.INICIOPERAQUIS,FER.FIMPERAQUIS) DIAS_FERIAS,
'No' as fg_ferias_perdidas, PER.FALTAS as nu_dias_falta
FROM
PFUFERIAS FER
LEFT OUTER JOIN FOL_FUNCIONARIOS FUN ON FUN.CHAPA = FER.CHAPA
LEFT OUTER JOIN PFUFERIASPER PER ON PER.CODCOLIGADA = FER.CODCOLIGADA AND PER.CHAPA = FER.CHAPA AND PER.FIMPERAQUIS = FER.FIMPERAQUIS
LEFT OUTER JOIN PFUNC FU ON FU.CODCOLIGADA = FER.CODCOLIGADA AND FU.CHAPA = FER.CHAPA
WHERE FU.CODSITUACAO = ('A')
)
RESULT1
ORDER
BY CHAPA,dh_inicio_periodo
June 4, 2019 at 1:27 pm
The error is telling you the problem here. You have the subquery below:
SELECT SUM(CASE WHEN PPP.NRODIASFERIAS IS NOT NULL AND FER.PERIODOABERTO = 1 THEN PPP.NRODIASFERIAS ELSE 0 END)
FROM PFUFERIASPER PPP
WHERE FER.CODCOLIGADA = PPP.CODCOLIGADA
AND FER.CHAPA = PPP.CHAPA
AND FER.FIMPERAQUIS = PPP.FIMPERAQUIS
GROUP BY PPP.CHAPA
FER is an outer reference and is contained in an aggregate function. Unfortunately, the rest of your post is in a different language, and SSC is an English Speaking Community. This is a guess as a results, however, most likely you need to replace the above subquery with this:
SELECT SUM(PPP.NRODIASFERIAS)
FROM PFUFERIASPER PPP
WHERE FER.CODCOLIGADA = PPP.CODCOLIGADA
AND FER.CHAPA = PPP.CHAPA
AND FER.FIMPERAQUIS = PPP.FIMPERAQUIS
AND FER.PERIODOABERTO = 1
GROUP BY PPP.CHAPA
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
June 4, 2019 at 4:43 pm
Entendi Thom,
Não há necessidade de fazer um subselect se a tabela já existe na sentença né.
Muito obrigado pelo seu feedback, me ajudou muito.
Abraço,
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply