Dúvida - Iniciante!

Dúvidas, dicas e truques de PL/SQL. Aqui também vão assuntos relacionados a pacotes, triggers, funções, Java-Stored Procedures, etc
Responder
reneanarcizo
Rank: Estagiário Sênior
Rank: Estagiário Sênior
Mensagens: 9
Registrado em: Qui, 11 Mar 2010 2:36 pm
Localização: Campinas - SP
Renê Narcizo

Galera perdoem minha ignorância, mas estou com uma dúvida!!!
Preciso fazer um select que teste e me traga o resultado do seguinte cenário:

Tenho a tabela PROCESSO com os seguintes campos:

Selecionar tudo

COD_PROCESSO
CLIENTE

Tenho também, a tabela CUSTOS_PROCESSO com os seguintes campos:

Selecionar tudo

COD_PROCESSO
DESC_CUSTO
VALOR_CUSTO

Imaginando-se o seguinte cenário:

Tabela PROCESSO:

Selecionar tudo

PROCESSO.COD_PROCESSO|PROCESSO.CLIENTE
1                                        | ALFA
Tabela CUSTOS_PROCESSO:

Selecionar tudo

CUSTOS_PROCESSO.COD_PROCESSO|CUSTOS_PROCESSO.DESC_CUSTO|CUSTOS_PROCESSO.VALOR_CUSTO
1   |   ARMAZENAGEM   |   R$ 100,00
1   |   FRETE                |   R$ 600,00
1   |   SEGURO             |   R$ 20,00

Gostaria de fazer um select que me traga o seguinte resultado:

Selecionar tudo

PROCESSO  |  CLIENTE  |  ARMAZENAGEM  |  FRETE  |  SEGURO  |  TOTAL
1                 |  ALFA       |     100,00            | 600,00 |  20,00       |720,00

Alguém saberia me explicar como faço isso??? Pois todo select que faço me retorna o resultado esperado, porém cada custo em 1 linha ou seja, saem 3 linhas, e eu preciso que seja em apenas 1...

Brigadão...

[]’s
victorhugomuniz
Moderador
Moderador
Mensagens: 1396
Registrado em: Sex, 01 Fev 2008 2:06 pm
Localização: Rio de Janeiro - RJ
Contato:
:D

Selecionar tudo

select p.COD_PROCESSO,
         p.CLIENTE ,
(select DESC_CUSTO from CUSTOS_PROCESSO where p.COD_PROCESSO = COD_PROCESSO and rownum = 1) as DESC_CUSTO_1 ,
(select VALOR_CUSTO from CUSTOS_PROCESSO where p.COD_PROCESSO = COD_PROCESSO and rownum = 1) as VALOR_CUSTO_1,
(select DESC_CUSTO from CUSTOS_PROCESSO where p.COD_PROCESSO = COD_PROCESSO and rownum = 2) as DESC_CUSTO_2 ,
(select VALOR_CUSTO from CUSTOS_PROCESSO where p.COD_PROCESSO = COD_PROCESSO and rownum = 2),
(select DESC_CUSTO from CUSTOS_PROCESSO where p.COD_PROCESSO = COD_PROCESSO and rownum = 3) as DESC_CUSTO_3 ,
(select VALOR_CUSTO from CUSTOS_PROCESSO where p.COD_PROCESSO = COD_PROCESSO and rownum = 3) as VALOR_CUSTO_3,
(VALOR_CUSTO_1 + VALOR_CUSTO_2 + VALOR_CUSTO_3) as Total
from PROCESSO p
paulaholti
Rank: Programador Pleno
Rank: Programador Pleno
Mensagens: 43
Registrado em: Sex, 29 Jan 2010 4:02 pm
Localização: sp
iniciante oracle pl/sql forms reports

reneanarcizo : Por curiosidade (para aprender) você pode postar sua select ? .

victorhugomuniz : Criei as tabelas conforme descrito no tópico e populei com duas linhas. Entendi a linha de raciocinio do select, mas somente está dando certo com rownum = 1 que no caso trás ARMAZEM custo 600,00.

Mas ao que entendi, jamais terá um rownum = 2 ou rownum = 3, porque vai contra a regra da subconsulta que não pode voltar com mais de uma linha. A claúsula Where já filtrou para ficar so com um rownum. (achoo!!!!!)

E a coluna TOTAL, não é reconhecida (ORA-00904: nome inválido de coluna). Acho que cai nesse exemplo aqui :

Selecionar tudo

SQL> SELECT 10 AS A, A+10 AS B FROM DUAL ;
SELECT 10 AS A, A+10 AS B FROM DUAL
                *
ERRO na linha 1:
ORA-00904: nome inválido de coluna


SQL> SELECT A+10 AS B FROM (SELECT 10 AS A FROM DUAL);

         B
----------
        20 
* Perdoa-me caso falei besteiras.

De resto não consegui resolver o select :oops: rs , somente se for uma linha em especifico e com referencias na "mão" .

Selecionar tudo

SQL> edit
Gravou arquivo AFIEDT.BUF

  1  SELECT Z.COD_PROCESSO,
  2         Z.CLIENTE,
  3         (SELECT VALOR_CUSTO  FROM CUSTOS_PROCESSOS
  4         WHERE DESC_CUSTO = 'ARMAZEM' AND COD_PROCESSO =1 ) ARMAZEM,
  5         (SELECT VALOR_CUSTO  FROM CUSTOS_PROCESSOS
  6         WHERE DESC_CUSTO = 'FRETE' AND COD_PROCESSO =1 ) FRETE,
  7         (SELECT VALOR_CUSTO  FROM CUSTOS_PROCESSOS
  8         WHERE DESC_CUSTO = 'SEGURO' AND COD_PROCESSO =1 ) SEGURO,
  9         TOTAL
 10         FROM PROCESSO Z,
 11         (SELECT SUM(B.VALOR_CUSTO) TOTAL
 12          FROM PROCESSO A,CUSTOS_PROCESSOS B
 13          WHERE a.COD_PROCESSO = 1 AND b.cod_processo=1)
 14* WHERE Z.COD_PROCESSO = 1
 15  
SQL> /

COD_PROCESSO CLIENTE       ARMAZEM      FRETE     SEGURO      TOTAL
------------ ---------- ---------- ---------- ---------- ----------
           1 ALFA              100        600         20        720

SQL> 
O B R I G A D A A T O D O S ! !
reneanarcizo
Rank: Estagiário Sênior
Rank: Estagiário Sênior
Mensagens: 9
Registrado em: Qui, 11 Mar 2010 2:36 pm
Localização: Campinas - SP
Renê Narcizo

Srs. Bom dia...

Segue abaixo o meu select (funcionou PARCIALMENTE):

Selecionar tudo

select 
  p.cod_processo, 
  p.cliente, 
  cp1.valor_custo, 
  cp2.valor_custo, 
  cp3.valor_custo
from 
  processo p, 
  custos_processo cp1, 
  custos_processo cp2, 
  custos_processo cp3
where 
  p.cod_processo = cp1.cod_processo
  and p.cod_processo = cp2.cod_processo
  and p.cod_processo = cp3.cod_processo
O único problema com o qual me deparei, é para os casos onde o processo não possua 1 ou 2 dos 3 custos (no meu caso, apenas 5 processos possuem os 3 custos), tentei utilizar "OR" ao invés de "AND", porém, a query demorou mais de 10 min e tive que cancelá-la!!!
paulaholti
Rank: Programador Pleno
Rank: Programador Pleno
Mensagens: 43
Registrado em: Sex, 29 Jan 2010 4:02 pm
Localização: sp
iniciante oracle pl/sql forms reports

rene, o seu select me voltou 53 linhas para duas linhas, acredito que não seja por esse caminho.

não sei se é fácil ou não essa select, para mim ainda está dificil. Sei que com uma SP, daria para resolver melhor. Ou é muito fácil e não to enxergando ou é complicadinho mesmo.

Achei esse link, que parece ter a solução parecida para o que você quer :

http://glufke.net/oracle/viewtopic.php? ... horizontal

Mas ainda não testei por ele.
reneanarcizo
Rank: Estagiário Sênior
Rank: Estagiário Sênior
Mensagens: 9
Registrado em: Qui, 11 Mar 2010 2:36 pm
Localização: Campinas - SP
Renê Narcizo

Conversei com um amigo meu que me passou a solução...

Ficou simples e 100% funcional:

Selecionar tudo

select 
  p.cod_processo, 
  p.cliente, 
  cp1.valor_custo, 
  cp2.valor_custo, 
  cp3.valor_custo 
from 
  processo p LEFT OUTER JOIN, 
  custos_processo cp1 ON p.cod_processo = cp1.NUM_DOCUMENTO AND cp1.COD_DESPESA = 'ARM', 
  LEFT OUTER JOIN custos_processo cp2 ON p.cod_processo = cp2.NUM_DOCUMENTO AND cp2.COD_DESPESA = 'FRETE INTERNO', 
  LEFT OUTER JOIN custos_processo cp3 ON p.cod_processo = cp3.NUM_DOCUMENTO AND cp3.COD_DESPESA = 'FRETE'
paulaholti
Rank: Programador Pleno
Rank: Programador Pleno
Mensagens: 43
Registrado em: Sex, 29 Jan 2010 4:02 pm
Localização: sp
iniciante oracle pl/sql forms reports

Deu certo direitinho aqui

Selecionar tudo

SQL> EDIT
Gravou arquivo afiedt.buf

  1  select
  2  p.cod_processo,
  3  p.cliente,
  4  cp1.valor_custo,
  5  cp2.valor_custo,
  6  cp3.valor_custo
  7  from
  8  processo p LEFT OUTER JOIN
  9  custos_processos cp1 ON p.cod_processo = cp1.cod_processo AND cp1.DESC_CUSTO = 'ARMAZ
 10  LEFT OUTER JOIN custos_processos cp2 ON p.cod_processo = cp2.cod_processo AND cp2.DES
 11* LEFT OUTER JOIN custos_processos cp3 ON p.cod_processo = cp3.cod_processo AND cp3.DES
SQL> /

COD_PROCESSO CLIENTE    VALOR_CUSTO VALOR_CUSTO VALOR_CUSTO
------------ ---------- ----------- ----------- -----------
           1 ALFA               100         600          20
           2 BETA               200         500          40

SQL> 
Apenas retirei as virgulas ante do LEFT OUTER JOIN

Selecionar tudo

select
p.cod_processo,
p.cliente,
cp1.valor_custo,
cp2.valor_custo,
cp3.valor_custo
from
processo p LEFT OUTER JOIN
custos_processos cp1 ON p.cod_processo = cp1.cod_processo AND cp1.DESC_CUSTO = 'ARMAZEM'
LEFT OUTER JOIN custos_processos cp2 ON p.cod_processo = cp2.cod_processo AND cp2.DESC_CUSTO = 'FRETE'
LEFT OUTER JOIN custos_processos cp3 ON p.cod_processo = cp3.cod_processo AND cp3.DESC_CUSTO = 'SEGURO' 
/
paulaholti
Rank: Programador Pleno
Rank: Programador Pleno
Mensagens: 43
Registrado em: Sex, 29 Jan 2010 4:02 pm
Localização: sp
iniciante oracle pl/sql forms reports

Pra quem tem Oracle 11.1, pesquisando achei uma função não documentada chamada WM_CONCAT. Ela faz algo parecido com os selects do tópico, com muita facilidade .

exemplo do site :

Selecionar tudo

CREATE TABLE t (
col1 VARCHAR2(5),
col2 VARCHAR2(20));

INSERT INTO t VALUES (111, 'This');
INSERT INTO t VALUES (111, 'is');
INSERT INTO t VALUES (111, 'a');
INSERT INTO t VALUES (111, 'test');
INSERT INTO t VALUES (222, 'This is not');

SELECT * FROM t;

col concat format a40

SELECT col1, wmsys.wm_concat(col2) CONCAT
FROM t
GROUP BY col1;

SELECT col1, TRANSLATE(wmsys.wm_concat(col2), 'A,', 'A ') CONCAT
FROM t
GROUP BY col1; 
Aqui não funciona porque é a versao 9, mas esse select retornaria assim : (para o primeiro select)

Selecionar tudo

col1  CONCAT
-----------------------------
111  this, is, a, test
222  this is not 
* Acho que basta deixar WM.CONCAT(COL2) que funciona, não sei o que é esse WMSYS.

Se alguém souber algo parecido para o 9i , avisa aqui ..

O B R I G A D A A T O D O S!!!
Responder
  • Informação
  • Quem está online

    Usuários navegando neste fórum: Nenhum usuário registrado e 18 visitantes