Replace a fixed CASE value with a SELECT and JOIN?

Question

SELECT
DISTINCT P.CD_PACIENTE
, P.NM_PACIENTE
, DECODE (P.TP_SEXO, 'M', 'MASCULINO',
                     'F', 'FEMININO', 
                     'I', 'INDEFINIDO') SEXO                         
, TRUNC(P.DT_NASCIMENTO) DT_NASCIMENTO
, (SELECT FN_IDADE (P.DT_NASCIMENTO, 'a A / m M / d D' ) FROM DUAL) IDADE_PACIENTE
, A.CD_ATENDIMENTO
, A.DT_ATENDIMENTO
, (SELECT FN_IDADE (A.DT_ATENDIMENTO, 'a A / m M / d D' ) FROM DUAL) DIAS_INTERNADO
, PE.CD_PRESTADOR --PRESTADOR DO ATENDIMENTO
, PE.NM_PRESTADOR
, L.CD_LEITO --LEITO DO PACIENTE
, L.DS_LEITO
, UI.CD_UNID_INT                             CD_UNIDADE_INTERNACAO--UNIDADE DE INTERNAÇÃO VINCULADA A INTERNAÇÃO
, UI.DS_UNID_INT                             UNIDADE_INTERNACAO
, (SELECT S.NM_SETOR FROM SETOR S WHERE UI.CD_SETOR = S.CD_SETOR) AS SETOR_UNIDADE_INTERNACAO --SETOR DA UNIDADE DE INTERNAÇÃO    
, E.CD_ESPECIALID                            CD_ESPEC_ATENDIMENTO--ESPECIALIDADE VINCULADA AO MÉDICO PRESTADOR DO ATENDIMENTO
, E.DS_ESPECIALID                            ESPEC_ATENDIMENTO
, (CASE WHEN UI.CD_UNID_INT = 63 AND E.CD_ESPECIALID IN (64, 60, 82) THEN 1 ELSE 0 END) STATUS   
FROM LEITO L
JOIN ATENDIME A     ON A.CD_LEITO         = L.CD_LEITO
JOIN UNID_INT UI    ON UI.CD_UNID_INT     = L.CD_UNID_INT
JOIN SETOR S        ON S.CD_SETOR         = UI.CD_SETOR
JOIN PACIENTE P     ON P.CD_PACIENTE      = A.CD_PACIENTE    
JOIN ESP_MED EM     ON EM.CD_PRESTADOR    = A.CD_PRESTADOR --ESPECIALIDADE DO ATENDIMENTO
JOIN ESPECIALID E   ON E.CD_ESPECIALID    = A.CD_ESPECIALID --SE COLOCAR P.CD_PRESTADOR IRÁ RETORNAR MAIS RESULTADOS, POIS UM PRESTADOR PODE TER MAIS DE UMA ESPECIALIDADE.
JOIN PRESTADOR PE   ON PE.CD_PRESTADOR    = A.CD_PRESTADOR       
WHERE A.TP_ATENDIMENTO = 'I'
AND A.CD_MOT_ALT IS NULL

I wonder if I can get instead of this no case operation: UI.CD_UNID_INT = 63 OR E.CD_ESPECIALID IN (64, 60, 82)

Replace it with some select with Join so I don’t plaster the code and all did that is changed specialty or sector have to change the code?

Instead of doing this: (CASE WHEN (UI.CD_UNID_INT = 63 AND E.CD_ESPECIALID IN (64, 60, 82)) OR (UI.CD_UNID_INT = 64 AND E.CD_ESPECIALID IN (800, 652, 802)) OR (UI.CD_UNID_INT = 68 AND E.CD_ESPECIALID IN (800, 652, 802)) THEN 1 0 END) STATUS else else

Answer 1

What you want is possible, yes; even you already make a subconsultation.

SELECT
DISTINCT P.CD_PACIENTE
-- demais campos
, (CASE WHEN UI.CD_UNID_INT = (SELECT MAX(S_2.CODIGO) from SETOR S_2 where S_2.DESCRICAO = 'TESTE_SETOR') AND E.CD_ESPECIALID IN (SELECT FROM ESPECIALID E_2 WHERE E_2.DESCRICAO LIKE '%TESTE_ESPECIALIDADE%') THEN 1 ELSE 0 END) STATUS
FROM LEITO L
JOIN ATENDIME A     ON A.CD_LEITO         = L.CD_LEITO
JOIN UNID_INT UI    ON UI.CD_UNID_INT     = L.CD_UNID_INT
JOIN SETOR S        ON S.CD_SETOR         = UI.CD_SETOR
JOIN PACIENTE P     ON P.CD_PACIENTE      = A.CD_PACIENTE    
JOIN ESP_MED EM     ON EM.CD_PRESTADOR    = A.CD_PRESTADOR --ESPECIALIDADE DO ATENDIMENTO
JOIN ESPECIALID E   ON E.CD_ESPECIALID    = A.CD_ESPECIALID --SE COLOCAR P.CD_PRESTADOR IRÁ RETORNAR MAIS RESULTADOS, POIS UM PRESTADOR PODE TER MAIS DE UMA ESPECIALIDADE.
JOIN PRESTADOR PE   ON PE.CD_PRESTADOR    = A.CD_PRESTADOR       
WHERE A.TP_ATENDIMENTO = 'I'
AND A.CD_MOT_ALT IS NULL

The code would look something like this (note the validations of subselect, which columns you need to validate/bring).

Answer 2

To answer my question, there is no way to select.

There is no relationship between the specialty and the hospital, it is linked according to what the user informs, without restrictions, there is no relationship between the tables.