Solved

Adding an if statement on the select

Posted on 2008-06-17
2
179 Views
Last Modified: 2010-03-20
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

0
Comment
Question by:meteorelec
2 Comments
 
LVL 43

Accepted Solution

by:
TimCottee earned 500 total points
Comment Utility
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
 
LVL 2

Expert Comment

by:hoatv
Comment Utility
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

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now