Solved

Adding an if statement on the select

Posted on 2008-06-17
2
180 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
ID: 21800867
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
ID: 21800875
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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.

895 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

18 Experts available now in Live!

Get 1:1 Help Now