converting if then statement into SQL

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

converting if then statement into SQL

Author

Topic

emilie.lcl
Starting Member

2 Posts

Posted - 2014-07-05 : 12:10:06

Hi there, I have trouble converting the following statement to SQL
Can anyone help?
Thank, Emilie

CheckCC = False
CheckStock = False
CheckStop = False

Do While Not Order_rst.EOF And CheckStop = False
If (Order_rst![Order Number] = Order_Number.Value) Then
If Order_rst.Fields("Product Code") = Product_rst.Fields("Product Code") Then
If Customer_rst.Fields("Customer Number") = Customer_Number.Value Then
If Val(Customer_rst.Fields("Customer Category")) >= Val(Product_rst.Fields("Product Category")) Then
CheckCC = True
If Order_rst.Fields("Quantity") <= Product_rst.Fields("In-stock Quantity") Then
CheckStock = True
Order_rst.MoveNext
Product_rst.MoveFirst
Else
CheckStock = False
CheckStop = True 'indicate to terminate the checking process

MsgBox ("Product: " + Order_rst.Fields("Product Code") + " out of stock!")
End If
Else
CheckCC = False
CheckStop = True

MsgBox ("Customer not allowed to order product: " + Order_rst.Fields("Product Code"))
End If
Customer_rst.MoveFirst
Else
Customer_rst.MoveNext
End If
Else
Product_rst.MoveNext
End If
Else
Order_rst.MoveNext
End If
Loop

Product_rst.Close
Customer_rst.Close
Order_rst.Close

If CheckCC = True And CheckStock = True Then
MsgBox ("Order: " + Order_Number.Value + " OK!")
End If

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2014-07-05 : 13:59:18

Can we have description of the tables in question, sample data, input values and expected output?

emilie.lcl
Starting Member

2 Posts

Posted - 2014-07-05 : 20:13:47

Dear bitsmed,

The question required me to add a Confirmed (data type: Boolean) field to the Order Header table, then rewrite the Click method of the button which checks the current order.The objective is to use SQL statements to replace the loop structures to make your code more efficient. For the orders that are not OK (i.e. either customer is not allowed to order a product, or a product is out of stock), display exactly the same message. However, for orders that are OK, do the following:
1 Check if the order is already confirmed (i.e. the Confirmed field is
Yes) and if so, display the message: ‘Order: (Order Number) already
confirmed.’
2 If the order is not yet confirmed, change the value of the Confirmed
field to Yes.

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2014-07-06 : 05:40:15

Look here: [url]http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx[/url]
Especially the second paragraph is important.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources