GilaMonster (1/17/2009)
Actually, SQL knows they're numeric. The error comes from trying to concatenate a numeric value into a string. It's trying to convert 'delivered' into numeric, which isn't going to work.
SELECT Orders.CustomerCode,
Orders.Description,
Orders.IsDelivered,
Orders.OrderDate,
Orders.OrderNo,
Orders.RequisitionNo,
Orders.Username,
OrderedItems.DeliveredQty,
CASE
WHEN DeliveredQty = ItemQty THEN 'Delivered'
WHEN DeliveredQty < ItemQty THEN CAST(DeliveredQty AS VARCHAR(10)) + ' items delivered.'
ELSE CAST(DeliveredQty AS VARCHAR(10)) + ' items delivered' + CAST(DeliveredQty - ItemQty AS VARCHAR(10)) + ' consignment'
END AS Remarks
FROM Orders
INNER JOIN OrderedItems ON Orders.OrderNo = OrderedItems.OrderNo
WHERE (Orders.Username = @Username)
hi gilamonster!
thanks for the help, it worked!
one more question,
how can i makE the fields ending with 'Qty' into two decimal places (0.00) ?
thanks 🙂