Oracle - EBS: SQL&PL/SQL 如何根据flag参数决定是否按照某些字段group分组

最新推荐文章于 2020-12-19 02:38:29 发布

Frank-Li

最新推荐文章于 2020-12-19 02:38:29 发布

阅读量330

点赞数

分类专栏： SQL & PL/SQL 文章标签： flag参数 group分组

本文链接：https://blog.csdn.net/lfl6848433/article/details/8739537

版权

SQL & PL/SQL 专栏收录该内容

12 篇文章 0 订阅

订阅专栏

需求：如下面源数据，把子库、货位、物料的金额查出来。

可按照一个参数flag字段来判断是否要按照子库货位来分组；

如果flag = ‘N'，则不需要按照子库、货位，只需要按照物料来分组汇总金额；

如果flag = 'Y'，则按照子库、货位、物料分组汇总金额。

--源数据

SELECT 'A' subinv, 'a' locator, 10001 inventory_item_id, 100 amount
  FROM dual
UNION
SELECT 'A' subinv, 'a' locator, 10001 inventory_item_id, 200 amount
  FROM dual
UNION
SELECT 'A' subinv, 'c' locator, 10001 inventory_item_id, 500 FROM dual;

--按照子库、货位、物料分组汇总金额

SELECT subinv, LOCATOR, inventory_item_id, SUM(amount)
  FROM (SELECT 'A' subinv, 'a' locator, 10001 inventory_item_id, 100 amount
          FROM dual
        UNION
        SELECT 'A' subinv, 'a' locator, 10001 inventory_item_id, 200 amount
          FROM dual
        UNION
        SELECT 'A' subinv, 'c' locator, 10001 inventory_item_id, 500 FROM dual) t
 GROUP BY subinv, LOCATOR, inventory_item_id;

--根据参数判断是否需要根据子库、货位分组

SELECT decode('N', 'N', NULL, t.subinv), decode('N', 'N', NULL, LOCATOR), inventory_item_id, SUM(amount)
  FROM (SELECT 'A' subinv, 'a' locator, 10001 inventory_item_id, 100 amount
          FROM dual
        UNION
        SELECT 'A' subinv, 'a' locator, 10001 inventory_item_id, 200 amount
          FROM dual
        UNION
        SELECT 'A' subinv, 'c' locator, 10001 inventory_item_id, 500 FROM dual) t
 GROUP BY decode('N', 'N', NULL, t.subinv), decode('N', 'N', NULL, LOCATOR), inventory_item_id;

--分组Flag作为参数传递进去

DECLARE
  l_flag VARCHAR2(1) := 'Y'; --分组Flag
BEGIN
  dbms_output.put_line('subinv,locator,inventory_item_id,amount_sum');
  FOR rec IN (SELECT decode(l_flag, 'N', NULL, t.subinv) subinv
                    ,decode(l_flag, 'N', NULL, t.locator) locator
                    ,t.inventory_item_id
                    ,SUM(t.amount) amount_sum
                FROM (SELECT 'A' subinv
                            ,'a' locator
                            ,10001 inventory_item_id
                            ,100 amount
                        FROM dual
                      UNION
                      SELECT 'A' subinv
                            ,'a' locator
                            ,10001 inventory_item_id
                            ,200 amount
                        FROM dual
                      UNION
                      SELECT 'A' subinv
                            ,'c' locator
                            ,10001 inventory_item_id
                            ,500
                        FROM dual) t
               GROUP BY decode(l_flag, 'N', NULL, t.subinv)
                       ,decode(l_flag, 'N', NULL, locator)
                       ,inventory_item_id) LOOP
  
    dbms_output.put_line(rec.subinv || ',' || rec.locator || ',' ||
                         rec.inventory_item_id || ',' || rec.amount_sum);
  END LOOP;
END;