Solved

Adding an if statement on the select

Posted on 2008-06-17
2
184 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

MIM Survival Guide for Service Desk Managers

Major incidents can send mastered service desk processes into disorder. Systems and tools produce the data needed to resolve these incidents, but your challenge is getting that information to the right people fast. Check out the Survival Guide and begin bringing order to chaos.

Question has a verified solution.

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

Suggested Solutions

INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
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. …
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

820 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