CODEWITHSUNDEEP
sqlfilterinteger
Maria Mittelbach
Maria Mittelbach

Reputation: 125

Problem with filter a integer variable in SQL

I am trying to filter the data I have only for years between 2007 and 20011. The variable I want to filter is an integerano. The query is running but it is not filtering the data in this variable, but it is working to filter the other variable (conta).

SELECT 
    `ano`, `sigla_uf`, `id_municipio`, 
    MAX(CASE WHEN (`conta` = 'Rec Tributária') THEN (`valor`) END) AS `tax_fees`, 
    MAX(CASE WHEN (`conta` = 'Impostos') THEN (`valor`) END) AS `tax`, 
    MAX(CASE WHEN (`conta` = 'IPTU') THEN (`valor`) END) AS `iptu`, 
    MAX(CASE WHEN (`conta` = 'ITBI') THEN (`valor`) END) AS `itbi`, 
    MAX(CASE WHEN (`conta` = 'ISSQN') THEN (`valor`) END) AS `issqn`
FROM
    (SELECT 
         `ano`, `sigla_uf`, `id_municipio`, `conta`, `valor`
     FROM 
         (SELECT 
              CAST(`ano` AS INT) AS `ano`, `sigla_uf`, `id_municipio`, 
              `estagio`, `portaria`, `conta`, `estagio_bd`, 
              `id_conta_bd`, `conta_bd`, `valor`
          FROM 
              `basedosdados.br_me_siconfi.municipio_receitas_orcamentarias`)
     WHERE
         (`conta` = 'Rec Tributária' OR `conta` = 'Impostos' OR 
          `conta` = 'IPTU' OR `conta` = 'ITBI' OR `conta` = 'ISSQN' 
          AND ano >= 2007 AND ano < 2012))
GROUP BY 
    `ano`, `sigla_uf`, `id_municipio`

Upvotes: 1

Views: 336

Answers (1)

jarlh
jarlh

Reputation: 44766

AND has higher precedence than OR.

This means that your:

 WHERE
     (`conta` = 'Rec Tributária' OR `conta` = 'Impostos' OR 
      `conta` = 'IPTU' OR `conta` = 'ITBI' OR `conta` = 'ISSQN' 
      AND ano >= 2007 AND ano < 2012))

actually is read as:

 WHERE
     (`conta` = 'Rec Tributária' OR `conta` = 'Impostos' OR 
      `conta` = 'IPTU' OR `conta` = 'ITBI' OR
       (`conta` = 'ISSQN' AND ano >= 2007 AND ano < 2012))

Instead do:

 WHERE
     (`conta` = 'Rec Tributária' OR `conta` = 'Impostos' OR 
      `conta` = 'IPTU' OR `conta` = 'ITBI' OR `conta` = 'ISSQN')
  AND ano >= 2007 AND ano < 2012

Which also can be written as:

 WHERE `conta` IN ('Rec Tributária', 'Impostos', 'IPTU', 'ITBI', 'ISSQN') 
   AND ano >= 2007 AND ano < 2012

Upvotes: 2

Related Questions