Return a value only when conditions satisfy for entire column 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.
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
;