Firebird retornar dados em uma unica coluna

Firebird

28/11/2014

B. dia pessoal tenho a seguintes tabelas 
/* Table: ESTOQUEPROD, Owner: SYSDBA */
CREATE TABLE "ESTOQUEPROD" 
(
  "CODIGO"	 INTEGER NOT NULL,
  "CODFORNPENULT"	 INTEGER,
  "CODFORNENTRADA"	 INTEGER,
  "ID_LOJA"	 INTEGER NOT NULL,
  "QUANTIDADE"	 DOUBLE PRECISION NOT NULL,
  "QUANTIDADEDEP"	 DOUBLE PRECISION NOT NULL,
  "QUANTPENULT"	 DOUBLE PRECISION,
  "VALOR"	 DOUBLE PRECISION,
  "VALORPENULT"	 DOUBLE PRECISION,
  "ULTIMAVENDA"	 TIMESTAMP,
  "ULTIMAENTRADA"	 TIMESTAMP,
  "DATAPENULTENTRADA"	 TIMESTAMP,
  "ALTERACAO"	 TIMESTAMP,
  "QUANTENTRADA"	 DOUBLE PRECISION,
  "QTGRADE"	 VARCHAR(250),
  "E_PRECO_COMPRA"	 DOUBLE PRECISION,
  "E_PRECO_VENDA"	 DOUBLE PRECISION,
  "E_MARGEM"	 NUMERIC(15,2),
  "E_ESTMINIMO"	 INTEGER,
  "E_ESTMAXIMO"	 INTEGER,
CONSTRAINT "PK_ESTOQUEPROD" PRIMARY KEY ("CODIGO", "ID_LOJA")
);


CREATE TABLE "PRODUTOS" 
(
  "CODIGO"	 INTEGER NOT NULL,
  "CODSETOR"	 INTEGER,
  "CODFORN"	 INTEGER,
  "CODLAB"	 INTEGER,
  "CODIGO_BARRAS"	 VARCHAR(14),
  "PRODUTO"	 VARCHAR(50) NOT NULL,
  "ICMS"	 VARCHAR(6),
  "PRECO_COMPRA"	 DOUBLE PRECISION,
  "PRECO_VENDA"	 DOUBLE PRECISION,
  "MAXIMO"	 DOUBLE PRECISION,
  "MINIMO"	 DOUBLE PRECISION,
  "UNIDADE"	 VARCHAR(4),
  "ESPECIFICACAO"	 VARCHAR(45),
  "IMAGEM"	 BLOB SUB_TYPE 0 SEGMENT SIZE 80,


a tabela produtos carregar o cadastro do produto a tabela estoque
carrega os estoques por loja, gostaria retornar os dados em colunas.

Ex:

Produto Loja 1 Loja2
teste de loja 2 3
Obrigado a todos que poderem dar uma força.
Emerson Santos

Emerson Santos

Curtidas 0

Melhor post

Marisiana Battistella

Marisiana Battistella

30/11/2014

É q vc copiou uma parte q não pertence ao subselect, ele acaba no primeiro GROUP BY...

 SELECT p.produto,
           CASE
           WHEN v.id_loja = 1 THEN
             SUM(v.total)
           END as loja_1,
           CASE
           WHEN v.id_loja = 2 THEN
             SUM(v.total)
           END as loja_2
    FROM vendas v
    LEFT OUTER JOIN produtos p  ON (v.codigo = p.codigo)
    WHERE v.id_loja in (1,2)
    GROUP BY p.produto,v.id_loja
GOSTEI 1

Mais Respostas

Ronaldo Lanhellas

Ronaldo Lanhellas

28/11/2014

Você pode tentar o GROUP BY, para agrupar todos os produtos por loja. Estude um pouco sobre: http://www.firebirdsql.org/manual/nullguide-aggrfunc.html
GOSTEI 0
Ronaldo Lanhellas

Ronaldo Lanhellas

28/11/2014

Você pode tentar o GROUP BY, para agrupar todos os produtos por loja. Estude um pouco sobre: http://www.firebirdsql.org/manual/nullguide-aggrfunc.html
GOSTEI 0
Ronaldo Lanhellas

Ronaldo Lanhellas

28/11/2014

Você pode tentar o GROUP BY, para agrupar todos os produtos por loja. Estude um pouco sobre: http://www.firebirdsql.org/manual/nullguide-aggrfunc.html
GOSTEI 0
Ronaldo Lanhellas

Ronaldo Lanhellas

28/11/2014

Você pode tentar o GROUP BY, para agrupar todos os produtos por loja. Estude um pouco sobre: http://www.firebirdsql.org/manual/nullguide-aggrfunc.html
GOSTEI 0
Emerson Santos

Emerson Santos

28/11/2014

SELECT
P.CODIGO,
P.PRODUTO,
E.QUANTIDADE
FROM PRODUTOS P
LEFT OUTER JOIN ESTOQUEPROD E ON E.CODIGO =P.CODIGO
WHERE P.CODIGO=46463
GROUP BY P.CODIGO,P.PRODUTO,E.QUANTIDADE

mesmo utilizando o seguinte codigo , ele mostra o mesmo registro varias vezes
de acordo com a quantidade de lojas que tem estoque na tabela estoqueprod
eu queri agrupalos
produto quantidade1 quantidade2 quantidade3
caixa 2 3 5
GOSTEI 0
Marisiana Battistella

Marisiana Battistella

28/11/2014

No teu caso eu acho que vc vai ter que utilizar o CASE ou algum outro recurso que transforme linhas em colunas.
No Oracle pode-se fazer isso utilizando o PIVOT, mas eu acho q não tem esse recurso no firebird...
GOSTEI 0
Randrade

Randrade

28/11/2014

SELECT
P.CODIGO,
P.PRODUTO,
E.QUANTIDADE
FROM PRODUTOS P
LEFT OUTER JOIN ESTOQUEPROD E ON E.CODIGO =P.CODIGO
WHERE P.CODIGO=46463
GROUP BY P.CODIGO,P.PRODUTO,E.QUANTIDADE

mesmo utilizando o seguinte codigo , ele mostra o mesmo registro varias vezes
de acordo com a quantidade de lojas que tem estoque na tabela estoqueprod
eu queri agrupalos
produto quantidade1 quantidade2 quantidade3
caixa 2 3 5


Não entendi muito bem sua dúvida. Mas se está retornando o mesmo dado várias vezes, já tentou usar o "DISTINCT". Basta adicionar a palavra "DISTINCT" após o SELECT.

Se não for isso, peço que explique melhor seu problema para que possa ajudar melhor.
GOSTEI 0
Marisiana Battistella

Marisiana Battistella

28/11/2014

Randrade, pelo que entendi, ele precisa listar

PRODUTO     LOJA_1    LOJA_2   LOJA_3
prod1         1                3               4
prod2         2                1               2
prod3         2                4                4
GOSTEI 0
Emerson Santos

Emerson Santos

28/11/2014

Isso mesmo Marisiana que preciso , sim o oracle e mysql tem esse recurso pivot
ate o mesmo o access, no firebird nao tem , so tem um recurso chamado LIST
mais ainda sim nao é o que preciso, pensei em alguem que tivesse conhecimento em
store procedure ja vi isso utilizando esse recurso.
GOSTEI 0
Marisiana Battistella

Marisiana Battistella

28/11/2014

Consegui fazer assim:
SELECT res.nmproduto,
       SUM(res.loja_1) as loja_1,
       SUM(res.loja_2) as loja_2
FROM (
	SELECT p.nmproduto,
	       CASE 
		   WHEN v.idloja = 1 THEN
		     SUM(v.vlrtotal)
	       END as loja_1,
	       CASE 
		   WHEN v.idloja = 2 THEN
		     SUM(v.totalitem) 
	       END as loja_2
	FROM venda v
	JOIN produto p
	ON v.idproduto = p.idproduto
	WHERE v.idloja in (1,2)
	GROUP BY p.nmproduto,
		 v.idloja) res
GROUP BY res.nmproduto
ORDER BY res.nmproduto
GOSTEI 0
Marisiana Battistella

Marisiana Battistella

28/11/2014

Assim eu consegui fazer listar da forma que você precisa e na base que testei a performance ficou boa, isso que é uma base que contém muitos dados...
Basta saber se pode ser aplicado...
GOSTEI 0
Emerson Santos

Emerson Santos

28/11/2014

Legal Marisiana, bom demais vou testar aqui,
sql comum ou voce colocou em uma store procedures ?
GOSTEI 0
Emerson Santos

Emerson Santos

28/11/2014

Adequei de acordo com meu banco
mais da esse erro

ISC ERROR CODE:335544569

ISC ERROR MESSAGE:
Dynamic SQL Error
SQL error code = -104
Token unknown - line 5, column 5
SELECT

SELECT res.produto,
       SUM(res.loja_1) as loja_1,
       SUM(res.loja_2) as loja_2
FROM (
    SELECT p.produto,
           CASE
           WHEN v.id_loja = 1 THEN
             SUM(v.total)
           END as loja_1,
           CASE
           WHEN v.id_loja = 2 THEN
             SUM(v.total)
           END as loja_2
    FROM vendas v
    JOIN produtos p  ON v.codigo = p.codigo
    WHERE v.id_loja in (1,2)
    GROUP BY p.produto,v.id_loja) res
GROUP BY res.produto
ORDER BY res.produto
GOSTEI 0
Marisiana Battistella

Marisiana Battistella

28/11/2014

Eu fiz um SELECT normal, direto no banco de dados...
Executa só o select do subselect para ver se dá o mesmo erro?
GOSTEI 0
Emerson Santos

Emerson Santos

28/11/2014

executando o subselect
da esse erro
ISC ERROR CODE:335544569

ISC ERROR MESSAGE:
Dynamic SQL Error
SQL error code = -104
Token unknown - line 13, column 33
)

SELECT p.produto,
           CASE
           WHEN v.id_loja = 1 THEN
             SUM(v.total)
           END as loja_1,
           CASE
           WHEN v.id_loja = 2 THEN
             SUM(v.total)
           END as loja_2
    FROM vendas v
    JOIN produtos p  ON v.codigo = p.codigo
    WHERE v.id_loja in (1,2)
    GROUP BY p.produto,v.id_loja) res
GROUP BY res.produto
ORDER BY res.produto
GOSTEI 0
Marisiana Battistella

Marisiana Battistella

28/11/2014

Eu acho que problema é a sintaxe que não está de acordo com a do firebird.
Tenta incluir o parênteses em ON ( v.codigo = p.codigo )
GOSTEI 0
Marisiana Battistella

Marisiana Battistella

28/11/2014

Vc executou linhas a mais. executa assim:
SELECT p.produto,
           CASE
           WHEN v.id_loja = 1 THEN
             SUM(v.total)
           END as loja_1,
           CASE
           WHEN v.id_loja = 2 THEN
             SUM(v.total)
           END as loja_2
    FROM vendas v
    JOIN produtos p  ON  ( v.codigo = p.codigo )
    WHERE v.id_loja in (1,2)
    GROUP BY p.produto,v.id_loja
GOSTEI 0
Emerson Santos

Emerson Santos

28/11/2014

toquei pelo Left outer join
embora a sintaxe do Join confirmei esta certo
o erro ficou na linha 13
SC ERROR MESSAGE:
Dynamic SQL Error
SQL error code = -104
Token unknown - line 13, column 33
)

 SELECT p.produto,
           CASE
           WHEN v.id_loja = 1 THEN
             SUM(v.total)
           END as loja_1,
           CASE
           WHEN v.id_loja = 2 THEN
             SUM(v.total)
           END as loja_2
    FROM vendas v
    LEFT OUTER JOIN produtos p  ON (v.codigo = p.codigo)
    WHERE v.id_loja in (1,2)
    GROUP BY p.produto,v.id_loja) 
GROUP BY res.produto
ORDER BY res.produto
GOSTEI 0
Emerson Santos

Emerson Santos

28/11/2014

Marisiana desculpe nao me atentei,
é verdade mesmo, olha funcionou uma beleza
como uma velocidade impressionante, será
que consigo esse mesmo resultado com a tabela estoque

por exemplo

produto1 estoque loja1 estoque loja2
caixa teste 20 35
GOSTEI 0
Marisiana Battistella

Marisiana Battistella

28/11/2014

Que bom!
Mas para retornar os valores corretos, você precisa executar a instrução completa. Testa assim:

SELECT res.produto,
       SUM(res.loja_1) as loja_1,
       SUM(res.loja_2) as loja_2
FROM (
    SELECT p.produto,
           CASE
           WHEN v.id_loja = 1 THEN
             SUM(v.total)
           END as loja_1,
           CASE
           WHEN v.id_loja = 2 THEN
             SUM(v.total)
           END as loja_2
    FROM vendas v
    LEFT OUTER JOIN produtos p  ON ( v.codigo = p.codigo )
    WHERE v.id_loja in (1,2)
    GROUP BY p.produto,v.id_loja  ) res
GROUP BY res.produto
ORDER BY res.produto

Desculpa te pedir pra ficar testando assim, eu não tenho firebird instalado...
GOSTEI 0
Marisiana Battistella

Marisiana Battistella

28/11/2014

Tem como retornar os dados do estoque sim!
Basicamente vc vai alterar a estrutura e incluir a tabela de estoque no lugar da tabela de vendas...
GOSTEI 0
Emerson Santos

Emerson Santos

28/11/2014

Sem problema Marisiana , esta me ajudando muito.

vi que tem um problema mesmo se nao executar completo
ele completo da esse erro.


ISC ERROR CODE:335544569

ISC ERROR MESSAGE:
Dynamic SQL Error
SQL error code = -104
Token unknown - line 5, column 5
SELECT

Statement: SELECT res.produto,
GOSTEI 0
Marisiana Battistella

Marisiana Battistella

28/11/2014

Agora o problema deve ser o subselect. Qual é a versão do teu Firebird?

Eu li em um outro tópico aqui, que o Firebird 2.0 aceita esse tipo de estrutura com Subselect...
GOSTEI 0
Emerson Santos

Emerson Santos

28/11/2014

Marisiana a verdade era isso mesmo
a versão do firebird testei com uma versao 2.1
funcionou perfeito coloquei um coalesce para nao retornar
null,
so nao consegui fazer where ou and ele duplica de novo
exemplo


where datavenda >='11/04/2001' and datavenda <='11/02/2015'
me confudiu um pouco esse
WHERE v.id_loja in (1,2).
mais desde ja fico agradecido pela sua ajuda esta sendo
de grande valia.
GOSTEI 0
Marisiana Battistella

Marisiana Battistella

28/11/2014

Esse WHERE v.id_loja in (1,2) filtra os dados que serão utilizados na consulta.
Digamos q tenha uma loja 3 q e vc não inclua o CASE para somar os valores da loja 3, sem o WHERE, provavelmente, o retorno terá uma coluna a mais com os valores correspondentes a filial que vc não incluiu o CASE.
Da mesma forma, se vc incluisse o CASE para a loja 3 e não acrescentasse a loja 3 no WHERE, a coluna da loja 3 ficaria sem valores, pois a regra definida na claúsula WHERE era pra pra considerar apenas os dados da loja 1 e 2.
GOSTEI 0
Emerson Santos

Emerson Santos

28/11/2014

Entendi , agora nessa clausula where
se eu quiser colocar exemplo mais uma condicao para essa
sql exemplo; quero essas informacoes para todas as lojas
mais para apenas o produto de codigo =46453
GOSTEI 0
Marisiana Battistella

Marisiana Battistella

28/11/2014

Dai é só incluir a condição... Seguindo exemplo que tenho ali ficaria assim
WHERE v.id_loja in (1,2)
AND p.codigo = 46453
GOSTEI 1
Emerson Santos

Emerson Santos

28/11/2014

SELECT res.produto,
       SUM(res.loja_1) as loja_1,
       SUM(res.loja_2) as loja_2
FROM (
    SELECT p.produto,
           CASE
           WHEN e.id_loja = 1 THEN
             SUM(e.quantidade)
           END as loja_1,
           CASE
           WHEN e.id_loja = 2 THEN
             SUM(e.quantidade)
           END as loja_2
    FROM estoqueprod e
    LEFT OUTER JOIN produtos p  ON ( e.codigo = p.codigo )
    WHERE e.id_loja in (1,2)
    and p.codigo = 46463
    GROUP BY p.produto,e.id_loja  ) res
GROUP BY res.produto
ORDER BY res.produto


ok Marisiana fiz aqui mudei de acordo com estoqueprod
que era a intencao inicial funcionou certinho, muito obrigado mesmo,
pela sua ajuda e atenção.
GOSTEI 1
Marisiana Battistella

Marisiana Battistella

28/11/2014

Por nada!!
Também aprendi te ajudando!
GOSTEI 1
Emerson Santos

Emerson Santos

28/11/2014

Marisiana estou usando a sql a todo vapor,
agora estava precisando totalizar os campos res em coluna na frente
isso seria possivel ?
olha meu codigo, ele traz a falta de cada loja de acordo com o minimo
floja_1,floja_2,floja_3 e a falta das tres lojas de acordo com o minimo,
eu queria totalizar na frente.

ex:

produto minimo estLoja1 falta1 estLoja2 falta estLoja3 Falta total
caixa 4 2 2 0 4 1 3 9

tentei aqui mais nao consegui ainda.
 SELECT
        res.CODIGO,
        res.CODSETOR,
        res.CODFORN,
        res.CODLAB,
        res.PRECO_VENDA,
        res.PRECO_COMPRA,
        res.MINIMO,
        res.CODIGO_BARRAS,
        res.PRODUTO,
        res.UNIDADE,
        res.STATUS,
        res.PRIORIDADE,
        res.CONTROLFALTA,
        res.SETOR,
        res.FANTASIA,
        res.NOME_LAB,
        COALESCE(SUM(res.estloja_1 ),0) as estloja_1,
        COALESCE(SUM(res.floja_1 ),0) as floja_1,

        COALESCE(SUM(res.estloja_2 ),0) as estloja_2,
        COALESCE(SUM(res.floja_2 ),0) as floja_2,

        COALESCE(SUM(res.estloja_3),0) as estloja_3,
        COALESCE(SUM(res.floja_3 ),0) as floja_3,
        SUM(res.floja_1+res.floja_2+res.floja_3) AS FTotal

  FROM (
    SELECT
      P.CODIGO,
      P.CODSETOR,
      P.CODFORN,
      P.CODLAB,
      P.PRECO_VENDA,
      P.PRECO_COMPRA,
      P.MINIMO,
      P.CODIGO_BARRAS,
      P.PRODUTO,
      P.UNIDADE,
      P.STATUS,
      P.PRIORIDADE,
      P.CONTROLFALTA,
      ST.SETOR,
      F.FANTASIA,
      LB.NOME_LAB,
      CASE
         WHEN e.id_loja = 1 THEN
          SUM( E.QUANTIDADE   )
         END as estloja_1,
      CASE
         WHEN e.id_loja = 1 THEN
          SUM( P.MINIMO-E.QUANTIDADE   )
         END as floja_1,

       CASE
         WHEN e.id_loja = 2 THEN
            SUM( E.QUANTIDADE   )
         END as estloja_2,
       CASE
         WHEN e.id_loja = 2 THEN
             SUM( P.MINIMO-E.QUANTIDADE   )
         END as floja_2,
         
       CASE
         WHEN e.id_loja = 3 THEN
            SUM( E.QUANTIDADE   )
         END as estloja_3,
       CASE
        WHEN e.id_loja = 3 THEN
             SUM( P.MINIMO-E.QUANTIDADE   )
         END as floja_3
      

    FROM ESTOQUEPROD E
    LEFT OUTER JOIN PRODUTOS P  ON ( E.CODIGO = P.CODIGO )
    LEFT OUTER JOIN SETOR        ST ON P.CODSETOR = ST.NR
    LEFT OUTER JOIN FORNECEDOR    F ON P.CODFORN  = F.CODIGO
    LEFT OUTER JOIN TAB_LAB    LB   ON P.CODLAB   = LB.CODLAB

    WHERE e.id_loja in (1,2)
    AND PRODUTO STARTING WITH 'PROD'
    AND MINIMO >0
    AND P.STATUS = 'Ativo'

    GROUP BY  P.CODIGO,P.CODSETOR,P.CODFORN,P.CODLAB,P.PRECO_VENDA,P.PRECO_COMPRA,P.MINIMO,
              P.CODIGO_BARRAS,P.PRODUTO,P.UNIDADE,P.STATUS,P.PRIORIDADE,P.CONTROLFALTA ,E.ID_LOJA,ST.SETOR,F.FANTASIA ,LB.NOME_LAB) res

    GROUP BY res.CODIGO,res.CODSETOR,res.CODFORN,res.CODLAB,res.PRECO_VENDA,res.PRECO_COMPRA,res.MINIMO,
             res.CODIGO_BARRAS,res.PRODUTO,res.UNIDADE,res.STATUS,res.PRIORIDADE,res.CONTROLFALTA,res.SETOR,res.FANTASIA,
             res.NOME_LAB
    ORDER BY res.produto
GOSTEI 0
Marisiana Battistella

Marisiana Battistella

28/11/2014

Você tem que somar os valores totalizados, veja um exemplo: 
 ( SUM(res.estloja_1 ) + SUM(res.estloja_2 ) +  SUM(res.estloja_3) ) as total_estloja
GOSTEI 0
Emerson Santos

Emerson Santos

28/11/2014

B. tarde Marisiana tinha tentando desta forma
mais de que a coluna nao existe, fiz assim depois
do ultimo case coloquei uma virgula e o texto a baixo.
Da esse erro
SC ERROR CODE:335544569

ISC ERROR MESSAGE:
Dynamic SQL Error
SQL error code = -206
Column unknown
RES.ESTLOJA_1
At line 66, column 19


       CASE
         WHEN e.id_loja = 3 THEN
            SUM( E.QUANTIDADE   )
         END as estloja_3,
       CASE
         WHEN e.id_loja = 3 THEN
             SUM( P.MINIMO-E.QUANTIDADE   )
         END as floja_3,
         
        ( SUM(res.estloja_1 ) + SUM(res.estloja_2 ) +  SUM(res.estloja_3) ) as total_estloja
GOSTEI 0
Marisiana Battistella

Marisiana Battistella

28/11/2014

Mas não é no subselect que vc tem que incluir esse cálculo, pois vc está pegando valores de campos que são gerados pelo subselect.
"res" é o álias/apelido do retorno do subselect....
GOSTEI 0
Emerson Santos

Emerson Santos

28/11/2014

Marisiana deu certinho a verdade o problema que nao estava somando
é que se uma loja retorna-se null ele nao totalizava observei isso
coloquei um coalesce funcionou certinho.
Obrigado.
GOSTEI 0
Marisiana Battistella

Marisiana Battistella

28/11/2014

Por nada! =)
GOSTEI 0
POSTAR