Problem with IN condition in the sub-select

Question

Problem with IN condition in the sub-select

Navigation

#1 by (3 votes)
#2 by (2 votes)

1

I have the following problem;

I'm trying to do a search and include the value of the WHERE principal in a sub-select , however the result is always reported as 0,

Part of the query I'm having difficulties is this:

SELECT 
(SELECT COUNT(*) AS 'Colaboradores' FROM SRA010 WHERE RA_FILIAL = sd3.D3_FILIAL
 AND (RA_SITFOLH NOT IN ('D', 'A') OR RA_DEMISSA  > CAST(YEAR(sd3.D3_EMISSAO) AS VARCHAR)+CAST(REPLICATE('0', 2 - LEN(MONTH(sd3.D3_EMISSAO))) + RTrim(MONTH(sd3.D3_EMISSAO)) AS VARCHAR)+'31')
 AND RA_CC IN (SELECT CTT_CUSTO FROM CTT010 WHERE sd3.D3_CC = CTT_CUSTO)) as Pessoas
FROM SD3010 AS sd3
WHERE sd3.D3_TM = '010'
AND sd3.D3_LOCAL IN ('01','02')
AND sd3.D3_FILIAL = '0301'
AND sd3.D3_CC in ('1350400', '1350403', '1350405', '1350408', '1350409', '1350410', '1350411', '1350412', '1350413', '1350414', '1350415', '1350416', '1350417', '1350418', '1350419', '1350420', '1350421', '1350422', '1350423', '1350424', '1350425', '1350426', '1350427', '1350428', '1350429', '1350430', '1350431', '1350432', '13604', '1360400', '1360401', '1360402', '1360499')
AND sd3.D_E_L_E_T_ <> '*'  
AND sd3.D3_EMISSAO between '20161001' and '20161031'
group by sd3.D3_EMISSAO, sd3.D3_TM, sd3.D3_FILIAL, sd3.D3_CC

I need the sd3.D3_CC condition at the end of query , in in sd3.CC in subselect

Note: There are more subselects that do not interfere with this informed, however, I only informed this so that understanding can be clearer.

Obs2: Other conditions in Where principal, such as the sd3.D3_FILIAL field (in the 1st subselect condition) , work correctly.

Obs3: This query will transform into a view, which will serve as BI analysis, and you can change the sd3.D3_CC option in the filter, that is, the values in the sd3.D3_CC field are not fixed, they are in the query only to simulate BI filters

Update: Removing the AND RA_CC IN (SELECT CTT_CUSTO FROM CTT010 WHERE sd3.D3_CC = CTT_CUSTO) condition in the subselect, it displays results, as shown below (1st Result with the condition quoted above, 2nd Result without the condition quoted above)

sql sql-server query

asked by anonymous 21.11.2016 / 20:14

2 answers

How to use @ in SQL SERVER? Views accessing other controllers

score 3 · Answer 1

If I understand correctly, your SubSelect is making a RA_CC IN (sd3.D3_CC)) , this will be checked one by one for each value of your external query, ie for each line that your select does it will check the value of the D3_CC field that will surely be a single value and not a list.

Change your query to;

SELECT 
(SELECT COUNT(*) AS 'Colaboradores' FROM SRA010 WHERE RA_FILIAL = sd3.D3_FILIAL
     AND (RA_SITFOLH NOT IN ('D', 'A') OR RA_DEMISSA  > CAST(YEAR(sd3.D3_EMISSAO) AS VARCHAR)+CAST(REPLICATE('0', 2 - LEN(MONTH(sd3.D3_EMISSAO))) + RTrim(MONTH(sd3.D3_EMISSAO)) AS VARCHAR)+'31')
     AND RA_CC IN ('1350400', '1350403', '1350405', '1350408', '1350409', '1350410', '1350411', '1350412', '1350413', '1350414', '1350415', '1350416', '1350417', '1350418', '1350419', '1350420', '1350421', '1350422', '1350423', '1350424', '1350425', '1350426', '1350427', '1350428', '1350429', '1350430', '1350431', '1350432', '13604', '1360400', '1360401', '1360402', '1360499')) as Pessoas
FROM SD3010 AS sd3

WHERE sd3.D3_TM = '010'
AND sd3.D3_LOCAL IN ('01','02')
AND sd3.D3_FILIAL = '0301'
AND sd3.D3_CC in ('1350400', '1350403', '1350405', '1350408', '1350409', '1350410', '1350411', '1350412', '1350413', '1350414', '1350415', '1350416', '1350417', '1350418', '1350419', '1350420', '1350421', '1350422', '1350423', '1350424', '1350425', '1350426', '1350427', '1350428', '1350429', '1350430', '1350431', '1350432', '13604', '1360400', '1360401', '1360402', '1360499')
AND sd3.D_E_L_E_T_ <> '*'  


AND sd3.D3_EMISSAO between '20161001' and '20161031'

group by sd3.D3_EMISSAO, sd3.D3_TM, sd3.D3_FILIAL, sd3.D3_CC

score 2 · Answer 2

Below, I only updated the outside of your SQL with two ways to do it. Either way, which you choose, you copy and also use in the IN of the internal query.


SELECT  (SELECT COUNT(*) AS 'Colaboradores' 
           FROM SRA010 
          WHERE RA_FILIAL = sd3.D3_FILIAL
            AND (RA_SITFOLH NOT IN ('D', 'A') OR RA_DEMISSA  > CAST(YEAR(sd3.D3_EMISSAO) AS VARCHAR)+CAST(REPLICATE('0', 2 - LEN(MONTH(sd3.D3_EMISSAO))) + RTrim(MONTH(sd3.D3_EMISSAO)) AS VARCHAR)+'31')
            AND RA_CC IN ('1350400', '1350403', '1350405', '1350408', '1350409', '1350410', '1350411', '1350412', '1350413', '1350414', '1350415', '1350416', '1350417', '1350418', '1350419', '1350420', '1350421', '1350422', '1350423', '1350424', '1350425', '1350426', '1350427', '1350428', '1350429', '1350430', '1350431', '1350432', '13604', '1360400', '1360401', '1360402', '1360499')) as Pessoas
 FROM SD3010 AS sd3
WHERE sd3.D3_TM = '010'
  AND sd3.D3_LOCAL IN ('01','02')
  AND sd3.D3_FILIAL = '0301'
  --AND sd3.D3_CC in ('1350400', '1350403', '1350405', '1350408', '1350409', '1350410', '1350411', '1350412', '1350413', '1350414', '1350415', '1350416', '1350417', '1350418', '1350419', '1350420', '1350421', '1350422', '1350423', '1350424', '1350425', '1350426', '1350427', '1350428', '1350429', '1350430', '1350431', '1350432', '13604', '1360400', '1360401', '1360402', '1360499')
  **--UTILIZANDO IN (NÃO RECOMENDADO)**
  AND sd3.D3_CC in (SELECT D3_CC 
                      FROM tabela1
                      JOIN tabela2 ON(tabela1.id = tabela2.id)
                     WHERE --informar clausulas aqui, onde vai retornar sua lista de D3_CC
           )
**--UTILIZANDO EXISTS  (RECOMENDADO POR QUESTÃO DE PERFORMANCE)

**  AND EXISTS (SELECT TRUE 
                      FROM tabela1
                      JOIN tabela2 ON(tabela1.id = tabela2.id)
                     WHERE sd3.D3_CC = tabela1.D3_CC --informar outras clausulas aqui, onde vai retornar sua lista D3_cc
                     )

  AND sd3.D_E_L_E_T_ <> '*'

  AND sd3.D3_EMISSAO between '20161001' and '20161031'
GROUP BY sd3.D3_EMISSAO, sd3.D3_TM, sd3.D3_FILIAL, sd3.D3_CC

I believe there is a better way to do it, but since I have no data to test the query, it may have been wrong. Anyway, I'll post it below, and you'll modify it for your case.


SELECT  (SELECT COUNT(*) AS 'Colaboradores' 
           FROM SRA010 
          WHERE RA_FILIAL = sd3.D3_FILIAL
            AND (RA_SITFOLH NOT IN ('D', 'A') OR RA_DEMISSA  > CAST(YEAR(sd3.D3_EMISSAO) AS VARCHAR)+CAST(REPLICATE('0', 2 - LEN(MONTH(sd3.D3_EMISSAO))) + RTrim(MONTH(sd3.D3_EMISSAO)) AS VARCHAR)+'31')
            AND RA_CC = foo.D3_CC) as Pessoas
 FROM SD3010 AS sd3
 JOIN (SELECT D3_CC 
                      FROM tabela1
                      JOIN tabela2 ON(tabela1.id = tabela2.id)
                     WHERE --informar clausulas aqui, onde vai retornar sua lista de D3_CC
                     ) AS foo ON (foo.D3_CC = sd3.d3_cc)
WHERE sd3.D3_TM = '010'
  AND sd3.D3_LOCAL IN ('01','02')
  AND sd3.D3_FILIAL = '0301'

  AND sd3.D_E_L_E_T_ <> '*'

  AND sd3.D3_EMISSAO between '20161001' and '20161031'
GROUP BY sd3.D3_EMISSAO, sd3.D3_TM, sd3.D3_FILIAL, sd3.D3_CC

Any questions, I'm available.