Problem with IN condition in sub-select

Asked

Viewed 140 times

1

I have the following problem;

I’m trying to do some research and include the value of WHERE major in a sub-select, but the result is always reported as 0, and there are records.

Part of query that I’m in trouble 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 condition of Sd3.D3_CC at the end of query, in the WHERE, go to the WHERE in Sd3.CC in subselect

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

Obs2: Other conditions in Where main, as the field Sd3.D3_FILIAL (in subselect condition 1), work properly.

Obs3: This query will turn into a view, q will serve as BI analysis, and the option Sd3.D3_CC can be changed in the filter, that is, the values of the field Sd3.D3_CC are not fixed, they are in the query only to simulate the BI filters

Update: Removing the condition AND RA_CC IN (SELECT CTT_CUSTO FROM CTT010 WHERE Sd3.D3_CC = CTT_CUSTO) in subselect, it displays results as shown below (1º Result with the above mentioned condition, 2º Result without the above mentioned condition) inserir a descrição da imagem aqui

sql sql-server query

  • Without the table structure and some data to test is very difficult. Add that to the question.

  • If you have a query that returns the values that are in the in, you can use as subquery in the WHERE of the external and internal query. Otherwise, you’ll have to repeat the in on both places.

  • 1

    Another thing: Not using the clause "IN" for performance reasons. Always seek to use the clause NOT EXISTS OR EXISTS.

  • @Cristianobombazar, in the above case the IN is more indicated, it has a list and not a select from another table.

  • 1

    @Marconciliosouza I ended up not finishing my comment. I spoke about EXISTS if the values can be obtained by sub-query. Sorry.

  • @Cristianobombazar, no problem.

  • @Thiagoalessandro, the construction "RA_CC IN (SELECT CTT_CUSTO FROM CTT010 WHERE Sd3.D3_CC = CTT_CUSTO))" seemed strange to me. In this case, IN has the EXISTS function?

  • @Josédiz This is just the IN...the idea would be to fetch RA_CC within this set of data brought by SELECT CTT_CUSTO FROM........

Show 3 more comments

2 answers

3

If I understand correctly, your Subselect is making a RA_CC IN (sd3.D3_CC)), this will be checked ONE to ONE for each value of your external query, ie for each line that your select do it will check the value of the field D3_CC which will certainly 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

  • So it works, but these values of Sd3.D3_CC are not fixed. I put them only to simulate results. They will be changed as per the condition you provide. That is why you would need to pass the IN values (in the main Where) to subselect. And it can be more than one value to be sent to subselect, so the IN

  • @Thiagoalessandro What query do you use to return these Sd3.D3_CC values?

  • @Cristianobombazar You are coming from an INNER JOIN... INNER JOIN CTT010 AS ctt ON (sd3_sub1.D3_CC = ctt.CTT_CUSTO)

  • @Thiagoalessandro, in case you have to make a select inside your IN(select field table Where sd3_sub1.D3_CC = ctt.CTT_CUSTO), something like this

  • @Cristianobombazar In subselect? or Where main IN?

  • @Thiagoalessandro O from Where...

  • @Thiagoalessandro, what is your database? you were able to solve the problem?

  • @Cristianobombazar use SQL Server...I tried as you suggested, but keeps returning the count to 0. If I use a specific value of the condition in IN (value '1360401', for example), putting as fixed value, it returns the right amount of records. Last query change I made was in this part: AND RA_CC IN (SELECT CTT_CUSTO FROM CTT010 WHERE Sd3.D3_CC = CT_CUSTO)) as People if change to AND RA_CC IN ('1360401')) as People it brings correctly

  • @Thiagoalessandro Posts updated SQL. Edits your question...

  • @Cristianobombazar Updated

Show 5 more comments

2

Below, I only updated the outside of your SQL with two ways to do it. Anyway, which you choose, you copy and use also 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 don’t have any data to test the query, it may have been wrong. Anyway, I’ll post below, and you modify 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.

  • I understood its logic...but I could not put the condition up there pq the query will turn into a view, q will serve as BI analysis, and can change the option Sd3.D3_CC in the filter

  • @Thiagoalessandro The third example is just that. Not such putting the condition on top. Only linking the results of the table nicknamed "foo", which is only the Sd3.D3_CC.

  • This "table2" would be the table that returns the values of Sd3.D3_CC?

  • @Thiagoalessandro That SQL in there is just an example of how you can do it. The important thing is that within that query there, return the values of Sd3.D3_CC. That’s all.

  • It still brings 0. I checked the Join select that you suggested as an example, and is bringing the Sd3.D3_CC records correctly. Did I do something wrong? Follow query http://pastebin.com/3aD1GVFJ

  • @Thiagoalessandro Removes the Sd3.D3_CC in (...) of the external consultation. And in the internal consultation, there AND RA_CC = foo.D3_CC , this relationship is really right?

  • In this database, there are 2 tables with this information. I changed the table in JOIN, as a precaution, but it continues with the same result. the relationship is correct, because they are two keys. following query with the change to the other table and without the condition Sd3.D3_CC: http://pastebin.com/edxsBEtY

Show 2 more comments

Browser other questions tagged sql sql-server query

You are not signed in. Login or sign up in order to post.