SUBSELECT - POSSIBILITY OF IMPROVEMENT

Question

I would like some guidance on a query. It is working, but the current script generates a very high reading demand in the bank due to a case comparing a table data, with another through a subselect

select distinct
(T.COD),
T.NOME,
T.BRAS,
R.NOME as REF_NOME,
R.cod_tuss_brasindice AS TUSS_BRASSINDIC,

case
    when R.cod_tuss_brasindice <> (
        SELECT fcr.cod
        FROM fcrefund fcr
            left JOIN tbrefmemtuss tuss on fcr.cod = tuss.cod_fcrefund
            and fcr.tab = tuss.tab_fcrefund
            and fcr.classif = tuss.classif_fcrefund
        where fcr.cod = b.codref
            and fcr.tab = b.tab
            and fcr.classif = b.classif
            and fcr.data in (
                select max(FCREFUND.DATA)
                from FCREFUND
                where FCREFUND.COD = Fcr.cod
                    and FCREFUND.TAB = FCR.tab
            )
            and tuss.cod_tuss > 0
    ) then (
        SELECT fcr.cod
        FROM fcrefund fcr
            left JOIN tbrefmemtuss tuss on fcr.cod = tuss.cod_fcrefund
            and fcr.tab = tuss.tab_fcrefund
            and fcr.classif = tuss.classif_fcrefund
        where fcr.cod = b.codref
            and fcr.tab = b.tab
            and fcr.classif = b.classif
            and fcr.data in (
                select max(FCREFUND.DATA)
                from FCREFUND
                where FCREFUND.COD = Fcr.cod
                    and FCREFUND.TAB = FCR.tab
            )
            and tuss.cod_tuss > 0
    )
    WHEN
end
    from TBMATMED T
left join TBMATMED R on (
    T.TIPO_REF = R.TAB
    and T.BRAS = R.COD
)
INNER JOIN gearmloc a ON A.item = T.cod
INNER JOIN tbrefuni B ON T.cod = B.cod
AND B.tab = T.tab
    where T.TAB = 5
and T.CLASSIF in (2, 9)
and T.SITUACAO = 'A'
AND A.ano = 2021
AND A.mes = 04
AND A.local = 54
AND A.qtde > 0
order by T.COD

I wonder if there is any possibility to make that case subselect within a Join, so would query only once.

Answer 1

I think you can use a CTE to run the subquery, leaving Case for the final part of the query:

with CTE_Sub as
(
    select distinct
    T.COD,
    T.NOME,
    T.BRAS,
    R.NOME as REF_NOME,
    R.cod_tuss_brasindice AS TUSS_BRASSINDIC,

    (
        SELECT fcr.cod
        FROM fcrefund fcr
            left JOIN tbrefmemtuss tuss on fcr.cod = tuss.cod_fcrefund
            and fcr.tab = tuss.tab_fcrefund
            and fcr.classif = tuss.classif_fcrefund
        where fcr.cod = b.codref
            and fcr.tab = b.tab
            and fcr.classif = b.classif
            and fcr.data in (
                select max(FCREFUND.DATA)
                from FCREFUND
                where FCREFUND.COD = Fcr.cod
                    and FCREFUND.TAB = FCR.tab
            )
            and tuss.cod_tuss > 0
    ) as fcrCod

    from TBMATMED T
    left join TBMATMED R on (
        T.TIPO_REF = R.TAB
        and T.BRAS = R.COD
    )
    INNER JOIN gearmloc a ON A.item = T.cod
    INNER JOIN tbrefuni B ON T.cod = B.cod
    AND B.tab = T.tab
    where T.TAB = 5
    and T.CLASSIF in (2, 9)
    and T.SITUACAO = 'A'
    AND A.ano = 2021
    AND A.mes = 04
    AND A.local = 54
    AND A.qtde > 0
)
    
select
    COD,
    NOME,
    BRAS,
    REF_NOME,
    TUSS_BRASSINDIC,
    case when TUSS_BRASSINDIC <> fcrCod then fcrCod end
from CTE_Sub
order by 
    COD

I hope it helps