if else problem - SQL Server Forums

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

SQL Server 2005 Forums

Transact-SQL (2005)

if else problem

Author

Topic

Fury
Starting Member

2 Posts

Posted - 2009-02-04 : 12:27:30

Hi there,

I'm having some trouble with a value calculation of products in a stock app. The possible units of products are piece(stuk), box(doos) and meter.
Products have also a packagequantity so when the unit is piece the calculation of the value would be: price x packagequantity x quantity.
When the unit is box the calculation would be: price x quantity.
This has to be showed in a products list.
So I'm trying to put this in a if else query but I'm getting this error: Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

This is my query:


IF (SELECT ProductSupplier.Unit FROM ProductSupplier) = 'doos'
BEGIN 
SELECT Product.ProductName AS Beschrijving, Product.type, Product.length AS Lengte, 
Product.thermal AS Thermisch, ColorName AS Kleur, 
Color.ColorCode AS Kleurcode, Product.VitraluxCode, Product.Min, Product.Max, SUM(ProductSupplier.Quantity) AS Aantal, 
SUM(ISNULL(Product.Length, 1) * ProductSupplier.Quantity * ProductSupplier.Price) AS Waarde, "
Product.ProductID FROM Product INNER JOIN ProductSupplier ON Product.ProductID = ProductSupplier.ProductID 
LEFT OUTER JOIN Color ON Product.ColorID = Color.ColorID GROUP BY Product.ProductName, 
Product.Type, Product.Length, Product.Thermal, Product.Min, Product.Max, Color.ColorName, Color.ColorCode, Product.VitraluxCode, Product.ProductID
END 
ELSE
BEGIN 
SELECT Product.ProductName AS Beschrijving, Product.type, Product.length AS Lengte, 
Product.thermal AS Thermisch, ColorName AS Kleur, 
Color.ColorCode AS Kleurcode, Product.VitraluxCode, Product.Min, Product.Max, SUM(ProductSupplier.Quantity) AS Aantal, 
SUM(ISNULL(Product.Length, 1) * ProductSupplier.PackageQuantity * ProductSupplier.Quantity * ProductSupplier.Price) AS Waarde, 
Product.ProductID FROM Product INNER JOIN ProductSupplier ON Product.ProductID = ProductSupplier.ProductID 
LEFT OUTER JOIN Color ON Product.ColorID = Color.ColorID GROUP BY Product.ProductName, 
Product.Type, Product.Length, Product.Thermal, Product.Min, Product.Max, Color.ColorName, Color.ColorCode, Product.VitraluxCode, Product.ProductID
END

I don't have much if else sql experience so any help would be much appreciated!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-04 : 12:36:07

you need only this


SELECT Product.ProductName AS Beschrijving, Product.type, Product.length AS Lengte, 
Product.thermal AS Thermisch, ColorName AS Kleur, 
Color.ColorCode AS Kleurcode, Product.VitraluxCode, Product.Min, Product.Max, SUM(ProductSupplier.Quantity) AS Aantal, 
SUM(ISNULL(Product.Length, 1) * ProductSupplier.Quantity * ProductSupplier.Price * CASE WHEN ProductSupplier.Unit = 'doos' THEN 1 ELSE ProductSupplier.PackageQuantity END) AS Waarde, "
Product.ProductID FROM Product INNER JOIN ProductSupplier ON Product.ProductID = ProductSupplier.ProductID 
LEFT OUTER JOIN Color ON Product.ColorID = Color.ColorID GROUP BY Product.ProductName, 
Product.Type, Product.Length, Product.Thermal, Product.Min, Product.Max, Color.ColorName, Color.ColorCode, Product.VitraluxCode, Product.ProductID

[/code]

Fury
Starting Member

2 Posts

Posted - 2009-02-04 : 13:25:54

Thanks you!

It seems that with a if else statement it isn't possible to get a list? Correct me if I'm wrong...

tonymorell10
Yak Posting Veteran

90 Posts

Posted - 2009-02-04 : 16:26:05

You can use a list in an if statement with something like this:


IF 'Tofu' IN (SELECT ProductName FROM dbo.Products) BEGIN
	SELECT 'Yes, we carry Tofu'
END
ELSE BEGIN
	SELECT 'No Tofu here!'
END

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources