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.
| Author |
Topic |
|
drhall99
Starting Member
2 Posts |
 Posted - 2007-11-08 : 22:58:02
|
| I'm having a problem with a case statement that I imbeded a select statement into. I shortened the example below but there are so many values to account for that I get the following error when the query is full sized - "Your SQL is invalid: Too many table names in the query. The maximum allowable is 256." I need a way to remove the imbeded SELECT from the case statement. Here are the values. The reason for the select is to grab only the first value and not the rest.categoryids99,21151,3,150,1,99150,1150,1151,3,150,1,99151,3,150,1,99151,99(there are nearly 300 when values).SELECTCase When (SELECT TOP 1 CategoryID FROM Categories_Products_Link WHERE ProductID = p.ProductID ORDER BY ID) = 1 Then '13000000' When (SELECT TOP 1 CategoryID FROM Categories_Products_Link WHERE ProductID = p.ProductID ORDER BY ID) = 66 Then '13170100' When (SELECT TOP 1 CategoryID FROM Categories_Products_Link WHERE ProductID = p.ProductID ORDER BY ID) = 67 Then '25001000' When (SELECT TOP 1 CategoryID FROM Categories_Products_Link WHERE ProductID = p.ProductID ORDER BY ID) = 68 Then '31000000' When (SELECT TOP 1 CategoryID FROM Categories_Products_Link WHERE ProductID = p.ProductID ORDER BY ID) = 84 Then '13170100' When (SELECT TOP 1 CategoryID FROM Categories_Products_Link WHERE ProductID = p.ProductID ORDER BY ID) = 150 Then '16000000' When (SELECT TOP 1 CategoryID FROM Categories_Products_Link WHERE ProductID = p.ProductID ORDER BY ID) = 152 Then '13090200' When (SELECT TOP 1 CategoryID FROM Categories_Products_Link WHERE ProductID = p.ProductID ORDER BY ID) = 198 Then '25000500' When (SELECT TOP 1 CategoryID FROM Categories_Products_Link WHERE ProductID = p.ProductID ORDER BY ID) = 227 Then '24' When (SELECT TOP 1 CategoryID FROM Categories_Products_Link WHERE ProductID = p.ProductID ORDER BY ID) = 88 Then '12050100' ELSE '13000000' END AS category, pe.ProductManufacturer AS Manufacturer, substring(p.ProductName,1,95) AS Title, substring(pd.ProductDescriptionShort,1,995) AS [stripHTML-Description], 'Config_FullStoreURLProductDetails.asp?ProductCode=' + , IsNull(pe.Fixed_ShippingCost(IsNull(pe.EstShip_Ground,0))) AS Shipcost, Case when pe.yahoo_category LIKE '%BR=0%' then 0 when pe.yahoo_category LIKE '%BR=ACCY%' then 0 When pe.ProductPrice < 40 then 0 ELSE NULL End as Bid , Case when pe.yahoo_medium LIKE '%MM=0%' then 1 when pe.yahoo_medium LIKE '%MM=41,0%' then '41' ELSE '1' End as PromoText, pe.ProductPrice AS Price, pe.CustomField1 AS [stripHTML-AStockStatus], pe.yahoo_category AS List FROM Products p INNER JOIN Products_Descriptions pd ON p.ProductID = pd.ProductIDINNER JOIN Products_Extended pe ON pd.ProductID = pe.ProductID WHERE ((p.HideProduct is NULL OR p.HideProduct <> 'Y') AND ((pe.yahoo_category LIKE '%BR=Y%' OR pe.yahoo_category LIKE '%BR=0%') OR pe.yahoo_category LIKE '%BR=ACCY%')) AND ((pe.CustomField1 LIKE '%In Stock%' OR pe.CustomField1 LIKE '%In Stock%') OR ((pe.CustomField1 LIKE '%In Stock!%' OR pe.CustomField1 LIKE '%In Stock!%') OR (pe.CustomField1 LIKE '%Upgrade%' OR pe.CustomField1 LIKE '%Substitute%')) AND pe.ProductPrice > 10) |
|
|
Koji Matsumura
Posting Yak Master
141 Posts |
Posted - 2007-11-09 : 00:11:28
|
| SelectCase (SELECT TOP 1 CategoryID FROM Categories_Products_Link WHERE ProductID = p.ProductID ORDER BY ID) When 1 Then '13000000' When 66 Then '13170100' When 67 Then '25001000' When 68 Then '31000000' When 84 Then '13170100' When 150 Then '16000000' When 152 Then '13090200' When 198 Then '25000500' When 227 Then '24' When 88 Then '12050100' ELSE '13000000' END AS category,................ |
 |
|
|
drhall99
Starting Member
2 Posts |
Posted - 2007-11-09 : 03:22:06
|
| That worked perfectly. Thanks for your help! I figured that I wa making a simple mistake but I couldn't see it. |
|
|
|
|
|
|
|
|
Problems with case statement -