Jump to content
  • Revista PROGRAMAR: Já está disponível a edição #60 da revista programar. Faz já o download aqui!

Sign in to follow this  
PCerqueira

SQL - Mais de uma expressão em subquery?

Recommended Posts

PCerqueira

Boas a todos!

Sou novato tanto no campo da informática, como aqui no P@P, mas surgiu-me um problema e se alguém podesse resolver seria óptimo.

O problema é o seguinte:

(select prre.rem as Descricao1, isnull(sum (prre.ere),0) 
from prre inner join pr on prre.prstamp = pr.prstamp  
Where month(data)='1' and year(data)='2010' group by prre.rem) as 'AbonosJan', 0 as 'DescJan',

Óbviamente isto é parte do código, até porque isto é uma subquery.

Mas se ainda não perceberam qual o problema, passo a dizer: Quando tento inserir este código no SQL ele dá-me a seguinte mensagem de erro:

erromv.jpg

Msg 116, Level 16, State 1, Line 1

Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

Nota: 'Prre' e 'Pr' são tabelas;

        'rem' é um campo de texto e 'ede' é um campo com valores numéricos;

        'prre.prstamp = pr.prstamp' os 'stamps' são apenas campos de ligação entre as duas tabelas.

          O código funciona, mas não se for colocado numa subquerry, pois diz que só pode ter uma expressão nela, e ali eu tenho duas.

          Tenho de fazer este código para cada mês, ou seja, quando month(data)='01'/'02'/ e por aí fora, logo não posso meter no select geral, certo?

Desde já o meu obrigado.

Share this post


Link to post
Share on other sites
M6

Tens essa subquery na cláusula where do select principal não é? Algo tipo where x in (select .... ) ou where x  = (select ... )

Se assim é, a mensagem é bem explicita, esse select só pode devolver um campo/coluna, de forma a que o operador possa funcionar.


10 REM Generation 48K!
20 INPUT "URL:", A$
30 IF A$(1 TO 4) = "HTTP" THEN PRINT "400 Bad Request": GOTO 50
40 PRINT "404 Not Found"
50 PRINT "./M6 @ Portugal a Programar."

 

Share this post


Link to post
Share on other sites
PCerqueira

Não, não, está antes do where.

Eu mando-te o código completo, pode ser que percebas melhor:

select prre.cr, pr.nome as nome, pe.morada as 'Morada', pe.nasc as 'DataNascimento', pe.banco as 'Banco', pe.ncont as 'N.ºContribuinte', 
pe.balcao as 'Agência', pe.NIB as 'NIB', pe.nbenef as 'NBenef', pe.ccategoria as 'Categoria',
pr.dias, pe.caixa as 'Centro Seg. Soc.', pr.bairro as 'Bairro Fiscal', pr.seccao as 'LPSeccao',
pe.ccusto as 'Centro Custo', pe.dataadm as 'DataAdmissão', pe.csitprof as 'Situação', pe.cinicio as 'D.Antig', pe.ebasemes as 'V.Mes', pe.ebasedia as 'V.Dia', pe.contrato as 'Contrato',
'TOTAL DE ABONOS:' as Tipo, prre.cr as Codigo, sum(prre.rqtt) as 'Quant',sum(prre.ere),0, month(data)as Mês, year(data) as Ano,
(select prre.rem as Descricao1, isnull(sum (prre.ere),0) from prre inner join pr on prre.prstamp = pr.prstamp  Where month(data)='1' and year(data)='2010' group by prre.rem) as 'AbonosJan', 0 as 'DescJan',
(select prre.rem as Descricao1, isnull(sum (prre.ere),0) from prre inner join pr on prre.prstamp = pr.prstamp  Where month(data)='2' and year(data)='2010' group by prre.rem) as 'AbonosFev', 0 as 'DescFev',
(select prre.rem as Descricao1, isnull(sum (prre.ere),0) from prre inner join pr on prre.prstamp = pr.prstamp  Where month(data)='3' and year(data)='2010' group by prre.rem) as 'AbonosMar', 0 as 'DescMar',
(select prre.rem as Descricao1, isnull(sum (prre.ere),0) from prre inner join pr on prre.prstamp = pr.prstamp  Where month(data)='4' and year(data)='2010' group by prre.rem) as 'AbonosAbr', 0 as 'DescAbr',
(select prre.rem as Descricao1, isnull(sum (prre.ere),0) from prre inner join pr on prre.prstamp = pr.prstamp  Where month(data)='5' and year(data)='2010' group by prre.rem) as 'AbonosMai', 0 as 'DescMai',
(select prre.rem as Descricao1, isnull(sum (prre.ere),0) from prre inner join pr on prre.prstamp = pr.prstamp  Where month(data)='6' and year(data)='2010' group by prre.rem) as 'AbonosJun', 0 as 'DescJun',
(select prre.rem as Descricao1, isnull(sum (prre.ere),0) from prre inner join pr on prre.prstamp = pr.prstamp  Where month(data)='7' and year(data)='2010' group by prre.rem) as 'AbonosJul', 0 as 'DescJul',
(select prre.rem as Descricao1, isnull(sum (prre.ere),0) from prre inner join pr on prre.prstamp = pr.prstamp  Where month(data)='8' and year(data)='2010' group by prre.rem) as 'AbonosAgo', 0 as 'DescAgo',
(select prre.rem as Descricao1, isnull(sum (prre.ere),0) from prre inner join pr on prre.prstamp = pr.prstamp  Where month(data)='9' and year(data)='2010' group by prre.rem) as 'AbonosSet', 0 as 'DescSet',
(select prre.rem as Descricao1, isnull(sum (prre.ere),0) from prre inner join pr on prre.prstamp = pr.prstamp  Where month(data)='10' and year(data)='2010' group by prre.rem) as 'AbonosOut', 0 as 'DescOut',
(select prre.rem as Descricao1, isnull(sum (prre.ere),0) from prre inner join pr on prre.prstamp = pr.prstamp  Where month(data)='11' and year(data)='2010' group by prre.rem) as 'AbonosNov', 0 as 'DescNov',
(select prre.rem as Descricao1, isnull(sum (prre.ere),0) from prre inner join pr on prre.prstamp = pr.prstamp  Where month(data)='12' and year(data)='2010' group by prre.rem) as 'AbonosDez', 0 as 'DescDez'
from  pr (nolock) INNER JOIN prre (nolock) on prre.prstamp = pr.prstamp inner join cm6 (nolock) on cm6.cm=prre.cr inner join prde (nolock) on prde.prstamp=pr.prstamp inner join pe (nolock) on pe.no=pr.no
where year(data) = '2010' and u_coddesc=0
group by prre.cr, prre.rem, pr.nome, data,pe.morada,pe.nasc, pe.banco, pe.ncont, pe.balcao, pe.NIB, pe.nbenef, 
pe.ccategoria, pr.dias, pe.caixa, pr.bairro, pr.seccao, pe.ccusto, pe.dataadm,  pe.csitprof, pe.cinicio, pe.ebasemes, pe.ebasedia, pe.contrato
union all

(...) O resto, visto que é um Union será practicamente igual, mas se conseguir resolver o de cima resolvo o de baixo da mesma forma.

PS: Não liguem aos 'pe' e 'prre' e etc, são apenas tabelas e campos.

Obrigado.

Share this post


Link to post
Share on other sites
M6

Ok, já percebi.

Não podes fazer isso no Select, a razão é a mesma do Where. Não podes devolver mais de um campo/coluna, numa query que é feita numa cláusula select. Repara que estás a dizer que o campo/coluna AbonosJan é o nome do resultado de uma query que devolve vários campo, o que não pode ser. Essa querie só pode devolver um único campo ao qual será dado o nome AbonosJan.

Essa queries todas deviam ser feitas com um group by por mês, ano e outros campos se necessário (até porque como está feito é mais ineficiente).


10 REM Generation 48K!
20 INPUT "URL:", A$
30 IF A$(1 TO 4) = "HTTP" THEN PRINT "400 Bad Request": GOTO 50
40 PRINT "404 Not Found"
50 PRINT "./M6 @ Portugal a Programar."

 

Share this post


Link to post
Share on other sites
PCerqueira

Ok, eu fiz de outra forma:

select pe.no, pr.nome as nome, pe.morada as 'Morada', pe.nasc as 'DataNascimento', pe.banco as 'Banco', pe.ncont as 'N.ºContribuinte', 
pe.balcao as 'Agência', pe.NIB as 'NIB', pe.nbenef as 'NBenef', pe.ccategoria as 'Categoria',
pr.dias, pe.caixa as 'Centro Seg. Soc.', pr.bairro as 'Bairro Fiscal', pr.seccao as 'LPSeccao',
pe.ccusto as 'Centro Custo', pe.dataadm as 'DataAdmissão', pe.csitprof as 'Situação', pe.cinicio as 'D.Antig', pe.ebasemes as 'V.Mes', pe.ebasedia as 'V.Dia', pe.contrato as 'Contrato',
'TOTAL DE ABONOS:' as Tipo, prre.cr as Codigo, sum(prre.rqtt) as 'Quant', month(data)as Mês, year(data) as Ano, prre.rem as Descricao1,sum(prre.ere)
from  pr (nolock) INNER JOIN prre (nolock) on prre.prstamp = pr.prstamp inner join cm6 (nolock) on cm6.cm=prre.cr inner join prde (nolock) on prde.prstamp=pr.prstamp inner join pe (nolock) on pe.no=pr.no
Where exists
(Select prre.rem as Descricao1,sum(prre.ere) as Valores
from  pr (nolock) INNER JOIN prre (nolock) on prre.prstamp = pr.prstamp inner join cm6 (nolock) on cm6.cm=prre.cr inner join prde (nolock) on prde.prstamp=pr.prstamp inner join pe (nolock) on pe.no=pr.no
Where month(data)='1' and year(data)='2010' 
group by prre.rem, prre.cr) 
group by pe.no,prre.rem, prre.cr, prre.rem, pr.nome, data,pe.morada,pe.nasc, pe.banco, pe.ncont, pe.balcao, pe.NIB, pe.nbenef, 
pe.ccategoria, pr.dias, pe.caixa, pr.bairro, pr.seccao, pe.ccusto, pe.dataadm,  pe.csitprof, pe.cinicio, pe.ebasemes, pe.ebasedia, pe.contrato

Ela dá-me o resultado pretendido, mas isto só para o mês de Janeiro, agora eu gostava de saber como faço para os outros meses. :bored:

Share this post


Link to post
Share on other sites
M6

Parece-me que te dá o resultado pretendido quase por um acaso.

A query é chata de ler e esse "Where exists" parece-me um enorme ponto de falha para obter a informação que queres.

Se fizeres

Select prre.rem as Descricao1,month(data),  year(data), sum(prre.ere) as Valores
from  pr (nolock) INNER JOIN prre (nolock) on prre.prstamp = pr.prstamp inner join cm6 (nolock) on cm6.cm=prre.cr inner join prde (nolock) on prde.prstamp=pr.prstamp inner join pe (nolock) on pe.no=pr.no
group by month(data), year(data), prre.rem, prre.cr

Vais obter os valores de que necessitas para cada um dos meses e anos.

Usa isso para fazeres join com a restante query, vais obter toda a informação de que necessitas.


10 REM Generation 48K!
20 INPUT "URL:", A$
30 IF A$(1 TO 4) = "HTTP" THEN PRINT "400 Bad Request": GOTO 50
40 PRINT "404 Not Found"
50 PRINT "./M6 @ Portugal a Programar."

 

Share this post


Link to post
Share on other sites
PCerqueira

É assim, isso seria perfeito, até porque já tentei.

Mas o problema é que tenho de ter um campo/coluna para cada mês, isto porque terei de trabalhar com PHC (Não sei se conhecem, mas é um programa de gestão) e basicamente, no desenho do mapa tenho de ter um campo para cada mês ou então ele irá dar um valor sorteado ou ainda a soma de todos eles.

Agradeço desde já qualquer tipo de ajuda.

(Senão me fiz perceber eu tento explicar de outra forma)

PS: Peço desculpa por não ter dito isto antes. E quanto à query pode ficar do jeito que ficar, logo que cada campo seja o valor de cada mês; Quanto ao ano, não importa, é manipulado no tal PHC.

Share this post


Link to post
Share on other sites
M6

Não sei porquê, isso cheira-me a esturro...

Não conheço a API do PHC, mas não me cheira bem essa história. Há uma boa razão para isso ou será que não viste bem como fazer a coisa? É que isso é basicamente uma marretada "contra natura" ao modelo relacional, e não me parece que o PHC queira fazer isso... Até porque o que vai acontecer quando mudar o ano? Martelar novo desenvolvimento? Não faz muito sentido...

Mas enfim, adiante...

Para fazeres a coisa como pretendes, pode sempre fazer um join com essa tabela e fazeres cada um dos selects (como tinhas inicialmente) retirando apenas o valor que pretendes para cada mês, dado que já os tens, basta-te fazeres o select com o filtro correspondente.


10 REM Generation 48K!
20 INPUT "URL:", A$
30 IF A$(1 TO 4) = "HTTP" THEN PRINT "400 Bad Request": GOTO 50
40 PRINT "404 Not Found"
50 PRINT "./M6 @ Portugal a Programar."

 

Share this post


Link to post
Share on other sites
PCerqueira

M6, para o ano crio uma variavél e o ano varia, quanto aos meses, terão que aparecer sempre.

O mapa não fui eu que fiz, já está feito, mas em formato papel, eu apenas tenho de faze-lo em suporte informático.

PHC é como Crystal Reports, não sei se sabes algo de Crystal, mas de forma mais simplificada que o PHC.

Quanto à query:

Estás a dizer para fazer 12 joins?

Pá, eu tive uma 'ideia', não sei é faze-la, ou sequer se é possível, mas o problema seria resolvido quando na cláusula WHERE o mês for igual a 1, dar um nome, criando um campo/coluna.

Ou seja, o que estou a dizer é qualquer coisa como isto:

SELECT prre.rem AS Descricao1,month(DATA),  year(DATA), sum(prre.ere) AS Valores
FROM  pr (nolock) INNER JOIN prre (nolock) ON prre.prstamp = pr.prstamp INNER JOIN cm6 (nolock) ON cm6.cm=prre.cr INNER JOIN prde (nolock) ON prde.prstamp=pr.prstamp INNER JOIN pe (nolock) ON pe.no=pr.no
Where (month(data)='1' as jan), (month(Data)='2' as fev)
GROUP BY month(DATA), year(DATA), prre.rem, prre.cr

Assim, óbviamente não funciona, mas é este o pensamento.

Share this post


Link to post
Share on other sites
M6

Conheço o modelo de outros tipos de reports, sim.

Se trocares as linhas pelas colunas és capaz de obter o que necessitas com o group by, mas voltando ao que interessa, não é necessário fazeres 12 joins, fazes apenas um a essa query do group by e depois fazes 12 selects a e isso.

Exemplo:

select ...
(select sum(total) from tabelaAgrupada where tabelaAgrupada.mes = 1) as ValorJaneiro,
(select sum(total) from tabelaAgrupada where tabelaAgrupada.mes = 2) as ValorFevereiro,
...
(select sum(total) from tabelaAgrupada where tabelaAgrupada.mes = 12) as ValorDezembro
from tabelaPrincipal
inner join (select sum(valor) as total, id, mes, ano from tabela group by id, mes, ano) as tabelaAgrupada
on tabelaAgrupada.id = tabelaPrincipal.id 
...

A ideia é basicamente esta.


10 REM Generation 48K!
20 INPUT "URL:", A$
30 IF A$(1 TO 4) = "HTTP" THEN PRINT "400 Bad Request": GOTO 50
40 PRINT "404 Not Found"
50 PRINT "./M6 @ Portugal a Programar."

 

Share this post


Link to post
Share on other sites
PCerqueira

Ou quase, porque se fizer apenas e só

 (SELECT sum(total) FROM tabelaAgrupada WHERE tabelaAgrupada.mes = 1) AS ValorJaneiro,

ele vai dar um valor TOTAL e não um total por secção, ou seja, vai dar um valor igual em todos os meses, pois vai fazer a soma de todos.

Por isso, dentro desta subquery terá de entrar o campo de texto (prre.rem, neste caso) para que ele faça sum apenas Àquela 'secção'.

E volto à estaca zero, pois foi exactamente aqui que impanquei  :bored:

Share this post


Link to post
Share on other sites
M6

Continuo a achar que devias validar isso do PHC e dos reports antes de implementares esta solução "menos limpa"...

O que necessitas então é algo do tipo

select 
...
tabelaAgrupada.rme, -- vem do prre.rem
(select sum(ta.total) from tabelaAgrupada ta where ta.mes = 1 and tabelaAgrupada.rme = ta.rme) as ValorJaneiro,
(select sum(ta.total) from tabelaAgrupada ta where ta.mes = 2 and tabelaAgrupada.rme = ta.rme) as ValorFevereiro,
...
(select sum(ta.total) from tabelaAgrupada ta where ta.mes = 12 and tabelaAgrupada.rme = ta.rme) as ValorDezembro
from tabelaPrincipal
inner join (select sum(valor), as total, id, mes, ano, prre.rem  from tabela 
              inner join prre.rem 
              on prre.chave = tabela.chave 
              group by rem, id, mes, ano) as tabelaAgrupada
on tabelaAgrupada.id = tabelaPrincipal.id
...

A ideia é basicamente esta.


10 REM Generation 48K!
20 INPUT "URL:", A$
30 IF A$(1 TO 4) = "HTTP" THEN PRINT "400 Bad Request": GOTO 50
40 PRINT "404 Not Found"
50 PRINT "./M6 @ Portugal a Programar."

 

Share this post


Link to post
Share on other sites
PCerqueira

Dessa forma não resultava, até porque não consegui interpretar bem o código,  mas de qualquer forma, já consegui arranjar solução. O único problema, é para quem lê a query, pois torna-se chata, e pesada. Tanto que tem mais de duzentas linhas.

Mas o que imporata é que está a funcionar na perfeição.

Vou deixar aqui a query, caso haja mais alguma vez este problema:

select pe.no, pr.nome as nome, pe.morada as 'Morada', pe.nasc as 'DataNascimento', pe.banco as 'Banco', pe.ncont as 'N.ºContribuinte', 
pe.balcao as 'Agência', pe.NIB as 'NIB', pe.nbenef as 'NBenef', pe.ccategoria as 'Categoria',
pr.dias, pe.caixa as 'Centro Seg. Soc.', pr.bairro as 'Bairro Fiscal', pr.seccao as 'LPSeccao',
pe.ccusto as 'Centro Custo', pe.dataadm as 'DataAdmissão', pe.csitprof as 'Situação', pe.cinicio as 'D.Antig', pe.ebasemes as 'V.Mes', pe.ebasedia as 'V.Dia', pe.contrato as 'Contrato',
'TOTAL DE ABONOS:' as Tipo, prre.cr as Codigo, sum(prre.rqtt) as 'Quant', month(data)as Mês, year(data) as Ano, prre.rem as Descricao1, 
isnull(sum(prre.ere),0) as 'AbonosJan', 0 as 'DescJan', 0 as 'AbonosFev',
0 as 'DescFev',0 as 'AbonosMar', 0 as 'DescMar', 0 as 'AbonosAbr', 0 as 'DescAbr',
0 as 'AbonosMai', 0 as 'DescMai', 0 as 'AbonosJun', 0 as 'DescJun', 
0 as 'AbonosJul', 0 as 'DescJul', 0 as 'AbonosAgo', 0 as 'DescAgo',
0 as 'AbonosSet', 0 as 'DescSet', 0 as 'AbonosOut', 0 as 'DescOut',
0 as 'AbonosNov', 0 as 'DescNov', 0 as 'AbonosDez', 0 as 'DescDez'
from  pr (nolock) INNER JOIN prre (nolock) on prre.prstamp = pr.prstamp inner join cm6 (nolock) on cm6.cm=prre.cr inner join prde (nolock) on prde.prstamp=pr.prstamp inner join pe (nolock) on pe.no=pr.no
Where (month(Data)='1') and year(data)='2009' 
group by pe.no,prre.rem, prre.cr, prre.rem, pr.nome, data,pe.morada,pe.nasc, pe.banco, pe.ncont, pe.balcao, pe.NIB, pe.nbenef, 
pe.ccategoria, pr.dias, pe.caixa, pr.bairro, pr.seccao, pe.ccusto, pe.dataadm,  pe.csitprof, pe.cinicio, pe.ebasemes, pe.ebasedia, pe.contrato
union --JANEIRO

Select prde.cd, pr.nome as nome, pe.morada as 'Morada', pe.nasc as 'DataNascimento', pe.banco as 'Banco', pe.ncont as 'N.ºContribuinte', 
pe.balcao as 'Agência', pe.NIB as 'NIB', pe.nbenef as 'NBenef', pe.ccategoria as 'Categoria',
pr.dias, pe.caixa as 'Centro Seg. Soc.', pr.bairro as 'Bairro Fiscal', pr.seccao as 'LPSeccao',
pe.ccusto as 'Centro Custo', pe.dataadm as 'DataAdmissão', pe.csitprof as 'Situação', pe.cinicio as 'D.Antig', pe.ebasemes as 'V.Mes', pe.ebasedia as 'V.Dia', pe.contrato as 'Contrato',
'TOTAL DE DESCONTOS:' as Tipo, prde.cd as Codigo, 0 as 'Quant', month(pr.data) as Mês, year(data) as Ano, prde.des as Descricao1,
0 as 'AbonosJan', isnull(sum(prde.ede),0) as 'DescJan', 0 as 'AbonosFev',
0 as 'DescFev',0 as 'AbonosMar', 0 as 'DescMar', 0 as 'AbonosAbr', 0 as 'DescAbr',
0 as 'AbonosMai', 0 as 'DescMai', 0 as 'AbonosJun', 0 as 'DescJun', 
0 as 'AbonosJul', 0 as 'DescJul', 0 as 'AbonosAgo', 0 as 'DescAgo',
0 as 'AbonosSet', 0 as 'DescSet', 0 as 'AbonosOut', 0 as 'DescOut',
0 as 'AbonosNov', 0 as 'DescNov', 0 as 'AbonosDez', 0 as 'DescDez'
from  pr (nolock) INNER JOIN prde (nolock) on prde.prstamp = pr.prstamp inner join cm6 (nolock) on cm6.cm=prde.cd inner join prre (nolock) on prre.prstamp=pr.prstamp inner join pe (nolock) on pe.no=pr.no
where month(data)='1' and year(data) = '2009'
group by prde.cd, prde.des, pr.nome, data,pe.morada,pe.nasc, pe.banco, pe.ncont, pe.balcao, pe.NIB, pe.nbenef, 
pe.ccategoria, pr.dias, pe.caixa, pr.bairro, pr.seccao, pe.ccusto, pe.dataadm,  pe.csitprof, pe.cinicio, pe.ebasemes, pe.ebasedia, pe.contrato

No fundo torna-se muito fácil de compreender, mas um bocado chata, admito.

Nota: Reparem que toda esta query é para o mês de Janeiro, aqueles zeros todos são para os outros meses.

M6, muito obrigado pelo teu tempo.  :cheesygrin:

RESOLVIDO!

Share this post


Link to post
Share on other sites
M6

Fico contente por teres ficado com o problema resolvido. Mas não fiquei convencido com essa coisa de "torcer" o modelo relacional.


10 REM Generation 48K!
20 INPUT "URL:", A$
30 IF A$(1 TO 4) = "HTTP" THEN PRINT "400 Bad Request": GOTO 50
40 PRINT "404 Not Found"
50 PRINT "./M6 @ Portugal a Programar."

 

Share this post


Link to post
Share on other sites
PCerqueira

Apenas o tive de fazer por causa do PHC.

Mas se não fosse o caso, a query que disseste servia na perfeição.  ;)

Mais uma vez, obrigado.

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
Sign in to follow this  

×

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.