时间戳

首页 > 解决方案 > PostgreSQL：查询在带有变量的查询中没有结果数据的目的地

postgresql - PostgreSQL：查询在带有变量的查询中没有结果数据的目的地

问题描述

错误：查询没有结果数据的目的地

提示：如果您想丢弃 SELECT 的结果，请改用 PERFORM。

上下文：SQL 语句中的 PL/pgSQL 函数 inline_code_block 第 11 行


DO $$
DECLARE
id_renovacion integer := (select Id_renovacionContrato 
from BEA.DIM_RENOVACION_CONTRATO where upper(Desc_renovacionContrato) = 'UNDEFINED'); 

id_renovacionpending integer := (select Id_renovacionContrato 
from BEA.DIM_RENOVACION_CONTRATO where upper(Desc_renovacionContrato) = 'PENDING');


BEGIN

SELECT count(id_contrato) as id_cantidad,
                CASE    when count(id_contrato) = 0 THEN 'ImagesHappyMaritransparente1.png'
                             when count(id_contrato) is null THEN 'ImagesHappyMaritransparente1.png'
                             when count(id_contrato) = 1 THEN 'ImagesTITIhappymarycontratos-off-1_145.png'
                             when count(id_contrato) = 2 THEN 'ImagesTITIhappymarycontratos-off-2_145.png'
                             when count(id_contrato) = 3 THEN 'ImagesTITIhappymarycontratos-off-3_145.png'
                             when count(id_contrato) = 4 THEN 'ImagesTITIhappymarycontratos-off-4_145.png'
                             when count(id_contrato) = 5 THEN 'ImagesTITIhappymarycontratos-off-5_145.png'
                             when count(id_contrato) = 6 THEN 'ImagesTITIhappymarycontratos-off-6_145.png'
                             when count(id_contrato) = 7 THEN 'ImagesTITIhappymarycontratos-off-7_145.png'
                             when count(id_contrato) = 8 THEN 'ImagesTITIhappymarycontratos-off-8_145.png'
                             when count(id_contrato) = 9 THEN 'ImagesTITIhappymarycontratos-off-9_145.png'
                             when count(id_contrato) > 9 THEN 'ImagesTITIhappymarycontratos-off-9+_145.png'
                END
 FROM BEA.FACT_CONTRATOS
WHERE cantDiasDif > 0 AND Id_rangoContrato IN (id_renovacion, id_renovacionpending);

END 
$$

LANGUAGE plpgsql; ```

标签： postgresql

解决方案

我可以在发布的代码中看到两个问题。

  1. create在代码中。我不知道你为什么create在你的匿名块中有。

  2. 您的 DO 块中有以下 SELECT ：

SELECT count(id_contrato) as id_cantidad,
                CASE    when count(id_contrato) = 0 THEN 'ImagesHappyMaritransparente1.png'
                             when count(id_contrato) is null THEN 'ImagesHappyMaritransparente1.png'
                             when count(id_contrato) = 1 THEN 'ImagesTITIhappymarycontratos-off-1_145.png'
                             when count(id_contrato) = 2 THEN 'ImagesTITIhappymarycontratos-off-2_145.png'
                             when count(id_contrato) = 3 THEN 'ImagesTITIhappymarycontratos-off-3_145.png'
                             when count(id_contrato) = 4 THEN 'ImagesTITIhappymarycontratos-off-4_145.png'
                             when count(id_contrato) = 5 THEN 'ImagesTITIhappymarycontratos-off-5_145.png'
                             when count(id_contrato) = 6 THEN 'ImagesTITIhappymarycontratos-off-6_145.png'
                             when count(id_contrato) = 7 THEN 'ImagesTITIhappymarycontratos-off-7_145.png'
                             when count(id_contrato) = 8 THEN 'ImagesTITIhappymarycontratos-off-8_145.png'
                             when count(id_contrato) = 9 THEN 'ImagesTITIhappymarycontratos-off-9_145.png'
                             when count(id_contrato) > 9 THEN 'ImagesTITIhappymarycontratos-off-9+_145.png'
                END
 FROM BEA.FACT_CONTRATOS
WHERE cantDiasDif > 0 AND Id_rangoContrato IN (id_renovacion, id_renovacionpending);

而且您没有将 SELECT 的返回结果存储到任何变量中。如果您不关心 SELECT 结果，请使用 PERFORM，如下所示：

PERFORM count(id_contrato) as id_cantidad,
                CASE    when count(id_contrato) = 0 THEN 'ImagesHappyMaritransparente1.png'
                             when count(id_contrato) is null THEN 'ImagesHappyMaritransparente1.png'
                             when count(id_contrato) = 1 THEN 'ImagesTITIhappymarycontratos-off-1_145.png'
                             when count(id_contrato) = 2 THEN 'ImagesTITIhappymarycontratos-off-2_145.png'
                             when count(id_contrato) = 3 THEN 'ImagesTITIhappymarycontratos-off-3_145.png'
                             when count(id_contrato) = 4 THEN 'ImagesTITIhappymarycontratos-off-4_145.png'
                             when count(id_contrato) = 5 THEN 'ImagesTITIhappymarycontratos-off-5_145.png'
                             when count(id_contrato) = 6 THEN 'ImagesTITIhappymarycontratos-off-6_145.png'
                             when count(id_contrato) = 7 THEN 'ImagesTITIhappymarycontratos-off-7_145.png'
                             when count(id_contrato) = 8 THEN 'ImagesTITIhappymarycontratos-off-8_145.png'
                             when count(id_contrato) = 9 THEN 'ImagesTITIhappymarycontratos-off-9_145.png'
                             when count(id_contrato) > 9 THEN 'ImagesTITIhappymarycontratos-off-9+_145.png'
                END
 FROM BEA.FACT_CONTRATOS
WHERE cantDiasDif > 0 AND Id_rangoContrato IN (id_renovacion, id_renovacionpending);

但是，请问您为什么要这样做？让我们知道不存储 SELECT 的结果或您的意图的原因。

推荐阅读