R and Postgresql - AND/OR and BEETWEN/NOT BEETWEN how to help logic?

Asked

Viewed 35 times

1

I need to get some information from the postgresql database through R .

However, the filters are not downloading what I want. I would like to download only the cnaes of companies with cadastral situation "2" and date "02/2020". As I did, he is pulling other dates.

Following example:

library(RPostgreSQL)
...
Cnae_selecionada <- dbGetQuery(con,"
                   SELECT *
                   FROM cnpj.cnae
                   WHERE 
                      (cnae_fiscal BETWEEN 3700000 AND 3999999)
                   OR (cnae_fiscal BETWEEN 4900000 AND 5399999)
                   OR (cnae_fiscal BETWEEN 5500000 AND 5699999)
                   OR (cnae_fiscal BETWEEN 5800000 AND 6399999)
                   OR (cnae_fiscal BETWEEN 6600000 AND 6699999)
                   OR (cnae_fiscal BETWEEN 6800000 AND 7599999)
                   OR (cnae_fiscal BETWEEN 7700000 AND 8299999)
                   OR (cnae_fiscal BETWEEN 9200000 AND 9699999)
                   OR (cnae_fiscal BETWEEN 160000 AND 169999)
                   OR (cnae_fiscal BETWEEN 230000 AND 239999)
                   OR (cnae_fiscal BETWEEN 4520000 AND 4529999)
                   OR (cnae_fiscal BETWEEN 4543000 AND 4543999)
                   OR (cnae_fiscal BETWEEN 8550000 AND 8559999)
                   OR (cnae_fiscal BETWEEN 8590000 AND 8599999)
                   AND (cnae_fiscal NOT BETWEEN 6912000 AND 6912999)
                   AND (cnae_fiscal NOT BETWEEN 7010000 AND 7019999)
                   AND (cnae_fiscal NOT BETWEEN 8112000 AND 8112999)
                   AND (cnae_fiscal NOT BETWEEN 9400000 AND 9499999)
                   AND situacao_cadastral LIKE '2'AND data LIKE '%02/2020%'
                   ")

I tried some other ways too, but without success.

r postgresql logical-operator
  • 1

    Check the priority of the AND and OR operators. Maybe a parenting is necessary for the correct evaluation. In your case all these Dnas apply only to (cnae_fiscal BETWEEN 8590000 AND 8599999) or should apply to all tracks?

  • what type of field data data?

1 answer

3


You need to separate the OR and conditions, if they are aligned, any OR that is true will ignore the other AND. Use ( ) and separate all Ors:

   SELECT *
   FROM cnpj.cnae
   WHERE 
   (
      (cnae_fiscal BETWEEN 3700000 AND 3999999)
   OR (cnae_fiscal BETWEEN 4900000 AND 5399999)
   OR (cnae_fiscal BETWEEN 5500000 AND 5699999)
   OR (cnae_fiscal BETWEEN 5800000 AND 6399999)
   OR (cnae_fiscal BETWEEN 6600000 AND 6699999)
   OR (cnae_fiscal BETWEEN 6800000 AND 7599999)
   OR (cnae_fiscal BETWEEN 7700000 AND 8299999)
   OR (cnae_fiscal BETWEEN 9200000 AND 9699999)
   OR (cnae_fiscal BETWEEN 160000 AND 169999)
   OR (cnae_fiscal BETWEEN 230000 AND 239999)
   OR (cnae_fiscal BETWEEN 4520000 AND 4529999)
   OR (cnae_fiscal BETWEEN 4543000 AND 4543999)
   OR (cnae_fiscal BETWEEN 8550000 AND 8559999)
   OR (cnae_fiscal BETWEEN 8590000 AND 8599999) 
   )

   AND (cnae_fiscal NOT BETWEEN 6912000 AND 6912999)
   AND (cnae_fiscal NOT BETWEEN 7010000 AND 7019999)
   AND (cnae_fiscal NOT BETWEEN 8112000 AND 8112999)
   AND (cnae_fiscal NOT BETWEEN 9400000 AND 9499999)
   AND situacao_cadastral = '2' AND  data LIKE '%02/2020%'

The date, unless it is in text format, you can use: 

date_trunc('day', data) between '01/02/2020' and '29/02/2020'

  • Perfect ! Thanks ! And the date is in text form, with an extra charactere, including.

Browser other questions tagged r postgresql logical-operator

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