0
I am new to SQL and I have the following error in my query: "The sub-query returned more than 1 value. This is not allowed when the subconsulta follows a =, != , <, <= , >, >= or when it is used as an expression".
The query is this:
SELECT distinct
(PP.ProjDesc),
U1.UsuRazaoSocial,
C.CompDesc QtdeCNPJs,
U1.UsuUF,
T.TarID,
U.UsuNome 'Responsável',
CONVERT(DATE, PP.DataInicio, 103) Data_Início,
CONVERT(DATE, PP.DataPrevLiberacao,103) Data_Fim,
DATEDIFF(DAY,PP.DataInicio ,GETDATE()) Dias_Decorridos,
SLA_Projeto =
CASE WHEN PP.DataPrevLiberacao >= GETDATE() THEN
'Dentro do Prazo'
ELSE
'FORA do Prazo'
END,
(SELECT
COUNT(T.TarID)
FROM Projetos P
INNER JOIN Tarefa T ON P.ProjID = T.ProjID
WHERE P.ProjID = PP.ProjID) QtdeTarefas,
(SELECT
COUNT(T.TarID)
FROM Projetos P
INNER JOIN Tarefa T ON P.ProjID = T.ProjID
WHERE T.TarStatus = 9
AND P.ProjID = PP.ProjID) QtdeConcluidas,
(SELECT
Faturado =
CASE T1.TarEstagioID WHEN 112 THEN
'SIM'
ELSE
'NÃO'
END
FROM Projetos P
INNER JOIN Tarefa T1 ON P.ProjID = T1.ProjID
WHERE T1.TarStatus = 9
AND T1.TarTitulo = 'Treinamento Realizado - Emitir Boleto Setup'
AND P.ProjID = PP.ProjID
AND T1.TarTipID = 674) Faturado,
(SELECT
COUNT(T.TarID)
FROM Projetos P
INNER JOIN Tarefa T ON P.ProjID = T.ProjID
WHERE T.TarStatus = 1
AND P.ProjID = PP.ProjID) QtdeAtendimento,
(SELECT
COUNT(T.TarID)
FROM Projetos P
INNER JOIN Tarefa T ON P.ProjID = T.ProjID
WHERE T.TarStatus NOT IN (1,9)
AND P.ProjID = PP.ProjID) QtdePendentes,
(SELECT
COUNT(T.TarID)
FROM Projetos P
INNER JOIN Tarefa T ON P.ProjID = T.ProjID
WHERE T.TarStatus = 5
AND T.TarEstagioID IN (160)
AND P.ProjID = PP.ProjID) QtdePendentesCliente,
(SELECT
COUNT(T.TarID)
FROM Projetos P
INNER JOIN Tarefa T ON P.ProjID = T.ProjID
WHERE T.TarStatus = 0
AND T.TarEstagioID IN (8)
AND P.ProjID = PP.ProjID) QtdePendentesOperadora,
(SELECT
COUNT(T.TarID)
FROM Projetos P
INNER JOIN Tarefa T ON P.ProjID = T.ProjID
WHERE T.TarStatus = 0
AND T.TarEstagioID IN (114)
AND P.ProjID = PP.ProjID) QtdePendentesBoavista
FROM PROJETOS PP
INNER JOIN Tarefa T ON PP.ProjID = T.ProjID
INNER JOIN Usuario U ON T.UsuIDResponsavel = U.UsuID
INNER JOIN Usuario U1 ON T.UsuIDCliente = U1.UsuID
LEFT JOIN Complemento C ON C.UsuID = T.UsuIDCliente and C.CompID = 1
WHERE T.Macprocid = 33
AND T.TarTipID = 866
the problem seems to be in that subselect
(SELECT Faturado = CASE T1.TarEstagioID WHEN 112 THEN 'SIM' ELSE 'NÃO' END
FROM Projetos P
INNER JOIN Tarefa T1 ON P.ProjID = T1.ProjID
WHERE T1.TarStatus = 9
AND T1.TarTitulo = 'Treinamento Realizado - Emitir Boleto Setup'
AND P.ProjID = PP.ProjID
AND T1.TarTipID = 674) Faturado,that can return more than one line– Rovann Linhalis
if I run only this sub-volume, it normally generates
– Renan Bessa
By the way, apologies, it really does occur an error when I run this sub-concession. Error is: Msg 4104, Level 16, State 1, Line 6 Multi-part identifier "PP.Projid" could not be associated.
– Renan Bessa
it is not the query that is wrong, and you cannot execute it only because it depends on the values of the higher queries. The problem is that it can return more than one line, which cannot happen in sub-consultations. try to put it like this:
– Rovann Linhalis
(SELECT TOP 1 Faturado = CASE T1.TarEstagioID WHEN 112 THEN 'SIM' ELSE 'NÃO' END FROM Projetos P INNER JOIN Tarefa T1 ON P.ProjID = T1.ProjID WHERE T1.TarStatus = 9 AND T1.TarTitulo = 'Treinamento Realizado - Emitir Boleto Setup' AND P.ProjID = PP.ProjID AND T1.TarTipID = 674) Faturado,– Rovann Linhalis
@Renanbessa: What is the result that the query should return? // I realize that there is project and that a project can be composed of one or more tasks. And that it is necessary to obtain summary information about each project. But there is a mixture of synthetic report (project accumulators, for example), with analytical (individual information of each task). // What is the rule for calculating the value of the Invoiced column? The information must be generated per project or per project/task?
– José Diz
Has any response helped solve the problem and can address similar questions from other users? If so, make sure to mark the answer as accepted. To do this just click on the left side of it (below the indicator of up and down votes).
– Sorack