asked on

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

ASKER CERTIFIED SOLUTION

TimCottee

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

hoatv

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