Jump to content
diego_10

Visual Studio 2010 + DataSet

Recommended Posts

diego_10

Boas,

Tenho um Data Set Criado com varias tables, agora estou a tentar criar uma nova com uma query  e escolho a opcçao Query builder, quando coloco a primeira vez a query e faço executar ele da-me um erro "The OVER SQL construct or statement is not supported", mas quando faço executar a segunda vez ja me retorna os resultados trabalhados no SQL,  faço ok e faço next, depois retiro o Fill a date Table e o Return a DateTable e quando faço next da-me novamente o erro "The OVER SQL construct or statement is not supported" e nao me cria a tabela.

Aqui esta a Query:


Select top5.*,l.Nome as Linha
,(
Select top 1 a.Descricao from Paragem as p
Left outer join Avaria as a 
on p.IDAvaria = a.IDAvaria
WHERE p.IDLinha=top5.IDLinha and p.Data >= Cast('2011-05-27' as Date) and p.Data <= Cast('2011-05-27' as Date) and a.IDZonaAvaria in (2,3,4)
order by p.Minutos desc
) as avaria1

,(	Select tot.Minutos from(
Select top 1 a.descricao, Sum(p.Minutos) as Minutos from Paragem as p
Left outer join Avaria as a 
on p.IDAvaria = a.IDAvaria
WHERE p.IDLinha=top5.IDLinha and p.Data >= Cast('2011-05-27' as Date) and p.Data <= Cast('2011-05-27' as Date) and a.IDZonaAvaria in(2,3,4)
Group by a.Descricao
order by Minutos desc) as tot

) as minutos1
, (
SELECT x.Descricao FROM(
Select top 2 ROW_NUMBER() OVER(order by Sum(Minutos) desc) AS rnumber, a.descricao from Paragem as p
Left outer join Avaria as a 
on p.IDAvaria = a.IDAvaria
WHERE p.IDLinha=top5.IDLinha and p.Data >= Cast('2011-05-27' as Date) and p.Data <= Cast('2011-05-27' as Date) and a.IDZonaAvaria in(2,3,4)
Group by a.Descricao
) AS x
WHERE x.rnumber=2
) AS avaria2
, (
SELECT x.Minu FROM(
Select top 2 ROW_NUMBER() OVER(order by Sum(Minutos) desc) AS rnumber, a.descricao,Sum(Minutos) as Minu from Paragem as p
Left outer join Avaria as a 
on p.IDAvaria = a.IDAvaria
WHERE p.IDLinha=top5.IDLinha and p.Data >= Cast('2011-05-27' as Date) and p.Data <= Cast('2011-05-27' as Date) and a.IDZonaAvaria in(2,3,4)
Group by a.Descricao
) AS x
WHERE x.rnumber=2
) AS minutos2
, (
SELECT x.Descricao FROM(
Select top 3 ROW_NUMBER() OVER(order by Sum(Minutos) desc) AS rnumber, a.descricao from Paragem as p
Left outer join Avaria as a 
on p.IDAvaria = a.IDAvaria
WHERE p.IDLinha=top5.IDLinha and p.Data >= Cast('2011-05-27' as Date) and p.Data <= Cast('2011-05-27' as Date) and a.IDZonaAvaria in(2,3,4)
Group by a.Descricao
) AS x
WHERE x.rnumber=3
) AS avaria3
, (
SELECT x.Minu FROM(
Select top 3 ROW_NUMBER() OVER(order by Sum(Minutos) desc) AS rnumber, a.descricao,Sum(Minutos) as Minu from Paragem as p
Left outer join Avaria as a 
on p.IDAvaria = a.IDAvaria
WHERE p.IDLinha=top5.IDLinha and p.Data >= Cast('2011-05-27' as Date) and p.Data <= Cast('2011-05-27' as Date) and a.IDZonaAvaria in(2,3,4)
Group by a.Descricao
) AS x
WHERE x.rnumber=3
) AS minutos3
from (
/* LINHAS */
SELECT 0 AS IDProjecto, base.IDLinha , base.IDUap 
,turno1.pecasembaladas AS pecasturno1, turno1.defeitos AS defeitosturno1, turno1.perc AS percturno1
,turno2.pecasembaladas AS pecasturno2, turno2.defeitos AS defeitosturno2, turno2.perc AS percturno2
,turno3.pecasembaladas AS pecasturno3, turno3.defeitos AS defeitosturno3, turno3.perc AS percturno3
,total.pecasembaladas AS pecastotal, total.defeitos AS defeitostotal, total.perc AS perctotal
FROM (
SELECT DISTINCT pp.IDLinha,uapl.IDUap
FROM PecasProduzidas AS pp
INNER JOIN UAPLinha AS uapl ON pp.IDLinha = uapl.IDLinha
where pp.Data >= Cast('2011-05-27' as Date) and pp.Data <= Cast('2011-05-27' as Date) 
) AS base
LEFT OUTER JOIN (
SELECT a.IDLinha,a.iduap,a.pecasembaladas,b.defeitos, (CASE WHEN pecasembaladas>0 AND defeitos>0 THEN ((CAST(defeitos AS DECIMAL(18,3))*100) / CAST(pecasembaladas AS DECIMAL(18,3))) ELSE 0 END) AS perc FROM (
	SELECT pp.IDLinha,uapl.IDUap,SUM(pp.TotalPecasEmbaladas) AS pecasembaladas
	FROM PecasProduzidas AS pp
	INNER JOIN UAPLinha AS uapl ON pp.IDLinha = uapl.IDLinha
	WHERE pp.IDTurno = 1 and pp.Data >= Cast('2011-05-27' as Date) and pp.Data <= Cast('2011-05-27' as Date) 
	GROUP BY pp.IDLinha,uapl.IDUap
) AS a
LEFT OUTER JOIN (
/**/
	SELECT pp.IDLinha,uapl.IDUap,SUM(qd.QuantidadePecas) AS defeitos
	FROM PecasProduzidas AS pp
	INNER JOIN UAPLinha AS uapl ON pp.IDLinha = uapl.IDLinha
	LEFT OUTER JOIN QuantidadeDefeitos AS qd ON pp.IDPecasProduzidas = qd.IDPecasProduzidas
	WHERE pp.IDTurno = 1 and pp.Data >= Cast('2011-05-27' as Date) and pp.Data <= Cast('2011-05-27' as Date)  
	GROUP BY pp.IDLinha,uapl.IDUap
) AS b
ON a.IDLinha=b.IDLinha AND a.IDUap = b.IDUap
) AS turno1
ON base.IDLinha = turno1.IDLinha AND base.IDUap = turno1.IDUap
LEFT OUTER JOIN (
SELECT a.IDLinha,a.iduap,a.pecasembaladas,b.defeitos, (CASE WHEN pecasembaladas>0 AND defeitos>0 THEN ((CAST(defeitos AS DECIMAL(18,3))*100) / CAST(pecasembaladas AS DECIMAL(18,3))) ELSE 0 END) AS perc FROM (
	SELECT pp.IDLinha,uapl.IDUap,SUM(pp.TotalPecasEmbaladas) AS pecasembaladas
	FROM PecasProduzidas AS pp
	INNER JOIN UAPLinha AS uapl ON pp.IDLinha = uapl.IDLinha
	WHERE pp.IDTurno = 2 and pp.Data >= Cast('2011-05-27' as Date) and pp.Data <= Cast('2011-05-27' as Date)  
	GROUP BY pp.IDLinha,uapl.IDUap
) AS a
LEFT OUTER JOIN (
	SELECT pp.IDLinha,uapl.IDUap,SUM(qd.QuantidadePecas) AS defeitos
	FROM PecasProduzidas AS pp
	INNER JOIN UAPLinha AS uapl ON pp.IDLinha = uapl.IDLinha
	LEFT OUTER JOIN QuantidadeDefeitos AS qd ON pp.IDPecasProduzidas = qd.IDPecasProduzidas
	WHERE pp.IDTurno = 2 and pp.Data >= Cast('2011-05-27' as Date) and pp.Data <= Cast('2011-05-27' as Date)  
	GROUP BY pp.IDLinha,uapl.IDUap
) AS b
ON a.IDLinha=b.IDLinha AND a.IDUap = b.IDUap
) AS turno2
ON base.IDLinha = turno2.IDLinha AND base.IDUap = turno2.IDUap
LEFT OUTER JOIN (
SELECT a.IDLinha,a.iduap,a.pecasembaladas,b.defeitos, (CASE WHEN pecasembaladas>0 AND defeitos>0 THEN ((CAST(defeitos AS DECIMAL(18,3))*100) / CAST(pecasembaladas AS DECIMAL(18,3))) ELSE 0 END) AS perc FROM (
	SELECT pp.IDLinha,uapl.IDUap,SUM(pp.TotalPecasEmbaladas) AS pecasembaladas
	FROM PecasProduzidas AS pp
	INNER JOIN UAPLinha AS uapl ON pp.IDLinha = uapl.IDLinha
	WHERE pp.IDTurno = 3 and pp.Data >= Cast('2011-05-27' as Date) and pp.Data <= Cast('2011-05-27' as Date) 
	GROUP BY pp.IDLinha,uapl.IDUap
) AS a
LEFT OUTER JOIN (
	SELECT pp.IDLinha,uapl.IDUap,SUM(qd.QuantidadePecas) AS defeitos
	FROM PecasProduzidas AS pp
	INNER JOIN UAPLinha AS uapl ON pp.IDLinha = uapl.IDLinha
	LEFT OUTER JOIN QuantidadeDefeitos AS qd ON pp.IDPecasProduzidas = qd.IDPecasProduzidas
	WHERE pp.IDTurno = 3 and pp.Data >= Cast('2011-05-27' as Date) and pp.Data <= Cast('2011-05-27' as Date)  
	GROUP BY pp.IDLinha,uapl.IDUap
) AS b
ON a.IDLinha=b.IDLinha AND a.IDUap = b.IDUap
) AS turno3 
ON base.IDLinha = turno3.IDLinha AND base.IDUap = turno3.IDUap
LEFT OUTER JOIN (
SELECT a.IDLinha,a.iduap,a.pecasembaladas,b.defeitos, (CASE WHEN pecasembaladas>0 AND defeitos>0 THEN ((CAST(defeitos AS DECIMAL(18,3))*100) / CAST(pecasembaladas AS DECIMAL(18,3))) ELSE 0 END) AS perc FROM (
	SELECT pp.IDLinha,uapl.IDUap,SUM(pp.TotalPecasEmbaladas) AS pecasembaladas
	FROM PecasProduzidas AS pp
	INNER JOIN UAPLinha AS uapl ON pp.IDLinha = uapl.IDLinha
	where pp.Data >= Cast('2011-05-27' as Date) and pp.Data <= Cast('2011-05-27' as Date)  
	GROUP BY pp.IDLinha,uapl.IDUap
) AS a
LEFT OUTER JOIN (
	SELECT pp.IDLinha,uapl.IDUap,SUM(qd.QuantidadePecas) AS defeitos
	FROM PecasProduzidas AS pp
	INNER JOIN UAPLinha AS uapl ON pp.IDLinha = uapl.IDLinha
	LEFT OUTER JOIN QuantidadeDefeitos AS qd ON pp.IDPecasProduzidas = qd.IDPecasProduzidas
	where pp.Data >= Cast('2011-05-27' as Date) and pp.Data <= Cast('2011-05-27' as Date)  
	GROUP BY pp.IDLinha,uapl.IDUap
) AS b
ON a.IDLinha=b.IDLinha AND a.IDUap = b.IDUap

) AS total
ON base.IDLinha = total.IDLinha AND base.IDUap = total.IDUap
) as top5
Left outer join Linhas as l on top5.IDLinha = l.IDLinhas
where IDUap=1
ORDER BY idprojecto,idlinha


Alguem me pode ajudar nisto?

Cumprimentos a todos!

Share this post


Link to post
Share on other sites
Caça

Pelo que parece, não aceita a instrução OVER.


Pedro Martins

Não respondo a duvidas por PM

Share this post


Link to post
Share on other sites
diego_10

Não tenho como resolver isso não é?

So trocando a função rownumber no sql é isso?

Cumprimentos

Share this post


Link to post
Share on other sites
Caça

Vais ter de alterar a query de modo a que consigas os mesmos resultados, mas de outra maneira.

Como alternativa, podes fazer por código mas não é recomendável ter wizards e código no mesmo projecto.


Pedro Martins

Não respondo a duvidas por PM

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×
×
  • Create New...

Important Information

By using this site you accept our Terms of Use and Privacy Policy. We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.