Adding an if statement on the select

The something wrong with the IF statement and i don't no what,

Any help, thanks
DECLARE @COST FLOAT
 
SELECT scheme.opheadm.order_no, scheme.opheadm.customer, scheme.slcustm.currency, scheme.opheadm.date_entered, scheme.opheadm.territory, scheme.opheadm.carrier_code, scheme.opdetm.order_no, scheme.opdetm.order_line_no, scheme.opdetm.line_type, scheme.opdetm.warehouse, scheme.opdetm.product, scheme.opdetm.unit_of_sale, scheme.opdetm.order_line_status, scheme.opdetm.order_qty, scheme.opdetm.allocated_qty, scheme.opdetm.despatched_qty, scheme.opdetm.val,
IF scheme.opdetm.order_line_status ='B'
SELECT @COST = scheme.opdetm.order_qty
IF scheme.opdetm.allocated_qty>0 
SELECT @COST = scheme.opdetm.allocated_qty 
IF scheme.opdetm.despatched_qty>0
SELECT @COST = scheme.opdetm.despatched_qty 
 
 
FROM (scheme.opdetm (NOLOCK) INNER JOIN scheme.opheadm  (NOLOCK)  ON scheme.opdetm.order_no = scheme.opheadm.order_no)  
INNER JOIN scheme.slcustm (NOLOCK) ON scheme.opheadm.customer = scheme.slcustm.customer
WHERE (scheme.opheadm.date_entered)= CAST(FLOOR(CAST(GETDATE() AS FLOAT))AS DATETIME);

Open in new window

LVL 2
meteorelecAsked:
Who is Participating?
 
TimCotteeConnect With a Mentor Head of Software ServicesCommented:
Hello meteorelec,

IF is used in SQL for control of flow operations, you cannot use it in this way. You must use the CASE construct instead:

   
   SELECT scheme.opheadm.order_no, scheme.opheadm.customer, scheme.slcustm.currency, scheme.opheadm.date_entered, scheme.opheadm.territory, scheme.opheadm.carrier_code, scheme.opdetm.order_no, scheme.opdetm.order_line_no, scheme.opdetm.line_type, scheme.opdetm.warehouse, scheme.opdetm.product, scheme.opdetm.unit_of_sale, scheme.opdetm.order_line_status, scheme.opdetm.order_qty, scheme.opdetm.allocated_qty, scheme.opdetm.despatched_qty, scheme.opdetm.val,
   Case When scheme.opdetm.order_line_status ='B' Then scheme.opdetm.order_qty
   When scheme.opdetm.allocated_qty>0 Then scheme.opdetm.allocated_qty
   When scheme.opdetm.despatched_qty>0 Then scheme.opdetm.despatched_qty End As Cost
   FROM (scheme.opdetm (NOLOCK) INNER JOIN scheme.opheadm  (NOLOCK)  ON scheme.opdetm.order_no = scheme.opheadm.order_no)  
   INNER JOIN scheme.slcustm (NOLOCK) ON scheme.opheadm.customer = scheme.slcustm.customer
   WHERE (scheme.opheadm.date_entered)= CAST(FLOOR(CAST(GETDATE() AS FLOAT))AS DATETIME);

Regards,

TimCottee
0
 
hoatvCommented:
If you want select COST VALUE, the command is:
SELECT scheme.opheadm.order_no, scheme.opheadm.customer, scheme.slcustm.currency, scheme.opheadm.date_entered, scheme.opheadm.territory, 
	scheme.opheadm.carrier_code, scheme.opdetm.order_no, scheme.opdetm.order_line_no, scheme.opdetm.line_type, scheme.opdetm.warehouse, 
	scheme.opdetm.product, scheme.opdetm.unit_of_sale, scheme.opdetm.order_line_status, scheme.opdetm.order_qty, scheme.opdetm.allocated_qty, 
	scheme.opdetm.despatched_qty, scheme.opdetm.val,
	CASE 
      WHEN scheme.opdetm.order_line_status ='B' THEN scheme.opdetm.order_qty
      WHEN scheme.opdetm.allocated_qty>0		THEN scheme.opdetm.allocated_qty
      WHEN scheme.opdetm.despatched_qty>0       THEN scheme.opdetm.despatched_qty
    END
FROM (scheme.opdetm (NOLOCK) INNER JOIN scheme.opheadm  (NOLOCK)  ON scheme.opdetm.order_no = scheme.opheadm.order_no)  
INNER JOIN scheme.slcustm (NOLOCK) ON scheme.opheadm.customer = scheme.slcustm.customer
WHERE (scheme.opheadm.date_entered)= CAST(FLOOR(CAST(GETDATE() AS FLOAT))AS DATETIME);

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.