April 10, 2008 at 6:25 pm
Hello everyone! I need a little help with a query I am trying to get to work. I am writing a report in visual studio 2003. Need to get the average for some columns. I've found a solution however it's giving me the following error: "Could not allocate ancillary table for view or function resolution. The maximum number of tables in a query (260) was exceeded". I understand the problem but my knowledge in SQL is limited to the basic (as you may see in the upcoming query).
So, the query is:
SELECT FilteredQuote.Name, FilteredQuote.TotalAmount, FilteredQuote.New_MediaDescComercial,
FilteredQuote.New_MximoDescontoComercail, FilteredQuote.New_MediaPorposta, FilteredQuote.New_MximodeReduo,
FilteredQuoteDetail.New_DescontoComercial, FilteredQuoteDetail.New_PerOferta, FilteredQuoteDetail.New_calcPerc,
FilteredQuoteDetail.ManualDiscountAmount, FilteredProduct.Name AS NomeProduto, FilteredAccount.Name AS NomeCliente,
(SELECT AVG(M1.New_MediaDescComercial)
FROM (SELECT FilteredQuote.Name, FilteredQuote.TotalAmount, FilteredQuote.New_MediaDescComercial,
FilteredQuote.New_MximoDescontoComercail, FilteredQuote.New_MediaPorposta,
FilteredQuote.New_MximodeReduo, FilteredQuoteDetail.New_DescontoComercial,
FilteredQuoteDetail.New_PerOferta, FilteredQuoteDetail.New_calcPerc,
FilteredQuoteDetail.ManualDiscountAmount, FilteredProduct.Name AS NomeProduto, FilteredAccount.Name AS NomeCliente
FROM FilteredQuote INNER JOIN
FilteredQuote ON FilteredQuote.QuoteId = FilteredQuote.QuoteId INNER JOIN
FilteredQuoteDetail ON FilteredQuote.QuoteId = FilteredQuoteDetail.QuoteId INNER JOIN
FilteredQuoteDetail ON FilteredQuoteDetail.QuoteDetailId = FilteredQuoteDetail.QuoteDetailId INNER JOIN
FilteredProduct ON FilteredQuoteDetail.ProductId = FilteredProduct.ProductId INNER JOIN
FilteredAccount ON FilteredQuote.AccountId = FilteredAccount.AccountId
WHERE (CONVERT(char, FilteredQuote.New_Tipologiaclassificao) LIKE @TipoClassificacao) AND
(FilteredQuote.New_VendaCruzada = @VendaCruzada) AND (FilteredQuote.CreatedOn BETWEEN @DataDe AND
@DataAte) AND (FilteredQuote.owninguser LIKE @GestComercial) AND (CONVERT(char, FilteredAccount.IndustryCode)
LIKE @CodSector) AND (CONVERT(char, FilteredQuote.New_GestCliente) LIKE @GestCliente) AND
(FilteredProduct.Name LIKE @TpProduto)
GROUP BY FilteredQuote.Name, FilteredQuote.TotalAmount, FilteredQuote.New_MediaDescComercial,
FilteredQuote.New_MximoDescontoComercail, FilteredQuote.New_MediaPorposta,
FilteredQuote.New_MximodeReduo, FilteredQuoteDetail.New_DescontoComercial,
FilteredQuoteDetail.New_PerOferta, FilteredQuoteDetail.New_calcPerc,
FilteredQuoteDetail.ManualDiscountAmount, FilteredProduct.Name, FilteredAccount.Name) M1) AS TotalMediaComercial,
(SELECT AVG(M2.New_MximoDescontoComercail)
FROM (SELECT FilteredQuote.Name, FilteredQuote.TotalAmount, FilteredQuote.New_MediaDescComercial,
FilteredQuote.New_MximoDescontoComercail, FilteredQuote.New_MediaPorposta,
FilteredQuote.New_MximodeReduo, FilteredQuoteDetail.New_DescontoComercial,
FilteredQuoteDetail.New_PerOferta, FilteredQuoteDetail.New_calcPerc,
FilteredQuoteDetail.ManualDiscountAmount, FilteredProduct.Name AS NomeProduto, FilteredAccount.Name AS NomeCliente
FROM FilteredQuote INNER JOIN
FilteredQuote ON FilteredQuote.QuoteId = FilteredQuote.QuoteId INNER JOIN
FilteredQuoteDetail ON FilteredQuote.QuoteId = FilteredQuoteDetail.QuoteId INNER JOIN
FilteredQuoteDetail ON FilteredQuoteDetail.QuoteDetailId = FilteredQuoteDetail.QuoteDetailId INNER JOIN
FilteredProduct ON FilteredQuoteDetail.ProductId = FilteredProduct.ProductId INNER JOIN
FilteredAccount ON FilteredQuote.AccountId = FilteredAccount.AccountId
WHERE (CONVERT(char, FilteredQuote.New_Tipologiaclassificao) LIKE @TipoClassificacao) AND
(FilteredQuote.New_VendaCruzada = @VendaCruzada) AND (FilteredQuote.CreatedOn BETWEEN @DataDe AND
@DataAte) AND (FilteredQuote.owninguser LIKE @GestComercial) AND (CONVERT(char, FilteredAccount.IndustryCode)
LIKE @CodSector) AND (CONVERT(char, FilteredQuote.New_GestCliente) LIKE @GestCliente) AND
(FilteredProduct.Name LIKE @TpProduto)
GROUP BY FilteredQuote.Name, FilteredQuote.TotalAmount, FilteredQuote.New_MediaDescComercial,
FilteredQuote.New_MximoDescontoComercail, FilteredQuote.New_MediaPorposta,
FilteredQuote.New_MximodeReduo, FilteredQuoteDetail.New_DescontoComercial,
FilteredQuoteDetail.New_PerOferta, FilteredQuoteDetail.New_calcPerc,
FilteredQuoteDetail.ManualDiscountAmount, FilteredProduct.Name, FilteredAccount.Name) M2) AS MedMaxDescComercial,
(SELECT AVG(M3.New_MediaPorposta)
FROM (SELECT FilteredQuote.Name, FilteredQuote.TotalAmount, FilteredQuote.New_MediaDescComercial,
FilteredQuote.New_MximoDescontoComercail, FilteredQuote.New_MediaPorposta,
FilteredQuote.New_MximodeReduo, FilteredQuoteDetail.New_DescontoComercial,
FilteredQuoteDetail.New_PerOferta, FilteredQuoteDetail.New_calcPerc,
FilteredQuoteDetail.ManualDiscountAmount, FilteredProduct.Name AS NomeProduto, FilteredAccount.Name AS NomeCliente
FROM FilteredQuote INNER JOIN
FilteredQuote ON FilteredQuote.QuoteId = FilteredQuote.QuoteId INNER JOIN
FilteredQuoteDetail ON FilteredQuote.QuoteId = FilteredQuoteDetail.QuoteId INNER JOIN
FilteredQuoteDetail ON FilteredQuoteDetail.QuoteDetailId = FilteredQuoteDetail.QuoteDetailId INNER JOIN
FilteredProduct ON FilteredQuoteDetail.ProductId = FilteredProduct.ProductId INNER JOIN
FilteredAccount ON FilteredQuote.AccountId = FilteredAccount.AccountId
WHERE (CONVERT(char, FilteredQuote.New_Tipologiaclassificao) LIKE @TipoClassificacao) AND
(FilteredQuote.New_VendaCruzada = @VendaCruzada) AND (FilteredQuote.CreatedOn BETWEEN @DataDe AND
@DataAte) AND (FilteredQuote.owninguser LIKE @GestComercial) AND (CONVERT(char, FilteredAccount.IndustryCode)
LIKE @CodSector) AND (CONVERT(char, FilteredQuote.New_GestCliente) LIKE @GestCliente) AND
(FilteredProduct.Name LIKE @TpProduto)
GROUP BY FilteredQuote.Name, FilteredQuote.TotalAmount, FilteredQuote.New_MediaDescComercial,
FilteredQuote.New_MximoDescontoComercail, FilteredQuote.New_MediaPorposta,
FilteredQuote.New_MximodeReduo, FilteredQuoteDetail.New_DescontoComercial,
FilteredQuoteDetail.New_PerOferta, FilteredQuoteDetail.New_calcPerc,
FilteredQuoteDetail.ManualDiscountAmount, FilteredProduct.Name, FilteredAccount.Name) M3) AS MedPropostas,
(SELECT AVG(M4.New_MximodeReduo)
FROM (SELECT FilteredQuote.Name, FilteredQuote.TotalAmount, FilteredQuote.New_MediaDescComercial,
FilteredQuote.New_MximoDescontoComercail, FilteredQuote.New_MediaPorposta,
FilteredQuote.New_MximodeReduo, FilteredQuoteDetail.New_DescontoComercial,
FilteredQuoteDetail.New_PerOferta, FilteredQuoteDetail.New_calcPerc,
FilteredQuoteDetail.ManualDiscountAmount, FilteredProduct.Name AS NomeProduto, FilteredAccount.Name AS NomeCliente
FROM FilteredQuote INNER JOIN
FilteredQuote ON FilteredQuote.QuoteId = FilteredQuote.QuoteId INNER JOIN
FilteredQuoteDetail ON FilteredQuote.QuoteId = FilteredQuoteDetail.QuoteId INNER JOIN
FilteredQuoteDetail ON FilteredQuoteDetail.QuoteDetailId = FilteredQuoteDetail.QuoteDetailId INNER JOIN
FilteredProduct ON FilteredQuoteDetail.ProductId = FilteredProduct.ProductId INNER JOIN
FilteredAccount ON FilteredQuote.AccountId = FilteredAccount.AccountId
WHERE (CONVERT(char, FilteredQuote.New_Tipologiaclassificao) LIKE @TipoClassificacao) AND
(FilteredQuote.New_VendaCruzada = @VendaCruzada) AND (FilteredQuote.CreatedOn BETWEEN @DataDe AND
@DataAte) AND (FilteredQuote.owninguser LIKE @GestComercial) AND (CONVERT(char, FilteredAccount.IndustryCode)
LIKE @CodSector) AND (CONVERT(char, FilteredQuote.New_GestCliente) LIKE @GestCliente) AND
(FilteredProduct.Name LIKE @TpProduto)
GROUP BY FilteredQuote.Name, FilteredQuote.TotalAmount, FilteredQuote.New_MediaDescComercial,
FilteredQuote.New_MximoDescontoComercail, FilteredQuote.New_MediaPorposta,
FilteredQuote.New_MximodeReduo, FilteredQuoteDetail.New_DescontoComercial,
FilteredQuoteDetail.New_PerOferta, FilteredQuoteDetail.New_calcPerc,
FilteredQuoteDetail.ManualDiscountAmount, FilteredProduct.Name, FilteredAccount.Name) M4) AS MedMaxReducoes
FROM FilteredQuote INNER JOIN
FilteredQuote ON FilteredQuote.QuoteId = FilteredQuote.QuoteId INNER JOIN
FilteredQuoteDetail ON FilteredQuote.QuoteId = FilteredQuoteDetail.QuoteId INNER JOIN
FilteredQuoteDetail ON FilteredQuoteDetail.QuoteDetailId = FilteredQuoteDetail.QuoteDetailId INNER JOIN
FilteredProduct ON FilteredQuoteDetail.ProductId = FilteredProduct.ProductId INNER JOIN
FilteredAccount ON FilteredQuote.AccountId = FilteredAccount.AccountId
WHERE (CONVERT(char, FilteredQuote.New_Tipologiaclassificao) LIKE @TipoClassificacao) AND
(FilteredQuote.New_VendaCruzada = @VendaCruzada) AND (FilteredQuote.CreatedOn BETWEEN @DataDe AND @DataAte) AND
(FilteredQuote.owninguser LIKE @GestComercial) AND (CONVERT(char, FilteredAccount.IndustryCode) LIKE @CodSector) AND (CONVERT(char,
FilteredQuote.New_GestCliente) LIKE @GestCliente) AND (FilteredProduct.Name LIKE @TpProduto)
GROUP BY FilteredQuote.Name, FilteredQuote.TotalAmount, FilteredQuote.New_MediaDescComercial, FilteredQuote.New_MximoDescontoComercail,
FilteredQuote.New_MediaPorposta, FilteredQuote.New_MximodeReduo, FilteredQuoteDetail.New_DescontoComercial,
FilteredQuoteDetail.New_PerOferta, FilteredQuoteDetail.New_calcPerc, FilteredQuoteDetail.ManualDiscountAmount, FilteredProduct.Name,
FilteredAccount.Name
Any sugestion would be apreciated.
Thanks.
April 21, 2008 at 4:18 pm
For those that might have the same problem i got the solution through the use of temporary tables.
CREATE procedure usr_AnaliseComercial(@TpClassificacao nvarchar(300), @VendaCruzada char(1), @DataDe datetime, @DataAte Datetime, @GestComercial nvarchar(300), @CodSector nvarchar(300), @CodEstado nvarchar(300), @NomeProduto nvarchar(300))
as
CREATE TABLE #Main (
NomeCotacao nvarchar(300),
QantTotal money,
MedDescComercial float(8),
MaxDescComercial float(8),
MediaProposta float(8),
MaxReducoes float(8),
DescontoComercial float(8),
PercOferta float(8),
CalcPert float(8),
QtDescManual money,
NomeProduto nvarchar(300),
NomeCliente nvarchar(300)
)
INSERT INTO #Main
exec('SELECT FilteredQuote.Name, FilteredQuote.TotalAmount, FilteredQuote.New_MediaDescComercial,
FilteredQuote.New_MximoDescontoComercail, FilteredQuote.New_MediaPorposta, FilteredQuote.New_MximodeReduo,
FilteredQuoteDetail.New_DescontoComercial, FilteredQuoteDetail.New_PerOferta, FilteredQuoteDetail.New_calcPerc,
FilteredQuoteDetail.ManualDiscountAmount, FilteredProduct.Name AS NomeProduto, FilteredAccount.Name AS NomeCliente
FROM FilteredQuote INNER JOIN
FilteredQuoteDetail ON FilteredQuote.QuoteId = FilteredQuoteDetail.QuoteId INNER JOIN
FilteredProduct ON FilteredQuoteDetail.ProductId = FilteredProduct.ProductId INNER JOIN
FilteredAccount ON FilteredQuote.AccountId = FilteredAccount.AccountId
WHERE (CONVERT(char, FilteredQuote.New_Tipologiaclassificao) LIKE ''' + @TpClassificacao + ''') AND
(FilteredQuote.New_VendaCruzada = ' + @VendaCruzada + ') AND
(FilteredQuote.CreatedOn BETWEEN ''' + @DataDe + ''' AND ''' + @DataAte + ''') AND
(FilteredQuote.OwningUser LIKE ''' + @GestComercial + ''') AND
(CONVERT(char, FilteredQuote.new_secactividade) LIKE ''' + @CodSector + ''') AND
(CONVERT(char, FilteredQuote.statecode) LIKE ''' + @CodEstado + ''') AND
(CONVERT(char, FilteredProduct.Name) LIKE ''' + @NomeProduto + ''')
GROUP BY FilteredQuote.Name, FilteredQuote.TotalAmount, FilteredQuote.New_MediaDescComercial,
FilteredQuote.New_MximoDescontoComercail, FilteredQuote.New_MediaPorposta, FilteredQuote.New_MximodeReduo,
FilteredQuoteDetail.New_DescontoComercial, FilteredQuoteDetail.New_PerOferta, FilteredQuoteDetail.New_calcPerc,
FilteredQuoteDetail.ManualDiscountAmount, FilteredProduct.Name, FilteredAccount.Name')
--Media 1
CREATE TABLE #TotalMediaComercial (
TotalMediaComercial float(8)
)
INSERT INTO #TotalMediaComercial
SELECT ROUND(AVG(MedDescComercial), 2) FROM #MAIN
--Media 2
CREATE TABLE #TotalMedMaxDescComercial(
TotalMedMaxDescComercial float(8)
)
INSERT INTO #TotalMedMaxDescComercial
SELECT ROUND(AVG(MaxDescComercial), 2) FROM #MAIN
--Media 3 M3.New_MediaPorposta - MedPropostas,
CREATE TABLE #TotalMedProposta (
TotalMedProposta float(8)
)
INSERT INTO #TotalMedProposta
SELECT ROUND(AVG(MediaProposta), 2) FROM #MAIN
--Media 4 - M4.New_MximodeReduo - MedMaxReducoes
CREATE TABLE #TotalMedMaxReducoes (
TotalMedMaxReducoes float(8)
)
INSERT INTO #TotalMedMaxReducoes
SELECT ROUND(AVG(MedDescComercial), 2) FROM #MAIN
SELECT * FROM #MAIN CROSS JOIN #TotalMediaComercial CROSS JOIN #TotalMedMaxDescComercial CROSS JOIN #TotalMedProposta CROSS JOIN #TotalMedMaxReducoes
DROP TABLE #MAIN
DROP TABLE #TotalMediaComercial
DROP TABLE #TotalMedMaxDescComercial
DROP TABLE #TotalMedProposta
DROP TABLE #TotalMedMaxReducoes
GO
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply