将行拆分以平衡行 || Oracle SQL

huangapple
go评论67阅读模式
英文:

Splitting Rows To Balance Lines || Oracle SQL

问题

具有表名：xxtb_je_txn的表如下所示：

|实体 | JE_HEADER_ID | NET_CR | NET_DR|
|----- |------------|------|------|
|401    |10101         |0       |30     |
|302    |10101         |0       |20     |
|402    |10101         |0       |50     |
|301    |10101         |100     |0      |
|402    |10102         |50      |0      |
|301    |10102         |0       |100    |
|401    |10102         |30      |0      |
|302    |10102         |20      |0      |
|102    |10103         |0       |400.44 |
|101    |10103         |992.57  |325.17 |
|201    |10103         |0       |266.96 |
|102    |10105         |62.5    |0      |
|201    |10105         |0       |17291  |
|101    |10105         |17228.5 |0      |
|204    |10104         |200     |0      |
|101    |10104         |0       |200    |
|301    |10106         |70      |0      |拒绝|
|302    |10106         |30      |0      |拒绝|
|401    |10106         |0       |60     |拒绝|
|402    |10106         |0       |40     |拒绝|
英文:

I have a table: xxtb_je_txn

CREATE TABLE XXTB_JE_TXN 
( "ENTITY" VARCHAR2(10 BYTE), 
"JE_HEADER_ID" VARCHAR2(1000 BYTE), 
"NET_CR" NUMBER, 
"NET_DR" NUMBER
);

Insert into XXTB_JE_TXN (ENTITY,JE_HEADER_ID,NET_CR,NET_DR) values ('401','10101',0,30);
Insert into XXTB_JE_TXN (ENTITY,JE_HEADER_ID,NET_CR,NET_DR) values ('302','10101',0,20);
Insert into XXTB_JE_TXN (ENTITY,JE_HEADER_ID,NET_CR,NET_DR) values ('402','10101',0,50);
Insert into XXTB_JE_TXN (ENTITY,JE_HEADER_ID,NET_CR,NET_DR) values ('301','10101',100,0);
Insert into XXTB_JE_TXN (ENTITY,JE_HEADER_ID,NET_CR,NET_DR) values ('402','10102',50,0);
Insert into XXTB_JE_TXN (ENTITY,JE_HEADER_ID,NET_CR,NET_DR) values ('301','10102',0,100);
Insert into XXTB_JE_TXN (ENTITY,JE_HEADER_ID,NET_CR,NET_DR) values ('401','10102',30,0);
Insert into XXTB_JE_TXN (ENTITY,JE_HEADER_ID,NET_CR,NET_DR) values ('302','10102',20,0);
Insert into XXTB_JE_TXN (ENTITY,JE_HEADER_ID,NET_CR,NET_DR) values ('102','10103',0,400.44);
Insert into XXTB_JE_TXN (ENTITY,JE_HEADER_ID,NET_CR,NET_DR) values ('101','10103',992.57,325.17);
Insert into XXTB_JE_TXN (ENTITY,JE_HEADER_ID,NET_CR,NET_DR) values ('201','10103',0,266.96);
Insert into XXTB_JE_TXN (ENTITY,JE_HEADER_ID,NET_CR,NET_DR) values ('102','10105',62.5,0);
Insert into XXTB_JE_TXN (ENTITY,JE_HEADER_ID,NET_CR,NET_DR) values ('201','10105',0,17291);
Insert into XXTB_JE_TXN (ENTITY,JE_HEADER_ID,NET_CR,NET_DR) values ('101','10105',17228.5,0);
Insert into XXTB_JE_TXN (ENTITY,JE_HEADER_ID,NET_CR,NET_DR) values ('204','10104',200,0);
Insert into XXTB_JE_TXN (ENTITY,JE_HEADER_ID,NET_CR,NET_DR) values ('101','10104',0,200);
Insert into XXTB_JE_TXN (ENTITY,JE_HEADER_ID,NET_CR,NET_DR) values ('301','10106',70,0);
Insert into XXTB_JE_TXN (ENTITY,JE_HEADER_ID,NET_CR,NET_DR) values ('302','10106',30,0);
Insert into XXTB_JE_TXN (ENTITY,JE_HEADER_ID,NET_CR,NET_DR) values ('401','10106',0,60);
Insert into XXTB_JE_TXN (ENTITY,JE_HEADER_ID,NET_CR,NET_DR) values ('402','10106',0,40);

It will look like this:

ENTITY JE_HEADER_ID NET_CR NET_DR
401 10101 0 30
302 10101 0 20
402 10101 0 50
301 10101 100 0
402 10102 50 0
301 10102 0 100
401 10102 30 0
302 10102 20 0
102 10103 0 400.44
101 10103 992.57 325.17
201 10103 0 266.96
102 10105 62.5 0
201 10105 0 17291
101 10105 17228.5 0
204 10104 200 0
101 10104 0 200
301 10106 70 0
302 10106 30 0
401 10106 0 60
402 10106 0 40

I want to split the row to create the Balance entries like:

ENTITY JE_HEADER_ID NET_CR NET_DR
401 10101 0 30
302 10101 0 20
402 10101 0 50
301 10101 30 0
301 10101 20 0
301 10101 50 0
402 10102 50 0
301 10102 0 50
301 10102 0 30
301 10102 0 20
401 10102 30 0
302 10102 20 0
102 10103 0 400.44
101 10103 400.44 0
101 10103 266.96 0
201 10103 0 266.96
102 10105 62.5 0
201 10105 0 17228.5
201 10105 0 62.5
101 10105 17228.5 0
204 10104 200 0
101 10104 0 200

For rows with JE_HEADER_ID = 10106, there is no clear-cut association of net_cr and net_dr between Entities. So query should ignore these rows and flag them like this.

ENTITY JE_HEADER_ID NET_CR NET_DR ERROR
301 10106 70 0 REJECT
302 10106 30 0 REJECT
401 10106 0 60 REJECT
402 10106 0 40 REJECT

How to Achieve This?

The query provided by @Serg was helpful, but it does not include rows with JE_HEADER_ID 10104, as I did not mention those rows in the original question. The solution worked well for the original question. Therefore, I am rephrasing the question with all the necessary assumptions (Two New Sets of Rows with JE_HEADER_IDs 10104 and 10106).

with t as (
select ENTITY,JE_HEADER_ID, greatest(0, NET_CR-NET_DR)NET_CR, greatest(0, NET_DR-NET_CR) NET_DR
  , sum(greatest(0, NET_CR-NET_DR)) over(partition by JE_HEADER_ID) tot
from XXTB_JE_TXN 
)
select a.*
from t
cross join lateral (
  select t.ENTITY,t.JE_HEADER_ID,t2.NET_CR,t2.NET_DR
  from t t2
  where t2.JE_HEADER_ID = t.JE_HEADER_ID and t2.tot != t2.NET_CR and t2.tot != t2.NET_DR
) a
where t.tot = t.NET_CR or t.tot = t.NET_DR
  
union all
  
select ENTITY,JE_HEADER_ID,NET_DR,NET_CR
from t
where t.tot != t.NET_CR and t.tot != t.NET_DR
order by 2, 1;

答案1

得分: 1

以下是翻译好的 SQL 查询部分：

SELECT entity,
       je_header_id,
       SUM(net_cr) AS net_cr,
       SUM(net_dr) AS net_dr,
       MAX(note) AS note
FROM   (
  SELECT entity,
         je_header_id,
         CASE WHEN type = 'CR' THEN amount END AS net_cr,
         CASE WHEN type = 'DR' THEN amount END AS net_dr,
         CASE WHEN min_group_size = 1 THEN 'ACCEPT' ELSE 'REJECT' END AS note
  FROM   (
    SELECT je_header_id,
           COALESCE(
             e_cr,
             LEAD(e_cr) IGNORE NULLS OVER (
               PARTITION BY je_header_id
               ORDER BY total, amount
             )
           ) AS e_cr,
           COALESCE(
             e_dr,
             LEAD(e_dr) IGNORE NULLS OVER (
               PARTITION BY je_header_id
               ORDER BY total, amount
             )
           ) AS e_dr,
           LEAST(
             COUNT(net_cr) OVER (PARTITION BY je_header_id),
             COUNT(net_dr) OVER (PARTITION BY je_header_id)
           ) AS min_group_size,
           total - LAG(total, 1, 0) OVER (
               PARTITION BY je_header_id
               ORDER BY total, amount
           ) AS amount
    FROM   (
      SELECT je_header_id,
             ABS(net_cr - net_dr) AS amount,
             CASE
             WHEN net_cr > net_dr
             THEN net_cr - net_dr
             END AS net_cr,
             CASE
             WHEN net_dr > net_cr
             THEN net_dr - net_cr
             END AS net_dr,
             CASE WHEN net_cr > net_dr THEN entity END AS e_cr,
             CASE WHEN net_dr > net_cr THEN entity END AS e_dr,
             SUM(ABS(net_cr - net_dr)) OVER (
               PARTITION BY je_header_id, SIGN(net_cr - net_dr)
               ORDER BY ABS(net_cr - net_dr)
             ) AS total
      FROM   xxtb_je_txn
    )
  )
  UNPIVOT (
    entity FOR type IN (
      e_cr AS 'CR',
      e_dr AS 'DR'
    )
  )
  WHERE  amount > 0
)
GROUP BY
       je_header_id,
       entity,
       CASE note WHEN 'REJECT' THEN NULL ELSE ROWNUM END

希望对你有所帮助。

英文:

You can use:

SELECT entity,
       je_header_id,
       SUM(net_cr) AS net_cr,
       SUM(net_dr) AS net_dr,
       MAX(note) AS note
FROM   (
  SELECT entity,
         je_header_id,
         CASE WHEN type = 'CR' THEN amount END AS net_cr,
         CASE WHEN type = 'DR' THEN amount END AS net_dr,
         CASE WHEN min_group_size = 1 THEN 'ACCEPT' ELSE 'REJECT' END AS note
  FROM   (
    SELECT je_header_id,
           COALESCE(
             e_cr,
             LEAD(e_cr) IGNORE NULLS OVER (
               PARTITION BY je_header_id
               ORDER BY total, amount
             )
           ) AS e_cr,
           COALESCE(
             e_dr,
             LEAD(e_dr) IGNORE NULLS OVER (
               PARTITION BY je_header_id
               ORDER BY total, amount
             )
           ) AS e_dr,
           LEAST(
             COUNT(net_cr) OVER (PARTITION BY je_header_id),
             COUNT(net_dr) OVER (PARTITION BY je_header_id)
           ) AS min_group_size,
           total - LAG(total, 1, 0) OVER (
               PARTITION BY je_header_id
               ORDER BY total, amount
           ) AS amount
    FROM   (
      SELECT je_header_id,
             ABS(net_cr - net_dr) AS amount,
             CASE
             WHEN net_cr > net_dr
             THEN net_cr - net_dr
             END AS net_cr,
             CASE
             WHEN net_dr > net_cr
             THEN net_dr - net_cr
             END AS net_dr,
             CASE WHEN net_cr > net_dr THEN entity END AS e_cr,
             CASE WHEN net_dr > net_cr THEN entity END AS e_dr,
             SUM(ABS(net_cr - net_dr)) OVER (
               PARTITION BY je_header_id, SIGN(net_cr - net_dr)
               ORDER BY ABS(net_cr - net_dr)
             ) AS total
      FROM   xxtb_je_txn
    )
  )
  UNPIVOT (
    entity FOR type IN (
      e_cr AS 'CR',
      e_dr AS 'DR'
    )
  )
  WHERE  amount > 0
)
GROUP BY
       je_header_id,
       entity,
       CASE note WHEN 'REJECT' THEN NULL ELSE ROWNUM END

Which, for your sample data, outputs:

ENTITY JE_HEADER_ID NET_CR NET_DR NOTE
301 10101 20 null ACCEPT
302 10101 null 20 ACCEPT
301 10101 30 null ACCEPT
401 10101 null 30 ACCEPT
301 10101 50 null ACCEPT
402 10101 null 50 ACCEPT
302 10102 20 null ACCEPT
301 10102 null 20 ACCEPT
401 10102 30 null ACCEPT
301 10102 null 30 ACCEPT
402 10102 50 null ACCEPT
301 10102 null 50 ACCEPT
101 10103 266.96 null ACCEPT
201 10103 null 266.96 ACCEPT
101 10103 400.44 null ACCEPT
102 10103 null 400.44 ACCEPT
204 10104 200 null ACCEPT
101 10104 null 200 ACCEPT
102 10105 62.5 null ACCEPT
201 10105 null 62.5 ACCEPT
101 10105 17228.5 null ACCEPT
201 10105 null 17228.5 ACCEPT
302 10106 30 null REJECT
402 10106 null 40 REJECT
301 10106 70 null REJECT
401 10106 null 60 REJECT

fiddle

答案2

得分: 1

我的尝试。针对您的输入进行了测试，显示出了正确的结果。这类似于匹配交易的查询，只是对于 _rejected_ 行有额外的逻辑：

[dbfiddle 演示](https://dbfiddle.uk/zE3GZRCq)

    with t as (
      select ENTITY ent, JE_HEADER_ID id,          
             greatest(NET_CR-NET_DR, 0) cr, greatest(NET_DR-NET_CR, 0) dr,
             sum(greatest(NET_CR-NET_DR, 0)) 
               over (partition by JE_HEADER_ID order by NET_CR, rowid) scr, 
             sum(greatest(NET_DR-NET_CR, 0)) 
               over (partition by JE_HEADER_ID order by NET_DR, rowid) sdr
      from XXTB_JE_TXN),
    c as (select ent, id, cr, scr-cr c1, scr c2 from t where cr > 0),
    d as (select ent, id, dr, sdr-dr d1, sdr d2 from t where dr > 0),
    m as (
      select cent, dent, id, cr, dr, val, cc, cd,
             max(case when cc > 1 and cd > 1 then 'rejected' end) 
                 over (partition by id) status
      from (
        select c.ent cent, d.ent dent, c.id, cr, dr, --c1, c2, d1, d2,
               least(cr, c2-d1, dr, d2-c1) val,
               count(1) over (partition by c.id, c.ent) cc, 
               count(1) over (partition by c.id, d.ent) cd
        from c join d on c.id = d.id and c1 < d2 and d1 < c2) )
    select t.ent, t.id, case status when 'rejected' then t.cr else m.val end cr, 
           0 dr, m.status 
    from t join m on t.id = m.id and t.ent = m.cent 
    where t.cr >= 0 and (cc < 2 or cd < 2)
    union all
    select t.ent, t.id, 0 cr, 
           case status when 'rejected' then t.dr else m.val end dr, m.status 
    from t join m on t.id = m.id and t.ent = m.dent 
    where t.dr >= 0 and (cc < 2 or cd < 2)
    order by id, cr, dr
英文:

My try. Tested for your input and it shows correct result. It is similar to queries matching transactions, except additional logic for rejected rows:

dbfiddle demo

with t as (
  select ENTITY ent, JE_HEADER_ID id,          
         greatest(NET_CR-NET_DR, 0) cr, greatest(NET_DR-NET_CR, 0) dr,
         sum(greatest(NET_CR-NET_DR, 0)) 
           over (partition by JE_HEADER_ID order by NET_CR, rowid) scr, 
         sum(greatest(NET_DR-NET_CR, 0)) 
           over (partition by JE_HEADER_ID order by NET_DR, rowid) sdr
  from XXTB_JE_TXN),
c as (select ent, id, cr, scr-cr c1, scr c2 from t where cr > 0),
d as (select ent, id, dr, sdr-dr d1, sdr d2 from t where dr > 0),
m as (
  select cent, dent, id, cr, dr, val, cc, cd,
         max(case when cc > 1 and cd > 1 then 'rejected' end) 
             over (partition by id) status
  from (
    select c.ent cent, d.ent dent, c.id, cr, dr, --c1, c2, d1, d2,
           least(cr, c2-d1, dr, d2-c1) val,
           count(1) over (partition by c.id, c.ent) cc, 
           count(1) over (partition by c.id, d.ent) cd
    from c join d on c.id = d.id and c1 < d2 and d1 < c2) )
select t.ent, t.id, case status when 'rejected' then t.cr else m.val end cr, 
       0 dr, m.status 
from t join m on t.id = m.id and t.ent = m.cent 
where t.cr >= 0 and (cc < 2 or cd < 2)
union all
select t.ent, t.id, 0 cr, 
       case status when 'rejected' then t.dr else m.val end dr, m.status 
from t join m on t.id = m.id and t.ent = m.dent 
where t.dr >= 0 and (cc < 2 or cd < 2)
order by id, cr, dr

答案3

得分: 0

一种选项是使用 MODEL Clause，它非常实用、可靠且快速。它的工作方式与使用 Excel 表格非常类似。因此，在需要处理数据集中的不同行、列或单元格时，这可能是一个不错的选择。

使用提供的数据，我创建了一个通用表达式（grid）来准备数据集...

(根据下面的评论调整代码)

 WITH grid AS
    (   选择    JE_HEADER_ID, ENTITY, NET_CR, NET_DR,
                  GREATEST(Sum(NET_CR) Over(Partition  By JE_HEADER_ID),
                  Sum(NET_DR) Over(Partition  By JE_HEADER_ID)) "TOTAL",
                  Case When Count(*) Over(Partition  By JE_HEADER_ID) = 2 Then 'TXN'
                       When NET_CR = Sum(NET_CR) Over(Partition  By JE_HEADER_ID) And NET_DR != 0 Then 'TOT/TXN'
                       When NET_CR = Sum(NET_CR) Over(Partition  By JE_HEADER_ID) And NET_DR = 0  Then 'TOT'
                       When NET_DR = Sum(NET_DR) Over(Partition  By JE_HEADER_ID) And NET_CR != 0 Then 'TOT/TXN'
                       When NET_DR = Sum(NET_DR) Over(Partition  By JE_HEADER_ID) And NET_CR = 0  Then 'TOT' 
                  Else 'TXN'
                  End "AMOUNT_TYPE",
                  Row_Number() Over(Partition  By JE_HEADER_ID Order By JE_HEADER_ID, ENTITY) "RN",
                  Count(*) Over(Partition  By JE_HEADER_ID) "MAX_RN"
        From      XXTB_JE_TXN
        Order By  JE_HEADER_ID, ENTITY 
    )

...然后使用 MODEL Clause 来获得结果...

--  主 SQL
SELECT  ENTITY, JE_HEADER_ID, NET_CR, NET_DR, ACCEPTED
FROM    (  选择  JE_HEADER_ID, IDX, RN, MAX_RN, AMOUNT_TYPE, ENTITY, Nvl(NET_CR, 0) "NET_CR", Nvl(NET_DR, 0) "NET_DR", Nvl(TOTAL, 0) "TOTAL", ACCEPTED
            从 (  选择    0 "IDX", JE_HEADER_ID, RN, MAX_RN, ENTITY, NET_CR, NET_DR, TOTAL, AMOUNT_TYPE, 'YES' "ACCEPTED"
                    从      grid  
                 )
            MODEL     Partition By   (JE_HEADER_ID)
                      Dimension By  (IDX, RN, AMOUNT_TYPE)
                      Measures       (ENTITY, NET_CR, NET_DR, TOTAL, ACCEPTED, MAX_RN)
            RULES   ITERATE(6)
              (   NET_DR[1, ITERATION_NUMBER + 1, 'TXN'] =  CASE WHEN NET_CR[0, CV(RN), 'TXN'] != 0 THEN NET_CR[0, CV(RN), CV()] ELSE 0 END, 
                  --
                  NET_CR[1, ITERATION_NUMBER + 1, 'TXN'] =  CASE WHEN NET_DR[0, CV(RN), 'TXN'] != 0 THEN NET_DR[0, CV(RN), CV()] ELSE 0 END,
                  --
                  ACCEPTED[ANY, ANY, ANY] =  CASE   WHEN MAX_RN[CV(), CV(), CV()] = 2 And ( NET_CR[0, 1, 'TXN'] = NET_DR[0, 2, 'TXN'] OR NET_DR[0, 1, 'TXN'] = NET_CR[0, 2, 'TXN'] ) THEN 'YES'
                                                    WHEN Max(TOTAL)[IDX = 0, RN > 0, AMOUNT_TYPE Like '%TOT%'] > 0 Then 'YES' 
                                            ELSE 'NO' 
                                            END,
                  ENTITY[1, ANY, 'TXN'] =  ENTITY[0, CV(RN), 'TXN']
              )
        )
WHERE      Nvl(NET_CR, 0) + Nvl(NET_DR, 0) != 0  And AMOUNT_TYPE != 'TOT' And 
           TOTAL != CASE WHEN ACCEPTED = 'NO' THEN 0 ELSE TOTAL + 1 END And
           (  (NET_CR = 0 And TOTAL IN(0, TOTAL)) OR (NET_DR = 0 And TOTAL IN(0, TOTAL))  )
ORDER BY JE_HEADER_ID, RN, IDX

--		结果：
--	
--	ENTITY  JE_HEADER_ID  NET_CR  NET_DR  ACCEPTED
--	------  ------------ ------- -------  --------
--	302	    10101	             0	    20	YES
--	302	    10101	            20	     0	YES
--	401	    10101	             0	    30	YES
--	401	    10101	            30	     0	YES
--	402	    10101	             0	    50	YES
--	402	    10101	            50	     0	YES
--	302	    10102	            20	     0	YES
--	302	    10102	             0	    20	YES
--	401	    10102	            30	     0	YES
--	401	    10102	             0	    30	YES
--	402	    10102	            50	     0	YES
--	402	    10102	             0	    50	YES
--	102	    10103	             0	400.44	YES
--	102	    10103	        400.44	     0	YES
--	201	    10103	             0	266.96	YES
--	201	    10103	        266.96	     0	YES
--	101		10104				 0	   200  YES
--	204		10104			   200		 0  YES
--	101	    10105	       17228.5       0	YES
--	101	    10105	             0 17228.5	YES
--	102	    10105	          62.5	     0	YES
--	102	    10105	             0	  

<details>
<summary>英文:</summary>

One of the options is to use [MODEL Clause][1] which is very usefull, reliable and fast. It works prety much like working with excel sheet. So, it could be a good choice when you need to work with different sets of rows or columns or with individual cells within the dataset.
With data provided, I created a cte (grid) to prepare the dataset...

*(Code adjusted after the comment below)*
~~~sql
 WITH grid AS
    (   Select    JE_HEADER_ID, ENTITY, NET_CR, NET_DR,
                  GREATEST(Sum(NET_CR) Over(Partition  By JE_HEADER_ID),
                  Sum(NET_DR) Over(Partition  By JE_HEADER_ID)) &quot;TOTAL&quot;,
                  Case When Count(*) Over(Partition  By JE_HEADER_ID) = 2 Then &#39;TXN&#39;
                       When NET_CR = Sum(NET_CR) Over(Partition  By JE_HEADER_ID) And NET_DR != 0 Then &#39;TOT/TXN&#39;
                       When NET_CR = Sum(NET_CR) Over(Partition  By JE_HEADER_ID) And NET_DR = 0  Then &#39;TOT&#39;
                       When NET_DR = Sum(NET_DR) Over(Partition  By JE_HEADER_ID) And NET_CR != 0 Then &#39;TOT/TXN&#39;
                       When NET_DR = Sum(NET_DR) Over(Partition  By JE_HEADER_ID) And NET_CR = 0  Then &#39;TOT&#39; 
                  Else &#39;TXN&#39;
                  End &quot;AMOUNT_TYPE&quot;,
                  Row_Number() Over(Partition  By JE_HEADER_ID Order By JE_HEADER_ID, ENTITY) &quot;RN&quot;,
                  Count(*) Over(Partition  By JE_HEADER_ID) &quot;MAX_RN&quot;
        From      XXTB_JE_TXN
        Order By  JE_HEADER_ID, ENTITY 
    )

...and then used the MODEL Clause to get the result...

--  M a i n   S Q L
SELECT  ENTITY, JE_HEADER_ID, NET_CR, NET_DR, ACCEPTED
FROM    (  Select  JE_HEADER_ID, IDX, RN, MAX_RN, AMOUNT_TYPE, ENTITY, Nvl(NET_CR, 0) &quot;NET_CR&quot;, Nvl(NET_DR, 0) &quot;NET_DR&quot;, Nvl(TOTAL, 0) &quot;TOTAL&quot;, ACCEPTED
            From (  Select    0 &quot;IDX&quot;, JE_HEADER_ID, RN, MAX_RN, ENTITY, NET_CR, NET_DR, TOTAL, AMOUNT_TYPE, &#39;YES&#39; &quot;ACCEPTED&quot;
                    From      grid  
                 )
            MODEL     Partition By   (JE_HEADER_ID)
                      Dimension By  (IDX, RN, AMOUNT_TYPE)
                      Measures       (ENTITY, NET_CR, NET_DR, TOTAL, ACCEPTED, MAX_RN)
            RULES   ITERATE(6)
              (   NET_DR[1, ITERATION_NUMBER + 1, &#39;TXN&#39;] =  CASE WHEN NET_CR[0, CV(RN), &#39;TXN&#39;] != 0 THEN NET_CR[0, CV(RN), CV()] ELSE 0 END, 
                  --
                  NET_CR[1, ITERATION_NUMBER + 1, &#39;TXN&#39;] =  CASE WHEN NET_DR[0, CV(RN), &#39;TXN&#39;] != 0 THEN NET_DR[0, CV(RN), CV()] ELSE 0 END,
                  --
                  ACCEPTED[ANY, ANY, ANY] =  CASE   WHEN MAX_RN[CV(), CV(), CV()] = 2 And ( NET_CR[0, 1, &#39;TXN&#39;] = NET_DR[0, 2, &#39;TXN&#39;] OR NET_DR[0, 1, &#39;TXN&#39;] = NET_CR[0, 2, &#39;TXN&#39;] ) THEN &#39;YES&#39;
                                                    WHEN Max(TOTAL)[IDX = 0, RN &gt; 0, AMOUNT_TYPE Like &#39;%TOT%&#39;] &gt; 0 Then &#39;YES&#39; 
                                            ELSE &#39;NO&#39; 
                                            END,
                  ENTITY[1, ANY, &#39;TXN&#39;] =  ENTITY[0, CV(RN), &#39;TXN&#39;]
              )
        )
WHERE      Nvl(NET_CR, 0) + Nvl(NET_DR, 0) != 0  And AMOUNT_TYPE != &#39;TOT&#39; And 
           TOTAL != CASE WHEN ACCEPTED = &#39;NO&#39; THEN 0 ELSE TOTAL + 1 END And
           (  (NET_CR = 0 And TOTAL IN(0, TOTAL)) OR (NET_DR = 0 And TOTAL IN(0, TOTAL))  )
ORDER BY JE_HEADER_ID, RN, IDX

--		R e s u l t :
--	
--	ENTITY  JE_HEADER_ID  NET_CR  NET_DR  ACCEPTED
--	------  ------------ ------- -------  --------
--	302	    10101	             0	    20	YES
--	302	    10101	            20	     0	YES
--	401	    10101	             0	    30	YES
--	401	    10101	            30	     0	YES
--	402	    10101	             0	    50	YES
--	402	    10101	            50	     0	YES
--	302	    10102	            20	     0	YES
--	302	    10102	             0	    20	YES
--	401	    10102	            30	     0	YES
--	401	    10102	             0	    30	YES
--	402	    10102	            50	     0	YES
--	402	    10102	             0	    50	YES
--	102	    10103	             0	400.44	YES
--	102	    10103	        400.44	     0	YES
--	201	    10103	             0	266.96	YES
--	201	    10103	        266.96	     0	YES
--	101		10104				 0	   200  YES
--	204		10104			   200		 0  YES
--	101	    10105	       17228.5       0	YES
--	101	    10105	             0 17228.5	YES
--	102	    10105	          62.5	     0	YES
--	102	    10105	             0	  62.5	YES
--	301	    10106	            70	     0	NO
--	302	    10106	            30	     0	NO
--	401	    10106	             0	    60	NO
--	402	    10106	             0	    40	NO

huangapple
  • 本文由 发表于 2023年6月19日 02:46:23
  • 转载请务必保留本文链接：https://go.coder-hub.com/76502072.html
  • oracle
  • sql
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定