Mango_Lier
Posts: 110
|
| Posted: 08/11/2007, 8:51 PM |
|
Couple of issues found in the 3.1.1.0.
1) the SQL below is valid, it executes in MS Access but it does not execute in the Visual Query Builder. I have no idea why:
SELECT Name, Product.Title AS Product, Sales, Target, SalesAndTargets.Sales-SalesAndTargets.Target AS [Delta], IIf([Delta]>0,Delta*1000,100) AS [Target Points],
[Target Points]+CompanyPoints.DisplayPoints+CompanyPoints.BonusPoints+CompanyPoints.TopPoints+CompanyPoints.DiscretionaryPoints AS [Total Points]
FROM (SalesAndTargets INNER JOIN (Shop INNER JOIN (DataPoint INNER JOIN CompanyPoints ON
DataPoint.DataPoint_id = CompanyPoints.DataPointID) ON
Shop.Shop_id = DataPoint.ShopID) ON
SalesAndTargets.DataPointID = DataPoint.DataPoint_id) INNER JOIN Product ON
SalesAndTargets.ProductID = Product.Product_id
WHERE Shop.Shop_id IN (SELECT DISTINCT Shop_id
FROM PromotionCity INNER JOIN ((Shop INNER JOIN (ShopCategory INNER JOIN (PromotionShopCategory INNER JOIN Promotion ON
PromotionShopCategory.PromotionID = Promotion.Promotion_id) ON
ShopCategory.ShopCategory_id = PromotionShopCategory.ShopCategoryID) ON
Shop.CategoryID = ShopCategory.ShopCategory_id) INNER JOIN City ON
Shop.CityID = City.City_id) ON
PromotionCity.CityID = City.City_id
WHERE Promotion.Promotion_id = 1)
AND Product.Product_id IN (SELECT Product_id
FROM (PromotionProduct INNER JOIN Product ON
PromotionProduct.ProductID = Product.Product_id) INNER JOIN Promotion ON
PromotionProduct.PromotionID = Promotion.Promotion_id
WHERE Promotion.Promotion_id = 1)
2) It seems that in VQB if you select to add WHERE clause and use the Expression option, the length of the edit control prevents entering long expressions. For example the following nested select query is truncated
SELECT DISTINCT Shop_id
FROM PromotionCity INNER JOIN ((Shop INNER JOIN (ShopCategory INNER JOIN (PromotionShopCategory
INNER JOIN Promotion ON
PromotionShopCategory.PromotionID = Promotion.Promotion_id) ON
ShopCategory.ShopCategory_id = PromotionShopCategory.ShopCategoryID) ON
Shop.CategoryID = ShopCategory.ShopCategory_id) INNER JOIN City ON
Shop.CityID = City.City_id) ON
PromotionCity.CityID = City.City_id
WHERE Promotion.Promotion_id = {Promotion_id}
|
 |
 |
|
