how to add a conditional statement after calculating two fields in SQL

Question

I need to output data based on a condition to limit output to usable data. Need help with understanding and optimizing query and removing redundancies for my SQL query

I tried conditions in the where statement, but that is giving me an error. Also tried adding a Having statement, which did not work either.

select
  o2.car_move_id as Carrier_Code,
  o1.early_shpdte,
  o1.prtnum,
  shpsts,
  (o1.host_ordqty / o3.untqty) as Order_pallets,
  (
    select
      count(i3.untqty)
    from
      INVENTORY_PCKWRK_VIEW i3
      inner join prtftp_dtl i4 on i3.prtnum = i4.prtnum
    where
      i3.invsts like 'U'
      and i3.wrkref is null
      and i3.prtnum = o1.prtnum
      and i3.untqty = i4.untqty
      and i4.uomcod like 'PL'
      and i4.wh_id like 'RX'
  ) as full_pallets,
  (
    select
      count(i5.untqty)
    from
      INVENTORY_PCKWRK_VIEW i5
      inner join prtftp_dtl i6 on i5.prtnum = i6.prtnum
    where
      i5.invsts like 'U'
      and i5.wrkref is null
      and i5.prtnum = o1.prtnum
      and i5.untqty < i6.untqty
      and i5.prtnum = i6.prtnum
      and i6.uomcod like 'PL'
      and i6.wh_id like 'RX'
  ) as Partial_pallets
from
  ord_line o1
  inner join SHIP_STRUCT_VIEW o2 on o1.ordnum = o2.ship_id
  inner join prtftp_dtl o3 on o1.prtnum = o3.prtnum
where
  o2.ship_id like '0%'
  and shpsts in ('R', 'I')
  and o1.non_alc_flg = 0
  and o3.wh_id like 'RX'
  and o3.uomcod like 'PL'
order by
  full_pallets asc,
  o1.early_shpdte asc

I want to only output the query where order_pallets > Full_Pallets. not sure where I can add this condition in my query.

Edid you post using code formatting tags. – Pochmurnik Aug 29 '19 at 12:37 — Pochmurnik, Aug 29 '19 at 12:37
`WHERE` _clause_, `HAVING` _clause_. _column_, not field. – jarlh Aug 29 '19 at 14:19 — jarlh, Aug 29 '19 at 14:19

score 0 · Answer 1 · answered Aug 29 '19 at 12:47

0

The items on the SELECT list of an SQL query are logically processed after the WHERE clause (as explained in this answer), that's why you cannot reference column aliases in the WHERE clause. You will need to use a subselect to accomplish what you want:

select * from (
  select
    o2.car_move_id as Carrier_Code,
    o1.early_shpdte,
    o1.prtnum,
    shpsts,
    -- the rest of your current query
) t
where t.order_pallets > t.Full_Pallets

answered Aug 29 '19 at 12:47

mustaccio

18,234
16
48
57

this was really helpful. Thank you – Ammar Bhotwawala Aug 29 '19 at 16:46
@AmmarBhotwawala please accept the answer if it solved your problem! – Joshua Schlichting Aug 29 '19 at 20:46

score 0 · Answer 2 · answered Aug 29 '19 at 13:05

0

You can enclose your entire query in

with x as ()

Then select from it:

select * from x 
where x.order_pallets > x.full_pallets

This will save you from having to maintain multiple subqueries for the same information.

answered Aug 29 '19 at 13:05

josh

1

how to add a conditional statement after calculating two fields in SQL

2 Answers2