Select com when e case

Question

I have the following select query:

select
F.nu_ine INE,
to_char(D.dt_ficha, 'YYYYMM') as CMP,
I.co_cbo_2002 CBO,
CASE 
WHEN (B.tp_atend = 1 or B.tp_atend = 2 or B.tp_atend = 5) and I.co_cbo_2002 != '225142' THEN '0301010030'
WHEN (B.tp_atend = 1 or B.tp_atend = 2 or B.tp_atend = 5) and I.co_cbo_2002 = '225142' THEN '0301010064'
WHEN B.tp_atend = 4 THEN '0301040079'
WHEN B.tp_atend = 6 THEN '0301060037'
ELSE B.tp_atend
END PROCEDIMENTO,
DATE_PART('year', D.dt_ficha) - DATE_PART('year', B.dt_nascimento) IDADE,
H.no_pessoa_fisica,
F.no_equipe,
I.no_cbo,
M.no_proced
from
tb_cds_atend_individual B,
tb_cds_ficha_atend_individual D,
tb_equipe F,
tb_cds_prof G,
tb_pessoa_fisica H,
tb_cbo I,
tb_proced M
where F.nu_ine = G.nu_ine
and G.nu_cns = H.nu_cns
and D.co_cds_prof = G.co_seq_cds_prof
and G.nu_cbo_2002 = I.co_cbo_2002
and B.co_cds_ficha_atend_individual = D.co_seq_cds_ficha_atend_indivdl
and D.dt_ficha >= '2017-10-01' and D.dt_ficha <= '2017-10-30'
and M.co_proced = PROCEDIMENTO <<<<<ESSA LINHA
order by B.tp_atend

I need to do a column comparison M.co_proced as a result of case (0301040079, 0301060037, etc.), but I get an error saying that column PROCEDIMENTO does not exist (as it is a "nickname I gave her").

How do I fix it?

the database is postgre, but marki mysql because many codes are similar

Answer 1

The column alias will not work in Where, you have to put the expression also in the syntax.

select
F.nu_ine INE,
to_char(D.dt_ficha, 'YYYYMM') as CMP,
I.co_cbo_2002 CBO,
(CASE 
WHEN (B.tp_atend = 1 or B.tp_atend = 2 or B.tp_atend = 5) and I.co_cbo_2002 != '225142' THEN '0301010030'
WHEN (B.tp_atend = 1 or B.tp_atend = 2 or B.tp_atend = 5) and I.co_cbo_2002 = '225142' THEN '0301010064'
WHEN B.tp_atend = 4 THEN '0301040079'
WHEN B.tp_atend = 6 THEN '0301060037'
ELSE B.tp_atend
END) PROCEDIMENTO,
DATE_PART('year', D.dt_ficha) - DATE_PART('year', B.dt_nascimento) IDADE,
H.no_pessoa_fisica,
F.no_equipe,
I.no_cbo,
M.no_proced
from
tb_cds_atend_individual B,
tb_cds_ficha_atend_individual D,
tb_equipe F,
tb_cds_prof G,
tb_pessoa_fisica H,
tb_cbo I,
tb_proced M
where F.nu_ine = G.nu_ine
and G.nu_cns = H.nu_cns
and D.co_cds_prof = G.co_seq_cds_prof
and G.nu_cbo_2002 = I.co_cbo_2002
and B.co_cds_ficha_atend_individual = D.co_seq_cds_ficha_atend_indivdl
and D.dt_ficha >= '2017-10-01' and D.dt_ficha <= '2017-10-30'
and M.co_proced = (CASE 
WHEN (B.tp_atend = 1 or B.tp_atend = 2 or B.tp_atend = 5) and I.co_cbo_2002 != '225142' THEN '0301010030'
WHEN (B.tp_atend = 1 or B.tp_atend = 2 or B.tp_atend = 5) and I.co_cbo_2002 = '225142' THEN '0301010064'
WHEN B.tp_atend = 4 THEN '0301040079'
WHEN B.tp_atend = 6 THEN '0301060037'
ELSE B.tp_atend
END)
order by B.tp_atend