CODEWITHSUNDEEP
sql
chamara
chamara

Reputation: 12711

sql case statement syntax error

SELECT ph.order_ref_no,
       pd.porder_item_no,
       pd.catlog_code,
       pd.order_qty,
       pd.part_number,
       ph.porder_no,
       ph.order_date,
       pd.date_required,
       pd.project_no,
       s.name,
       SUM(pd.order_qty * pd.price) -
       ( SUM(pd.order_qty * pd.price) * pd.discount_pct ) + ( SUM(
       pd.order_qty * pd.price) * pd.gst_rate ) AS total,
       CASE ph.status
         WHEN ( pd.order_qty > SUM(dd.recd_qty) ) THEN 'Proceed'
         WHEN pd.order_qty = SUM(dd.recd_qty) THEN 'Completed'
         WHEN 'X' THEN 'canceled'
         ELSE 'New'
       END                                      AS status
FROM   zpu_purch_heade ph
       INNER JOIN zpu_purch_detai pd
         ON ph.porder_no = pd.porder_no
       INNER JOIN zpu_supplie s
         ON ph.supplier_code = s.supplier_code
       LEFT JOIN zpu_deldoc_detai dd
         ON ph.porder_no = dd.porder_no
WHERE  ph.client_code = 'METTRA'
GROUP  BY pd.porder_item_no,
          ph.order_ref_no,
          pd.project_no,
          ph.porder_no,
          ph.order_date,
          pd.date_required,
          pd.order_qty,
          pd.part_number,
          pd.price,
          pd.discount_pct,
          pd.gst_rate,
          pd.status,
          s.name,
          ph.status,
          pd.catlog_code,
          pd.order_qty
ORDER  BY ph.order_date DESC

i'm getting the error "Incorrect syntax near '>'." in case statement whats the error with it?

Upvotes: 0

Views: 519

Answers (3)

Mikael Eriksson
Mikael Eriksson

Reputation: 138960

To fix the syntax error you need to change your case statement to this:

CASE 
  WHEN ( pd.order_qty > SUM(dd.recd_qty) ) THEN 'Proceed'
  WHEN pd.order_qty = SUM(dd.recd_qty) THEN 'Completed'
  WHEN ph.status = 'X' THEN 'canceled'
  ELSE 'New'
END AS status

Upvotes: 4

Daniel Hilgarth
Daniel Hilgarth

Reputation: 174299

ph.status after CASE is not correct. Remove it.

Upvotes: 0

cjk
cjk

Reputation: 46415

The problem is this part:

CASE ph.status  WHEN (pd.order_qty > sum(dd.recd_qty))

You can't include a SUM like this.

Upvotes: 0

Related Questions