CASE Statement when ranges involved
in [Programming SQL]
|
From: Terry on 18 Jul 2008 08:15 I would like to convert the following VBA to T-SQL, the problem I have is finding the correct syntax for specifying a range to be tested: Regards VBA------ Select Case Val(ProductGroupNumber) Case 1000 To 1110 Calc_MTA_Product_Groups = "n" Case 1150 To 1160, 1165 To 1201 Calc_MTA_Product_Groups = "k" Case 1161 To 1162, 1350 To 1380, 1700 To 1800, 1915, 1990 Calc_MTA_Product_Groups = "q" Case 1205 To 1320, 1500 To 1530, 1928 Calc_MTA_Product_Groups = "e" Case 1450 To 1470 Calc_MTA_Product_Groups = "h" Case 1475 To 1480 Calc_MTA_Product_Groups = "f" Case 1550 To 1555 Calc_MTA_Product_Groups = "g" Case 1560 Calc_MTA_Product_Groups = "i" Case 1600 Calc_MTA_Product_Groups = "l" Case Else Calc_MTA_Product_Groups = "x" End Select I have tested out the following OK: SELECT Floyd.dbo.StockItem.ItemID AS mtapg_ItemID, Floyd.dbo.StockItem.Code AS mtapg_ItemCode, CONVERT(int, Floyd.dbo.ProductGroup.Code) AS mtapg_ProductGroup, CASE CONVERT(int, Floyd.dbo.ProductGroup.Code) WHEN 1000 THEN 'n' ELSE 'x' END AS mtapg_EquipmentType FROM Floyd.dbo.StockItem INNER JOIN Floyd.dbo.ProductGroup ON Floyd.dbo.StockItem.ProductGroupID = Floyd.dbo.ProductGroup.ProductGroupID
From: Stuart Ainsworth on 18 Jul 2008 08:40 On Jul 18, 8:15 am, "Terry" <i...(a)REMOVETHISfloydautomatic.co.uk> wrote: > I would like to convert the following VBA to T-SQL, the problem I have is > finding the correct syntax for specifying a range to be tested: > > Regards > > VBA------ > Select Case Val(ProductGroupNumber) > Case 1000 To 1110 > Calc_MTA_Product_Groups = "n" > Case 1150 To 1160, 1165 To 1201 > Calc_MTA_Product_Groups = "k" > Case 1161 To 1162, 1350 To 1380, 1700 To 1800, 1915, 1990 > Calc_MTA_Product_Groups = "q" > Case 1205 To 1320, 1500 To 1530, 1928 > Calc_MTA_Product_Groups = "e" > Case 1450 To 1470 > Calc_MTA_Product_Groups = "h" > Case 1475 To 1480 > Calc_MTA_Product_Groups = "f" > Case 1550 To 1555 > Calc_MTA_Product_Groups = "g" > Case 1560 > Calc_MTA_Product_Groups = "i" > Case 1600 > Calc_MTA_Product_Groups = "l" > Case Else > Calc_MTA_Product_Groups = "x" > End Select > > I have tested out the following OK: > > SELECT Floyd.dbo.StockItem.ItemID AS mtapg_ItemID, > Floyd.dbo.StockItem.Code AS mtapg_ItemCode, CONVERT(int, > Floyd.dbo.ProductGroup.Code) > AS mtapg_ProductGroup, CASE CONVERT(int, > Floyd.dbo.ProductGroup.Code) WHEN 1000 THEN 'n' ELSE 'x' END AS > mtapg_EquipmentType > FROM Floyd.dbo.StockItem INNER JOIN > Floyd.dbo.ProductGroup ON > Floyd.dbo.StockItem.ProductGroupID = Floyd.dbo.ProductGroup.ProductGroupID Have you tried looking at Books Online? Not trying to be snarky, but it's probably the most valuable (and least used) tool for SQL Server professionals. Anyway, here's the sample straight from there: USE AdventureWorks; GO SELECT ProductNumber, Name, 'Price Range' = CASE WHEN ListPrice = 0 THEN 'Mfg item - not for resale' WHEN ListPrice < 50 THEN 'Under $50' WHEN ListPrice >= 50 and ListPrice < 250 THEN 'Under $250' WHEN ListPrice >= 250 and ListPrice < 1000 THEN 'Under $1000' ELSE 'Over $1000' END FROM Production.Product ORDER BY ProductNumber ; GO You can also use OR's and AND's to create very complex WHEN clauses: > Case 1161 To 1162, 1350 To 1380, 1700 To 1800, 1915, 1990 > Calc_MTA_Product_Groups = "q" Calc_MTA_Product_Groups = WHEN ProductGroupNumber IN (1161,1162, 1915, 1990) OR ProductGroupNumber BETWEEN 1350 AND 1380 OR ProductGroupNumber BETWEEN 1700 AND 1800 THEN q END HTH, Stu
From: Plamen Ratchev on 18 Jul 2008 08:40 You can use CASE with BETWEEN and IN: CASE WHEN CONVERT(int, Floyd.dbo.ProductGroup.Code) BETWEEN 1000 AND 1110 THEN ... WHEN CONVERT(int, Floyd.dbo.ProductGroup.Code) BETWEEN 1150 AND 1160 AND CONVERT(int, Floyd.dbo.ProductGroup.Code) BETWEEN 1165 AND 1201 THEN ... WHEN CONVERT(int, Floyd.dbo.ProductGroup.Code) BETWEEN 1350 AND 1380 AND CONVERT(int, Floyd.dbo.ProductGroup.Code) BETWEEN 1700 AND 1800 AND CONVERT(int, Floyd.dbo.ProductGroup.Code) IN (1161, 1162, 1915, 1990) THEN ... HTH, Plamen Ratchev http://www.SQLStudio.com
From: Plamen Ratchev on 18 Jul 2008 08:53 The conditions in the different CASE WHEN clauses should have been with OR, not AND of course... CASE WHEN CONVERT(int, Floyd.dbo.ProductGroup.Code) BETWEEN 1000 AND 1110 THEN ... WHEN CONVERT(int, Floyd.dbo.ProductGroup.Code) BETWEEN 1150 AND 1160 OR CONVERT(int, Floyd.dbo.ProductGroup.Code) BETWEEN 1165 AND 1201 THEN ... WHEN CONVERT(int, Floyd.dbo.ProductGroup.Code) BETWEEN 1350 AND 1380 OR CONVERT(int, Floyd.dbo.ProductGroup.Code) BETWEEN 1700 AND 1800 OR CONVERT(int, Floyd.dbo.ProductGroup.Code) IN (1161, 1162, 1915, 1990) THEN ... HTH, Plamen Ratchev http://www.SQLStudio.com
From: Terry on 18 Jul 2008 09:40 Hi Plamen, I guessed it would be something like your example, but it didn't look right with all those CONVERT's. I figured there must have been another way with perhaps just one convertion and then testing against the result of that. The problem I have is that the ProductGroup is a string, luckily for me it's a string representation of a number which makes it easier to handle after conversion. Regards "Plamen Ratchev" <Plamen(a)SQLStudio.com> wrote in message news:2A93E699-0323-4131-B6AA-37EB51A674AB(a)microsoft.com... > The conditions in the different CASE WHEN clauses should have been with > OR, not AND of course... > > CASE WHEN CONVERT(int, Floyd.dbo.ProductGroup.Code) > BETWEEN 1000 AND 1110 > THEN ... > WHEN CONVERT(int, Floyd.dbo.ProductGroup.Code) > BETWEEN 1150 AND 1160 > OR CONVERT(int, Floyd.dbo.ProductGroup.Code) > BETWEEN 1165 AND 1201 > THEN ... > WHEN CONVERT(int, Floyd.dbo.ProductGroup.Code) > BETWEEN 1350 AND 1380 > OR CONVERT(int, Floyd.dbo.ProductGroup.Code) > BETWEEN 1700 AND 1800 > OR CONVERT(int, Floyd.dbo.ProductGroup.Code) IN (1161, 1162, > 1915, 1990) > THEN ... > > HTH, > > Plamen Ratchev > http://www.SQLStudio.com
|
Next
|
Last
Pages: 1 2 3 Prev: Columns returned from Stored Procedure Next: Joining two tables after manipulating a field |