select * from products

Select ProductID, count(ProductID) as TheCount
From
(
  Select NVL(RTRIM(LTRIM(Product_ID)), 'None') as ProductID
  From products
) Clean
Group By ProductID

  Select p.product_instance_id, 
           p.product_id, 
           p.product_nm, 
           p.product_subtype, 
           p. bom_item_type_nm,
           NVL(RTRIM(LTRIM(tbs_cd)), '4') as tbs_cd, 
           p.components_exist_ind, 
           p.dwg_no, 
           p.eng_auth_nm, 
           p.export_control_cd, 
           p.purchased_equip_ind, 
           p.source_system_nm, 
           p.spares_ind, 
           p.standard_part_ind, 
           p.supplier_spec_controlled_ind, 
           p.type_part_cd 

  where p.export = 'AMERICAN'
  and p.Product_Instance_id in
  (
    Select p.Product_Instance_id
    FROM brg_products p
    WHERE p.bom_item_type_nm = 'INSTL' 
    AND p.Product_Instance_id in
    (
      Select rang.Product_Instance_id
      From brg_product_ranges rang
      Where rang.Total_qty > 0 
      and Range_id in
      (
        Select eff.Range_ID
        From brg_range_effs eff
        Where eff.Eff_ID In
        (
          Select Eff_ID
          From brg_ap_cross_refs acr
          Where acr.ap_id_type_cd = 'L'  
          and acr.ap_instance_id in
          (
            SELECT
            a.ap_instance_id
            FROM thebigones a
            WHERE
            a.active_ind = 'Y'
            and a.prod_line between 771 and 871
            and a.model = '757'
          )
        )
      )
    )
  )