[firebase-br] RES: RES: Duvida em como fazer um sql

Hugo Fabrício webstation em ig.com.br
Qua Ago 5 14:44:04 -03 2009


Ivan Guimarães Meirelles,


Fiz alguns testes, e no meu caso que já tenho o banco pronto e populado
teria que refazer todo o processo
Me deu uma luz mas ainda não é totalmente isto. 
Estou tentando algo com o substring, com o substring eu consigo agrupar por
cada nível
Tipo substring (campo, 1,1) nível 1
     substring (campo, 2,2) nível 2
etc o único problema e juntar tudo no mesmo select

Select
SubString(CODCCUSTO from 1 for 1) as FNivel,
sum(valordup)

from DUPLICATA
group by SubString(CODCCUSTO from 1 for 1)

Obrigado pela dica


Hugo Fabrício

-----Mensagem original-----
De: lista-bounces em firebase.com.br [mailto:lista-bounces em firebase.com.br] Em
nome de Wanderson Pereira
Enviada em: terça-feira, 4 de agosto de 2009 10:17
Para: FireBase
Assunto: Re: [firebase-br] RES: Duvida em como fazer um sql

Hugo, veja se o email abaixo te atende:

Hugo Fabrício escreveu:
> Tenho uma tabela de Centro de custo desta forma
> CODCCUSTO – String 12
> DESCRIÇÃO  -  String 50
>
> Tenho outra Tabela Contas
>
> N_DOCUMENTO – String 30
> CODCCUSTO – Chave Estrangeira da tabela Centro de Custo
> Valor_Documento – Numeric (15,2)
>
>
> Dentro da tabela centro de custo tem os seguintes cadastros
> 1.	Receita
> 1.1	Vendas Internas
> 1.1.1	Escola
> 1.1.2	Escritório
> 1.2	Vendas Externas
> 1.2.1	Livro
> 1.2.2	Brinquedos
>
> 2.	Despesas
> 2.1	Fornecedores
> 2.1.1	– Nacional
> 2.1.2	– Importado
> 2.2 – Escritório
> 2.2.1 – Materiais de limpeza
> 2.2.2 – Materiais de Escritório
>
> Tenho que montar um relatório que totalize por níveis todas as contas
> lançadas
> O centro de custo sempre vai ser lançada no ultimo nível ou seja no 1.1.1
ou
> 1.2.1 etc
>
> Exemplo:
>
> Lançamentos :
>
>  R$ 50,00   centro de custo:1.1.2 Escritório
>  R$ 100,00  centro de custo :1.2.2 Brinquedos
>
> Relatório 
>
> 1 Receitas  R$ 150,00
> 1.1	Vendas Internas R$ 50,00
> 1.2  Vendas Externas R$ 100,00 
>
> Se alguém puder me dar uma dica agradeço,
>
> Obs.: Uso FireBird 1.5.4
>
>
> Hugo Fabrício
>
>   
+++++++++++++++++++++++++++++

Como havia prometido, segue ai um passo-a-passo para quem quiser trabalhar
com hierarquia no Firebird.

O exemplo que vou montar aqui será baseado em um cadastro de funcionários e
seus supervisores, que também são funcionários:

1 - Crie a seguinte tabela:

CREATE TABLE FUNCIONARIOS
(
    IDFUNCIONARIO INTEGER NOT NULL,
    NOME VARCHAR(30),
    IDSUPERVISOR INTEGER
);

2 - Defina o campo IDFUNCIONARIO como chave primária:

alter table FUNCIONARIOS
     add constraint PK_FUNCIONARIOS primary key (IDFUNCIONARIO);
                
3 - Crie um auto-relacionamento nessa tabela, ligando IDSUPERVISOR a
IDFUNCIONARIO, criando uma chave estrangeira:

alter table FUNCIONARIOS
     add constraint FK_FUNC_SUPE foreign key (IDSUPERVISOR)
     references FUNCIONARIOS(IDFUNCIONARIO) on update CASCADE;
                
4 - Vamos alimentar essa tabela. Obs.: O funcionário que não tiver
supervisor, terá o campo IDSUPERVISOR como NULL:

insert into funcionarios(idfuncionario, nome, idsupervisor) values(1, 'MARIA
APARECIDA', null);
insert into funcionarios(idfuncionario, nome, idsupervisor) values(2, 'JOAO
DA SILVA', 1);
insert into funcionarios(idfuncionario, nome, idsupervisor) values(3,
'AGUINALDO OLIVEIRA', 1);
insert into funcionarios(idfuncionario, nome, idsupervisor) values(4,
'RENATA FRANCA', 3);
insert into funcionarios(idfuncionario, nome, idsupervisor) values(5,
'GISELE SOUZA', 3);
insert into funcionarios(idfuncionario, nome, idsupervisor) values(6, 'LAURA
BONIELI', 3);
insert into funcionarios(idfuncionario, nome, idsupervisor) values(7, 'HILDA
CONCEICAO', 6);
insert into funcionarios(idfuncionario, nome, idsupervisor) values(8,
'RICARDO SOUZA', 6);
insert into funcionarios(idfuncionario, nome, idsupervisor) values(9,
'MARCOS DUILIO', 8);

5 - Precisaremos aqui, declarar duas UDF's: STRLEN e LPAD, essas UDF's fazem
parte da instalação do Firebird:

DECLARE EXTERNAL FUNCTION STRLEN
    CSTRING(32767)
RETURNS INTEGER BY VALUE
ENTRY_POINT 'IB_UDF_strlen' MODULE_NAME 'ib_udf';

DECLARE EXTERNAL FUNCTION LPAD
    CSTRING(255),
    INTEGER,
    CSTRING(1)
RETURNS CSTRING(255) FREE_IT
ENTRY_POINT 'IB_UDF_lpad' MODULE_NAME 'ib_udf';

6 - Criaremos agora uma Stored Procedure Recursiva para similar uma
funcionalidade do Oracle, quando se trata de hierarquia:

CREATE PROCEDURE FUNC_HIERARQ (
    id integer,
    idinicial varchar(30))
returns (
    codigo varchar(30),
    nome varchar(30),
    idfuncionario integer,
    idsupervisor integer,
    nivel integer)
as
declare variable aux integer;
begin

    if (id is null) then
        begin
            for
                select lpad(FC.idfuncionario, 2, '0'), FC.nome,
FC.idfuncionario, FC.idsupervisor 
      from FUNCIONARIOS FC where FC.idsupervisor is null
            into
                :codigo, :nome, :idfuncionario, :idsupervisor
            do
                begin
                    nivel = (strlen(:codigo) + 1) / 3;
                    suspend;
                    for select FH.codigo, FH.nome, FH.idfuncionario,
FH.idsupervisor 
    from FUNC_HIERARQ(:codigo, :codigo) FH
                    into :codigo, :nome, :idfuncionario, :idsupervisor
                    do
                        begin
                            nivel = (strlen(:codigo) + 1) / 3;
                            suspend;
                        end
                end
        end

    if (not id is null) then
        begin
            for
                select coalesce(:idinicial,'') || '.' ||
lpad(FC.idfuncionario, 2, '0'), FC.nome, FC.idfuncionario, FC.idsupervisor
      from FUNCIONARIOS FC where FC.idsupervisor = :id
            into
                :codigo, :nome, :aux, :idsupervisor
            do
                begin
                    idfuncionario = :aux;
                    nivel = (strlen(:codigo) + 1) / 3;
                    suspend;
                    for select FH.codigo, FH.nome, FH.idfuncionario,
FH.idsupervisor from FUNC_HIERARQ(:aux, :codigo) FH
                    into :codigo, :nome, :idfuncionario, :idsupervisor
                    do
                        begin
                            nivel = (strlen(:codigo) + 1) / 3;
                            suspend;
                        end
                end
        end

end;

7 - A Stored Procedure abaixo mostra um exemplo de utilização da procedure
FUNC_HIERARQ:

CREATE PROCEDURE LISTA_FUNCIONARIOS returns ( relacao varchar(100) )
as
begin
    for SELECT LPAD(nome, STRLEN(nome) + (nivel * 10) - 10, ' ') FROM
func_hierarq(null, null)
        into :relacao do suspend;
end;

Depois de ter criado as procedures, para usá-las basta fazer assim:

select * from func_hierarq(null, null) --<-- e vc terá o seguinte retorno:

CODIGO         | NOME               | IDFUNCIONARIO | IDSUPERVISOR | NIVEL
---------------+--------------------+---------------+--------------+-------
01             | MARIA APARECIDA    | 1             | <NULL>       | 1
01.02          | JOAO DA SILVA      | 2             | 1            | 2
01.03          | AGUINALDO OLIVEIRA | 3             | 1            | 2
01.03.04       | RENATA FRANCA      | 4             | 3            | 3
01.03.05       | GISELE SOUZA       | 5             | 3            | 3
01.03.06       | LAURA BONIELI      | 6             | 3            | 3
01.03.06.07    | HILDA CONCEICAO    | 7             | 6            | 4
01.03.06.08    | RICARDO SOUZA      | 8             | 6            | 4
01.03.06.08.09 | MARCOS DUILIO      | 9             | 8            | 5

ou então:

select * from lista_funcionarios --<-- e vc terá o seguinte retorno:

RELACAO
----------------------------------
MARIA APARECIDA
     JOAO DA SILVA
     AGUINALDO OLIVEIRA
          RENATA FRANCA
          GISELE SOUZA
          LAURA BONIELI
               HILDA CONCEICAO
               RICARDO SOUZA
                    MARCOS DUILIO


Agora é com vocês... isso foi apenas uma idéia do que é possível se fazer
com o FireBird... basta ter um pouco de criatividade.
Por exemplo: Grupos e subgrupos de mercadorias com hierarquia, Centros de
Custo, Planos de Contas, Balancetes e muito mais...

Pedras, Xingamentos, Elogios, Dúvidas e/ou Sugestões... podem me contatar
por e-mail.

Um abraço a todos...

Ivan Guimarães Meirelles
Três Lagoas/MS - igmeirelles em gmail.com




-- 
[ ]s
\\|//
<o o>
+----------------OOo-V-oOO---------------+
| \/\/anderson R. P. P. - Cabo Frio - RJ |
+--------=====--------------=====--------+
| mailto:wpereira em ami.com.br |
| UIN - 89166786 |
+----------------------------------------+
* Adesivo de carro: "Respeite a mulher do próximo, principalmente se o 
próximo estiver muito próximo"*

______________________________________________
FireBase-BR (www.firebase.com.br) - Hospedado em www.locador.com.br
Para saber como gerenciar/excluir seu cadastro na lista, use:
http://www.firebase.com.br/fb/artigo.php?id=1107
Para consultar mensagens antigas: http://firebase.com.br/pesquisa





Mais detalhes sobre a lista de discussão lista