select 
      
      sum(case when oi.status = 'LOADING' or (oi.status = 'SORTING' and oi.type = 'RELEASE_OF_GOODS') then oi.release_qty else 0 end) as nonLoadedQty
      ,sum(case when oi.status = 'LOADING' or (oi.status = 'SORTING' and oi.type = 'RELEASE_OF_GOODS') then (oi.release_uom_value/oi.release_qty * oi.release_qty) else 0 end) as "nonLoadedUomValue" 
      from tmp_data rih
      left join pallets plt on plt.id = rih.reusable_pallet_id
      inner join products prd on prd.id = rih.product_id
      inner join bizplaces bz on bz.id = rih.bizplace_id
      inner join locations loc on loc.id = rih.location_id
      inner join warehouses wh on wh.id = loc.warehouse_id
      inner join order_inventories oi on oi.inventory_id = rih.inventory_id
      group by rih.inventory_id,rih.domain_id,rih.pallet_id,rih.carton_id,rih.qty,rih.uom_value,rih.uom,rih.last_seq,rih.created_at, rih.initial_inbound_at,rih.unit_cost,rih.reusable_pallet_id,
      rih.batch_id,plt.name,rih.batch_id_ref,rih.product_id,rih.packing_type,rih.bizplace_id,rih.location_id,prd.id,bz.id,loc.id,wh.name,rih.remark,rih.expiration_date,rih.status
      order by created_at desc
      where nonLoadedQty > 0
      OFFSET 0 LIMIT 20

Good day everyone, i want to get the alias value from the nonLoadedQty but it shows the error below

SQL Error [42703]: ERROR: column "nonloadedqty" does not exist Position: 1920

CodePudding user response：

Your condition belongs in the HAVING clause, not the WHERE clause:

HAVING SUM(CASE WHEN oi.status = 'LOADING' OR
                     (oi.status = 'SORTING' AND oi.type = 'RELEASE_OF_GOODS')
                THEN oi.release_qty ELSE 0 END) > 0
ORDER BY created_at DESC
OFFSET 0 LIMIT 20

Also note that ORDER BY comes after HAVING but before LIMIT.