sql test

test.sql

cte_drum as (
  SELECT 
      A.PROJECT_CODE, 
      A.DRUNO AS DRUM_NO,
      A.CTYPNO,
      A.METER_MARKING_YN,
      A.ON_DELIVERY,
      (NVL(A.LOST,0)+ NVL(A.SCRAPPED, 0)) AS SCRAPPED_LOST,
      decode (A.METER_MARKING_YN, 'Y', B.HIGH_LOW,
                                  'N', B.INSTALLED) 
                                  AS PULLED,
      NVL(A.ON_DELIVERY,0) - (NVL(decode (
                                  A.METER_MARKING_YN, 
                                  'Y', B.HIGH_LOW,
                                  'N', B.INSTALLED),0)
                          + NVL(A.LOST,0)+ NVL(A.SCRAPPED, 0)) AS LEFT_ON_DRUM
    FROM 
      PACS.J2_DRUREG A
  left join cte_used_on_drum B on
      a.DRUNO = B.DRUM_NO
  ),