Oracle – Return Value Only When Conditions Satisfy for Entire Column

casegroup byoracle

Can anyone help me to how to achieve below result

Input data

ORDER_NUMBER    LINE_ITEM   FLAG
LP-13288    1   TRUE
LP-13288    2   FALSE
LP-13288    3   (null)
LP-13288    4   FALSE
LP-13289    1   FALSE
LP-13289    2   FALSE
LP-13290    1   TRUE
LP-13290    2   TRUE
LP-13290    3   TRUE
LP-13292    1   FALSE
LP-13293    2   TRUE

But I am expecting result like below:

ORDER_NUMBER    LINE_ITEM   FLAG    RESULT
LP-13288    1   TRUE    0
LP-13288    2   FALSE   0
LP-13288    3   (null)  0
LP-13288    4   FALSE   0
LP-13289    1   FALSE   0
LP-13289    2   FALSE   0
LP-13290    1   TRUE    1
LP-13290    2   TRUE    1
LP-13290    3   TRUE    1
LP-13292    1   FALSE   0
LP-13293    2   TRUE    1

Scenarios that need to implement

For a group of order_number, if all line items are 'true' then result field need be '1'
For a group of order_number, if any of line items contains mixed flags (true, false, null) then result field need to populate '0'

Any help would be appreciated.

Best Answer

You could use window aggregation (an aggregate function with an OVER clause) to apply the condition to a column of values whilst calculating the results for detail rows. Here is how you could use window aggregation in your situation:

SELECT
  order_number,
  line_item,
  flag,
  CASE COUNT(CASE flag WHEN 'true' THEN 1 END)
       OVER (PARTITION BY order_number)
  WHEN COUNT(*)
       OVER (PARTITION BY order_number)
  THEN 1
  ELSE 0
  END AS result
FROM
  orders
;

The COUNT(CASE flag WHEN 'true' THEN 1 END) OVER (PARTITION BY order_number) expression calculates the number of true values per order_number and returns the same value for rows that have the same order_number. As a result, you can generate values identical within groups of rows as well.

A variation on the above (using reverse logic and one count instead of two):

SELECT
  order_number,
  line_item,
  flag,
  CASE WHEN 
       COUNT(CASE flag WHEN 'true' THEN NULL ELSE 1 END)
       OVER (PARTITION BY order_number)
       = 0
  THEN 1
  ELSE 0
  END AS result
FROM
  orders
;

Related Solutions

Sql-server – Restricting multiple records to one by preference

I think this will get what you want. It may not be the best regarding efficiency (due to the unioned view):

WITH LowestPrices AS
    ( SELECT 
          UPC, 
          Price1, 
          SUM(Quantity) OVER (PARTITION BY UPC) AS Quantity, 
          DBID AS PricingDBID, 
          COUNT(*) OVER (PARTITION BY UPC) AS VendorCount,
          ROW_NUMBER() OVER (PARTITION BY UPC 
                             ORDER BY Price1,
                                      DBID         -- to resolve ties
                            ) AS Rn
      FROM         
          vProducts_PriceQty_Union  
     -- WHERE
     --     UPC = '082442818986'
    )
SELECT 
    UPC,
    Price1,
    Quantity,
    PricingDBID,
    VendorCount
FROM
    LowestPrices
WHERE
    Rn = 1
ORDER BY 
    UPC ;

If the join to Products is really needed, you can add it, either inside the CTE or in the external query.

How to commit transaction on an after update event trigger

Why do you expect B to be updated, when you inserted a row with a different id? Actually, your INSERT ALL does not insert anything into B.

The above works fine after fixing your trigger and sample data:

create or replace trigger updB  
after update on B  
for each row  
declare  
v varchar2(5);  
begin  
  v := :new.val;
end;  
/

This is how you reference the new val in the trigger, not with a select. Your original trigger would run into the infamous ORA-04091 "table is mutating" error.

SQL> insert all into A values ('a', 'false') into b values ('a', 'false') select * from dual;

2 rows created.

SQL> commit;

Commit complete.

SQL> update A set val = 'true';
Cek cek: false

1 row updated.

SQL> commit;

Commit complete.

SQL> select * from a;

ID         VAL
---------- ----------
a          true

SQL> select * from b;

ID         VAL
---------- ----------
a          true

For the output:

Your updA trigger runs in an autonomous transaction. When you select val from A, your original update is still not finished (commited), you will see the old value. Fixing your trigger for A:

create or replace trigger updA  
after update on A  
for each row  
declare  
pragma autonomous_transaction;  
cek varchar2(5);  
begin  
commit;  
  cek := :new.val;  
dbms_output.put_line('Cek cek: ' || cek);  
update B set val = :new.val where id = :new.id;  
commit;  
end;  
/

Best Answer

Related Solutions

Sql-server – Restricting multiple records to one by preference

How to commit transaction on an after update event trigger

Related Question