来自不同select联合的值之和

6gpjuf90  于 2021-08-09  发布在  Java
关注(0)|答案(2)|浏览(343)

我有下一个问题:
如果你看到图像,我想加减值,但老实说,我不知道如何开始?

我就是这样得到这个结果的

select position, position_1, TIPO_RESUMEN, CONCEPT, to_char(NVL(VALUE,0) ,'FM999G999G999G999G990D00', 'NLS_NUMERIC_CHARACTERS='',.''') as VALUE
from (
    SELECT 1 position, 10 position_1, 'Expense' TIPO_RESUMEN, 'Expected expense' CONCEPT ,sum(IMP_GA_GU) VALUE from table_1 tg where UPPER(tg.cod) = 'USER_1' and tg.cod_fol = :P10_FOL and tg.prod_cuad = :P10_PROD and tg.fecha_inicio = :P10_FEC_INI and tip_ga = 'PREVISTO'
    union
    SELECT 2 position, 20 position_2, 'Expense' TIPO_RESUMEN, 'Unforeseen expense' CONCEPT ,sum(IMP_GA_GU) VALUE from table_2 tp where UPPER(tp.cod) = 'USER_1' and tp.cod_fol = :P10_FOL and tp.prod_cuad = :P10_PROD and tp.fecha_inicio = :P10_FEC_INI and tp.tip_ga = 'NO_PREVISTO'
    union
    SELECT 3 position, 30 position_3, 'Expense' TIPO_RESUMEN, 'Total' CONCEPT , 0 VALUE from dual
);

我需要的总价值是(预期费用+不可预见费用)
有人能帮我吗?
当做

sxpgvts3

sxpgvts31#

因为它是关于oracle应用程序表达,我建议你让apex做这项工作;它完全有能力做到这一点。怎样?只需创建一个报告(经典或交互式,无所谓)。
重要的是你应该——不知何故——知道哪些价值观是正面的,哪些是负面的。
如果你把它们放在一张table里,那就更好了。如果没有,使用 CASE 这样屏幕上的值就可以这样显示(正/负)。然后:
对于交互式报表,进入“操作”菜单,选择“数据”和“聚合”;在上使用“sum”函数 value
有关经典报告,请转到 value 列的属性,并检查“计算和”属性
很简单,不是吗?
如果你选择手工做每件事,那么,要得到一个有问题的结果就需要大量的编码/工作。你知道apex在主页上说什么吗?
构建企业应用程序的速度提高20倍,代码减少100倍
我建议你这样做。

t3irkdon

t3irkdon2#

你可以用cte然后 union all :

with cte as (
      select 1 as position, 10 as position_1, 'Expense' as TIPO_RESUMEN, 'Expected expense' as CONCEPT , sum(IMP_GA_GU) as VALUE
      from table_1 tg
      where UPPER(tg.cod) = 'USER_1' and tg.cod_fol = :P10_FOL and tg.prod_cuad = :P10_PROD and tg.fecha_inicio = :P10_FEC_INI and tip_ga = 'PREVISTO'
      union all
      select 2 as position, 20 as position_2, 'Expense' as TIPO_RESUMEN, 'Unforeseen expense' as CONCEPT, sum(IMP_GA_GU) as VALUE
      from table_2 tp 
      where UPPER(tp.cod) = 'USER_1' and tp.cod_fol = :P10_FOL and tp.prod_cuad = :P10_PROD and tp.fecha_inicio = :P10_FEC_INI and tp.tip_ga = 'NO_PREVISTO'
     )
select cte.*
from cte
union all
select 3 as position, 30 as position_3, 'Expense' as TIPO_RESUMEN, 'Total' as CONCEPT , sum(value)
from cte

相关问题