Jump to content
Fórum Script Brasil
  • 0

Erro na Sentença Subquery: 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 express


Question

Bom dia, 

Estou apanhando desde ontem em uma sentença Quando eu faço o select fora da sentença sozinho, os dados são demonstrados corretamente, porém, na subquery 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 outer reference must be the only column referenced in the expression.

Gostaria de uma ajuda por gentileza, não tenho mais o que fazer. Sublinhei onde ocorre o erro.

Segue o Select:

 

SELECT

   DISTINCT FUN.IDFUNCIONARIO, FER.CHAPA, FER.INICIOPERAQUIS, FER.FIMPERAQUIS, 

   CASE WHEN FU.CODSITUACAO IN ('A', 'E') 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 PPP.FIMPERAQUIS = FER.FIMPERAQUIS GROUP BY PPP.CHAPA) WHEN FU.CODSITUACAO IN ('C', 'D') THEN 0 ELSE FER.SALDO END VL_SALDO_FERIAS,

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

Link to post
Share on other sites

1 answer to this question

Recommended Posts

  • 0

No segundo CASE tira o "AND FER.PERIODOABERTO = 1"

SELECT DISTINCT
	FUN.IDFUNCIONARIO,
	FER.CHAPA,
	FER.INICIOPERAQUIS,
	FER.FIMPERAQUIS, 
	CASE
		WHEN FU.CODSITUACAO IN ('A', 'E') AND FER.PERIODOABERTO = 1
			THEN (DATEDIFF(DAY, FER.INICIOPERAQUIS,GETDATE())/12)
				- 
			(SELECT
				SUM(
					CASE
					WHEN PPP.NRODIASFERIAS IS NOT NULL THEN PPP.NRODIASFERIAS
					ELSE 0 END
				)
			FROM PFUFERIASPER PPP
			WHERE FER.CODCOLIGADA = PPP.CODCOLIGADA
			AND FER.CHAPA = PPP.CHAPA
			AND PPP.FIMPERAQUIS = FER.FIMPERAQUIS
			GROUP BY PPP.CHAPA)
		WHEN FU.CODSITUACAO IN ('C', 'D') THEN 0
		ELSE FER.SALDO
	END AS VL_SALDO_FERIAS,
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

 

Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Answer this question...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Cloud Computing


  • Forum Statistics

    • Total Topics
      148687
    • Total Posts
      644520
×
×
  • Create New...