查询处理器无法生成查询计划，因为需要工作表，且其最小的行大小超出了允许的 %1! 个字节的最大值。需要工作表通常是由于查询中有 GROUP BY 或 ORDER BY 子句.....
请各位大侠帮看一下这是什么问题？
sql.Append(" select *");
sql.Append(" from (");
sql.Append(" select '店面零售' as SellFlag, a.InputDate, a.InputDate as InputDateYM,");
sql.Append(" e.CompanyNo,e.CompanyFullName,(case e.Active when 1 then '是' else '否'end ) 公司可用, d.ShopNo, d.ShopFullName,(case d.Active when 1 then '是' else '否' end)店面可用,");
sql.Append(" f.BigRegionName, f.ProvinceName, f.CityName, f.CountyName, f.TownName,");
sql.Append(" '' as DestCompanyId, '' as DestCompanyName,");
sql.Append(" '' as DestBigRegionName, '' as DestProvinceName, '' as DestCityName, '' as DestCountyName, ");
sql.Append(" '' as DestTownName,");
if (rpSubProductLineId != "-1")
{
sql.Append(" rp.rpSubProductLine as ProductLine,");
}
else
{
sql.Append(" c.ProductLine,");
}
sql.Append(" p.ProductGroupName,b.ProductModel,b.ProductNum,b.ProductType,ps.ProductSeries,(case b.Active when 1 then '是' else '否' end)产品可用,a.SelloutQty as SelloutQty");
sql.Append(" from FactShopSellout a ");
sql.Append(" inner join DimProduct b on a.ProductId = b.ProductId ");
sql.Append(" inner join DimProductLine c on b.ProductLineId = c.ProductLineId ");
sql.Append(" inner join DimRPSubProductLine rp on b.RPSubProductLineId = rp.RPSubProductLineId ");
sql.Append(" inner join DimShop d on a.ShopId = d.ShopId ");
sql.Append(" inner join DimCompany e on d.CompanyId = e.CompanyId and isKP=0 ");
sql.Append(" inner join RelCompanyWith3T r on e.CompanyId = r.CompanyId and rp.RPSubProductLineId=r.RPSubProductLineId and r.Active=1");
sql.Append(" left join DimProductGroup p on b.ProductGroupId=p.ProductGroupId ");
sql.Append(" left join DimProductSeries ps on b.ProductSeriesId = ps.ProductSeriesId ");
sql.Append(" inner join vGeography f on e.TownId = f.TownId ");
sql.Append(" where 1=1 ");
sql.Append(" and a.InputDate BETWEEN '" + beginInputDate + "' and '" + endInputDate + "' ");
sql.Append(" and c.ProductLineId = ");
sql.Append(" (" + productLineId + ") ");
sql.Append(" and b.ProductType IN ");
sql.Append(" (" + ProductType + ") ");
sql.Append(" and b.Active IN ");
sql.Append(" (" + productActive + ") ");
sql.Append(" and b.ProductGroupId IN ");
sql.Append(" (" + productGroup + ") ");
sql.Append(" and isnull(b.ProductSeriesId,'') IN ");
sql.Append(" (" + productSeriesId + ") ");
if (rpSubProductLineId != "-1")
{
sql.Append(" and b.rpSubProductLineId=" + rpSubProductLineId);
}
if (bigRegionId != -1)
{
sql.Append(" and f.BigRegiOnId=" + bigRegionId);
}
if (regionId != -1)
{
sql.Append(" and f.RegiOnId=" + regionId);
}
if (provinceId != -1)
{
sql.Append(" and f.ProvinceId=" + provinceId);
}
if (rtCityId != -1)
{
sql.Append(" and f.CityId=" + rtCityId);
}
if (companyId != -1)
{
sql.Append(" and e.companyId=" + companyId);
}
sql.Append(" union all");
sql.Append(" select '公司批发' as SellFlag, a.InputDate, a.InputDate as InputDateYM, ");
sql.Append(" e.CompanyNo,e.CompanyFullName,(case e.Active when 1 then '是' else '否'end ) 公司可用, '' as ShopNo, '' as ShopFullName,'' as 店面可用,");
sql.Append(" f.BigRegionName, f.ProvinceName, f.CityName, f.CountyName,f.TownName, ");
sql.Append(" i.ResellerNo as DestCompanyId, i.FullName as DestCompanyName,");
sql.Append(" j.BigRegionName as DestBigRegionName, j.ProvinceName as DestProvinceName, j.CityName as DestCityName, ");
sql.Append(" i.CountyName as DestCountyName,i.TownName as DestTownName,");
if (rpSubProductLineId != "-1")
{
sql.Append(" rp.rpSubProductLine as ProductLine,");
}
else
{
sql.Append(" c.ProductLine,");
}
sql.Append(" p.ProductGroupName,b.ProductModel,b.ProductNum,b.ProductType,ps.ProductSeries,(case b.Active when 1 then '是' else '否' end)产品可用, ");
sql.Append(" a.SellthruQty as SelloutQty ");
sql.Append(" from Fact3TCompanySellthru a ");
sql.Append(" inner join DimProduct b on a.ProductId = b.ProductId ");
sql.Append(" inner join DimProductLine c on b.ProductLineId = c.ProductLineId ");
sql.Append(" inner join DimRPSubProductLine rp on b.RPSubProductLineId = rp.RPSubProductLineId ");
sql.Append(" inner join DimCompany e on a.CompanyId = e.CompanyId");
sql.Append(" inner join vGeography f on e.TownId = f.TownId ");
sql.Append(" left join vMixResellerWithDimReseller i on a.DestMixResellerId = i.MixResellerId ");
sql.Append(" left join vCountyGeography j on i.CountyId = j.CountyId ");
sql.Append(" left join RelResellerWith3Ttype k on i.ResellerId = k.ResellerId and b.ProductLineId = k.ProductLineId ");
sql.Append(" left join DimProductGroup p on b.ProductGroupId=p.ProductGroupId ");
sql.Append(" left join DimProductSeries ps on b.ProductSeriesId = ps.ProductSeriesId ");
下面还有一些就是条件了。
京公网安备 11010802041100号