Case When + IN combination

Question

我有点拘泥于这里的编码。。。我有这个扩展和复杂的代码，但实际上我在它的末尾失败了。我得到了SQL错误[42804]：错误：CASE/WHEN的参数必须是布尔类型，而不是字符类型可变的

问题是：当；bairro"；匹配"0"中的一行；SUB_COUNTRY_DB"；，获取psd.name，否则获取z.name。关于如何实现这一点，有什么建议吗？

select distinct
aa.mes,
--aa.modalidade,
--to_date(aa.created_date, 'yyyy-mm-dd') data_captacao,
ucl.ja_comprou_lf as comprou_lf,
case when bairro in (select sub_country from sub_country_DB)
then psd.name 
else z.name 
end loja,
count (distinct aa.customer_uid) qtde_socios,
count (distinct aa.assinatura_uid) qtde_assinaturas
from assinaturas_ativas aa
left join ultima_compra_loja_fisica ucl           on (aa.customer_uid = ucl.customer_uid
and aa.mes = ucl.mes)
left join zip_code z                              on (aa.customer_uid = z.customer_uid
and aa.mes = z.mes)
left join SUB_COUNTRY_DB psd 
on (psd.district = aa.bairro)
group by 1,2,3--,4

Answer 1

尝试以下变体：

• 将条件移动到内部查询

  CASE WHEN EXISTS (SELECT DISTINCT sub_country FROM sub_country_DB WHERE sub_country = barrio)
  

• ANY(仅PostgreSQL(

  CASE WHEN bairro = ANY(ARRAY_AGG(select sub_country from sub_country_DB))