Select with when and case

Question

Select with when and case

Navigation

#1 by (2 votes)

1

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 make a comparison of the M.co_proced column as a result of the case (0301040079, 0301060037, etc), but I get an error saying that the PROCEDIMENTO column does not exist (since it is a "nickname I gave it" ).

How do I fix this?

the database is postgre, however I marked mysql because many codes are similar

mysql query postgresql select

asked by anonymous 09.11.2017 / 19:04

1 answer

Loop for in getElementsByClassName How to install Opencv

score 2 · Accepted Answer

The alias of the column will not work in the 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