Error using case clause inside a Where

Question

I’m having the following error in trying to use the case clause of a WHERE.

ORA-00905: palavra-chave não encontrada
00905. 00000 -  "missing keyword"
*Cause:    
*Action:
Erro na linha: 56 Coluna: 39

My query is as follows:

SELECT ST.cod_tpo_solicitacao,
       ST.dsc_tpo_solicitacao,
       ST.nom_canteiro,
       ST.sta_primeiro_tramite,
       ST.sta_ativo,
       ST.cod_sistema,
       ST.cod_grupo,
       refs.rv_meaning AS DESC_GRUPO
FROM   me_solicitacao_tipos ST 
INNER JOIN me_cg_ref_codes REFS ON To_char(ST.cod_grupo) = REFS.rv_low_value
WHERE  ( ST.cod_sistema = 'LS' )
AND ( ST.cod_companhia = 'XXXXX' )
AND 
    (CASE ST.cod_sistema
        WHEN 'LS' THEN refs.rv_domain = 'DA_GRP_TPO_SERVICO_LS';
        WHEN 'IH' THEN refs.rv_domain = 'DA_GRP_TPO_SERVICO_IH';
        WHEN 'CF' THEN refs.rv_domain = 'DA_GRP_TPO_SERVICO_CF';
        WHEN 'CA' THEN refs.rv_domain = 'DA_GRP_TPO_SERVICO_CA';
        ELSE ' '
    END)
ORDER  BY cod_tpo_solicitacao, Trim(dsc_tpo_solicitacao);

I’ve made a few dozen changes to the query and some searches and I can’t identify the error.

Answer 1

I believe this is it ... in general CASE is used as a column but it can be used in Where.

...
     (CASE  WHEN ST.cod_sistema = 'LS' THEN 'DA_GRP_TPO_SERVICO_LS';
            WHEN ST.cod_sistema = 'IH' THEN 'DA_GRP_TPO_SERVICO_IH';
            WHEN ST.cod_sistema = 'CF' THEN 'DA_GRP_TPO_SERVICO_CF';
            WHEN ST.cod_sistema = 'CA' THEN 'DA_GRP_TPO_SERVICO_CA';
            ELSE ' '
    END) = refs.rv_domain
...

Answer 2

Just one detail, in your query the first condition of WHERE is ST.cod_sistema = 'LS'. I mean, you’re just picking up the records with cod_sistema equal to "LS". Therefore, no CASE it no longer makes sense to test whether cod_sistema has a different value (will not), so just test if refs.rv_domain = 'DA_GRP_TPO_SERVICO_LS'.

But in a more general case where you don’t know the value of cod_sistema, you can use the CASE to compare the value. Just how to do not as you are trying.

Just remember that the CASE is an expression, and that it returns a value. So just compare this value with the column you want to check. Something like this:

SELECT [colunas]
FROM   me_solicitacao_tipos ST 
INNER JOIN me_cg_ref_codes refs ON ST.cod_grupo = refs.rv_low_value
WHERE ST.cod_companhia = 'CEDAE' -- não incluí o cod_sistema = 'LS', pois aí não faria sentido ter o CASE
AND -- usa o valor da expressão CASE e compara com a coluna rv_domain
    (CASE ST.cod_sistema
        WHEN 'LS' THEN 'DA_GRP_TPO_SERVICO_LS'
        WHEN 'IH' THEN 'DA_GRP_TPO_SERVICO_IH'
        WHEN 'CF' THEN 'DA_GRP_TPO_SERVICO_CF'
        WHEN 'CA' THEN 'DA_GRP_TPO_SERVICO_CA'
        ELSE ' '
    END) = refs.rv_domain
order by etc...

Remember to remove the semicolons after each WHEN.

---

Another alternative is to put all possibilities in a single condition:

WHERE ST.cod_companhia = 'CEDAE'
-- troca o CASE por várias condições
AND (
  (ST.cod_sistema = 'LS' AND refs.rv_domain = 'DA_GRP_TPO_SERVICO_LS') OR
  (ST.cod_sistema = 'IH' AND refs.rv_domain = 'DA_GRP_TPO_SERVICO_IH') OR
  (ST.cod_sistema = 'CF' AND refs.rv_domain = 'DA_GRP_TPO_SERVICO_CF') OR
  (ST.cod_sistema = 'CA' AND refs.rv_domain = 'DA_GRP_TPO_SERVICO_CA')
)

I mean, I test whether cod_sistema is "LS" and rv_domain is "DA_GRP_TPO_SERVICO_LS", or if cod_sistema is "IH" and rv_domain is "DA_GRP_TPO_SERVICO_IH", etc...

Answer 3

I solved it. It was a syntax error. It was like this:

AND refs.rv_domain =  (CASE when
    ST.cod_sistema = 'LS' THEN  'DA_GRP_TPO_SERVICO_LS'
    when ST.cod_sistema = 'IH' THEN  'DA_GRP_TPO_SERVICO_IH'
    when ST.cod_sistema = 'CF' THEN 'DA_GRP_TPO_SERVICO_CF'
    when ST.cod_sistema = 'CA' THEN  'DA_GRP_TPO_SERVICO_CA'
    when ST.cod_sistema = 'CR' THEN  'DA_GRP_TPO_SERVICO'
    ELSE ' '
END)

Thank you to everyone who helped.