prathibha_aviator (10/28/2012)

---

Customer

CustomerID CustomerName Birthdate
1 John Doe 1/1/1970 08:31 AM
2 Jane Doe 1/1/1971 01:18 PM
3 Jon Public 1/1/1972 11:58 PM
4 Jane Public 1/1/1973 07:00 AM
5 John Smith 1/1/1974 08:31 AM
Order

OrderID PO Number OrderDate
1000 ABC123 1/1/2012 01:00 PM
2000 112233 2/1/2012 02:00 AM
3000 XYZ987 3/1/2012 03:00 PM
4000 50000 4/1/2012 04:00 AM
5000 Verbal 5/1/2012 05:00 AM
CustomerOrders

CustomerID OrderID IsShipped
1 1000 False
1 3000 True
3 4000 False
2 2000 True
5 5000 True

Question is

Provide the sql statement that will return the PO Number if the CustomerID is greater than 3 and the OrderID if the CustomerID is 3 or less.

prathibha_aviator (10/29/2012)

---

Thanks for the reply I tried using this and got some error

Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the nvarchar value 'Verbal' to data type int.

Do i need to use cast or convert functions..?

BriPan (10/28/2012)

---

Try this One

select 
* -- What ever column you want
,(case when Customer.CustomerID >3 then Order.PO else Order.OrderID end) as 'PO/OrderID'
from
Customer 
inner join CustomerOrders on Customer.CustomerID =CustomerOrders.CustomerID
inner join Order on CustomerOrders.OrderID =Order.OrderID

prathibha_aviator (10/29/2012)

---

getting an error saying converting the field value 'verbal' to int is impossible... Why is it bothered about the datatypes of the same column when we r using select statement??? Select just returns the values right??