Utilizando Common Table Expressions (CTEs) para estruturação de MENUs – Parte I

Boa Noite Pessoal,

Está realmente difícil estar presente e o backlog de atividades, artigos e tarefas está enorme. Bem, hoje vou postar uma dica rápida. Estou moderando diariamente os fóruns de SQL Server do MSDN e do Technet e hoje vi uma dúvida interessante envolvendo as CTEs. A dúvida é na montagem, ordenação e exibição dos registros de um plano de contas. Vejamos um exemplo prático:

A figura acima mostra a estrutura de um plano de contas de uma empresa fictícia. Ao lado da legenda do plano está relacionado também o seu ID. O script abaixo cria uma tabela e grava alguns registros para reproduzir o pano:

— Cria uma tabela de Plano de Contas
CREATE TABLE PlanoContas (
    ID INT NOT NULL,
    IDSup INT NULL,
    Descricao VARCHAR(50))

— Adiciona as constraints
ALTER TABLE PlanoContas ADD CONSTRAINT PK_Plano PRIMARY KEY (ID)

ALTER TABLE PlanoContas ADD CONSTRAINT FK_Plano FOREIGN KEY (IDSup)
REFERENCES PlanoContas (ID)

— Insere os registros no Plano de Contas
INSERT INTO PlanoContas VALUES (01,NULL,‘Ativo’)
INSERT INTO PlanoContas VALUES (02,01,‘Ativo Circulante’)
INSERT INTO PlanoContas VALUES (03,02,‘Disponibilidades’)
INSERT INTO PlanoContas VALUES (04,03,‘Caixa’)
INSERT INTO PlanoContas VALUES (05,04,‘Caixa Geral’)
INSERT INTO PlanoContas VALUES (06,03,‘Contas Bancárias’)
INSERT INTO PlanoContas VALUES (07,06,‘Conta Corrente’)
INSERT INTO PlanoContas VALUES (08,06,‘Conta Poupança’)

Há inúmeras possibilidades com uma CTE recursiva, mas uma necessidade comum, é montar um resultset para exibir exatamente o plano de contas com as respectivas identações ou ainda exibir os caminhos para chegar a uma determinada conta. Vejamos então algumas consultas utilizando as recursive CTEs.

— Mostra os caminhos até as contas
;WITH Res (ID, IDSup, Descricao) As (
    SELECT ID, IDSup, CAST(Descricao As VARCHAR(MAX)) FROM PlanoContas
    WHERE IDSup IS NULL
    UNION ALL
    SELECT F.ID, F.IDSup, P.Descricao + ‘ -> ‘ + F.Descricao
    FROM Res As P
    INNER JOIN PlanoContas As F ON P.ID = F.IDSup)

SELECT ID, Descricao FROM Res
ORDER BY ID

ID Descricao
01 Ativo
02 Ativo -> Ativo Circulante
03 Ativo -> Ativo Circulante -> Disponibilidades
04 Ativo -> Ativo Circulante -> Disponibilidades -> Caixa
05 Ativo -> Ativo Circulante -> Disponibilidades -> Caixa -> Caixa Geral
06 Ativo -> Ativo Circulante -> Disponibilidades -> Contas Bancárias
07 Ativo -> Ativo Circulante -> Disponibilidades -> Contas Bancárias -> Conta Corrente
08 Ativo -> Ativo Circulante -> Disponibilidades -> Contas Bancárias -> Conta Poupança

— Mostra as contas aninhadas
;WITH Res (ID, IDSup, Descricao, Nivel) As (
    SELECT ID, IDSup, Descricao, 1 As Nivel FROM PlanoContas
    WHERE IDSup IS NULL
    UNION ALL
    SELECT F.ID, F.IDSup, F.Descricao, Nivel + 1
    FROM Res As P
    INNER JOIN PlanoContas As F ON P.ID = F.IDSup)

SELECT ID, ‘|’ + REPLICATE(‘–‘,Nivel) + Descricao As Descricao FROM Res
ORDER BY ID

ID Descricao
01 |–Ativo
02 |—-Ativo Circulante
03 |——Disponibilidades
04 |——–Caixa
05 |———-Caixa Geral
06 |——–Contas Bancárias
07 |———-Conta Corrente
08 |———-Conta Poupança

Se o plano de contas ficasse estático ou se as contas sempre surgissem abaixo das contas existentes, seria fácil utilizar as consultas acima, mas como será que as consultas se comportam quando há inserções no meio das contas já existentes ? Vejamos a seguir:

INSERT INTO PlanoContas VALUES (09,02,‘Créditos’)
INSERT INTO PlanoContas VALUES (10,09,‘Duplicatas’)
INSERT INTO PlanoContas VALUES (11,10,‘Duplicatas Descontadas’)
INSERT INTO PlanoContas VALUES (12,10,‘Duplicatas a Receber’)
INSERT INTO PlanoContas VALUES (13,06,‘Conta Investimento’)

As novas contas mudam o organograma conforme a figura abaixo:

O novo plano de contas adicionou novos nós em sequência (09, 10, 11 e 12), mas uma nova conta (13) foi adicionada no plano e não manteve a ordem já que o 13 supostamente deve aparecer antes do 09. Vejamos como as consultas se comportam nesse caso

— Mostra os caminhos até as contas
;WITH Res (ID, IDSup, Descricao) As (
    SELECT ID, IDSup, CAST(Descricao As VARCHAR(MAX)) FROM PlanoContas
    WHERE IDSup IS NULL
    UNION ALL
    SELECT F.ID, F.IDSup, P.Descricao + ‘ -> ‘ + F.Descricao
    FROM Res As P
    INNER JOIN PlanoContas As F ON P.ID = F.IDSup)

SELECT ID, Descricao FROM Res
ORDER BY ID

ID Descricao
01 Ativo
02 Ativo -> Ativo Circulante
03 Ativo -> Ativo Circulante -> Disponibilidades
04 Ativo -> Ativo Circulante -> Disponibilidades -> Caixa
05 Ativo -> Ativo Circulante -> Disponibilidades -> Caixa -> Caixa Geral
06 Ativo -> Ativo Circulante -> Disponibilidades -> Contas Bancárias
07 Ativo -> Ativo Circulante -> Disponibilidades -> Contas Bancárias -> Conta Corrente
08 Ativo -> Ativo Circulante -> Disponibilidades -> Contas Bancárias -> Conta Poupança
09 Ativo -> Ativo Circulante -> Créditos
10 Ativo -> Ativo Circulante -> Créditos -> Duplicatas
11 Ativo -> Ativo Circulante -> Créditos -> Duplicatas -> Duplicatas Descontadas
12 Ativo -> Ativo Circulante -> Créditos -> Duplicatas -> Duplicatas a Receber
13 Ativo -> Ativo Circulante -> Disponibilidades -> Contas Bancárias -> Conta Investimento

— Mostra as contas aninhadas
;WITH Res (ID, IDSup, Descricao, Nivel) As (
    SELECT ID, IDSup, Descricao, 1 As Nivel FROM PlanoContas
    WHERE IDSup IS NULL
    UNION ALL
    SELECT F.ID, F.IDSup, F.Descricao, Nivel + 1
    FROM Res As P
    INNER JOIN PlanoContas As F ON P.ID = F.IDSup)

SELECT ID, ‘|’ + REPLICATE(‘–‘,Nivel) + Descricao As Descricao FROM Res
ORDER BY ID

ID Descricao
01 |–Ativo
02 |—-Ativo Circulante
03 |——Disponibilidades
04 |——–Caixa
05 |———-Caixa Geral
06 |——–Contas Bancárias
07 |———-Conta Corrente
08 |———-Conta Poupança
09 |——Créditos
10 |——–Duplicatas
11 |———-Duplicatas Descontadas
12 |———-Duplicatas a Receber
13 |———-Conta Investimento

Na primeira consulta, o caminho até a conta investimento (13) está correto, mas não apareceu na sequência esperada, ou seja, logo após a conta poupança (08) igual ao plano de contas. Na segunda consulta, a identação da conta investimento está correta (5⁰ nível), mas visivelmente aparece após as duplicatas e dá a falsa idéia de que essa conta está abaixo da conta "Duplicatas" o que não é verdade. A grande questão é elaborar a ordem em que as contas precisam ser recuperadas e por mais simples que pareça ela não é óbvia. Não é eficaz fazer a ordenação por ID e nem por nível. Demora algum tempo para perceber, mas a ordenação deve combinar ambos os fatores, ou seja, para um dado ID, organiza-se suas contas inferiores por níveis. A dificuldade está em elaborar o "ORDER BY". Uma alternativa é recorrer a materialização dos caminhos conforme o script abaixo:

;WITH Res (ID, IDSup, Descricao, Caminho) As (
    SELECT
        ID, IDSup, CAST(Descricao As VARCHAR(MAX)),
        CAST(ID As VARCHAR(MAX)) + ‘.’ FROM PlanoContas
    WHERE IDSup IS NULL
    UNION ALL
    SELECT
        F.ID, F.IDSup, P.Descricao + ‘ -> ‘ +
        F.Descricao, P.Caminho + CAST(F.ID As VARCHAR(MAX)) + ‘.’
    FROM Res As P
    INNER JOIN PlanoContas As F ON P.ID = F.IDSup)

SELECT ID, Descricao, Caminho FROM Res ORDER BY Caminho

ID Descricao Caminho
01 Ativo 1.
02 Ativo -> Ativo Circulante 1.2.
03 Ativo -> Ativo Circulante -> Disponibilidades 1.2.3.
04 Ativo -> Ativo Circulante -> Disponibilidades -> Caixa 1.2.3.4.
05 Ativo -> Ativo Circulante -> Disponibilidades -> Caixa -> Caixa Geral 1.2.3.4.5.
06 Ativo -> Ativo Circulante -> Disponibilidades -> Contas Bancárias 1.2.3.6.
13 Ativo -> Ativo Circulante -> Disponibilidades -> Contas Bancárias -> Conta Investimento 1.2.3.6.13.
07 Ativo -> Ativo Circulante -> Disponibilidades -> Contas Bancárias -> Conta Corrente 1.2.3.6.7.
08 Ativo -> Ativo Circulante -> Disponibilidades -> Contas Bancárias -> Conta Poupança 1.2.3.6.8.
09 Ativo -> Ativo Circulante -> Créditos 1.2.9.
10 Ativo -> Ativo Circulante -> Créditos -> Duplicatas 1.2.9.10.
11 Ativo -> Ativo Circulante -> Créditos -> Duplicatas -> Duplicatas Descontadas 1.2.9.10.11.
12 Ativo -> Ativo Circulante -> Créditos -> Duplicatas -> Duplicatas a Receber 1.2.9.10.12.

A idéia de materializar o caminho deixou as contas um pouco mais organizadas. Entretanto, os mais observadores irão perceber um pequeno erro que pode inclusive passar desapercebido por alguns. A conta investimento (13) foi recuperada antes das contas corrente (7) e poupança (8). Se o ID da conta investimento é 13 porque então essa conta foi retornada antes das contas de ID 7 e 8 ? A resposta é bem simples. Se considerássemos que 7 e 8 são números, o 13 viria posteriormente, mas considerando que a coluna "caminho" é um VARCHAR, a string 13 é recuperada antes das strings 7 e 8 e por isso ocorre essa pequena divergência. Para resolver esse pequeno detalhe é possível utilizar uma máscara que faça com que a ordem numérica e a ordem alfanumérica sejam iguais. Supondo um plano de até mil contas, pode-se alterar a consulta para a solução seguinte:

;WITH Res (ID, IDSup, Descricao, Caminho) As (
    SELECT
        ID, IDSup, CAST(Descricao As VARCHAR(MAX)),
        REPLICATE(‘0’,3-ROUND(LOG10(ID),0,1)) +
        CAST(ID As VARCHAR(MAX)) + ‘.’ FROM PlanoContas
    WHERE IDSup IS NULL
    UNION ALL
    SELECT
        F.ID, F.IDSup, P.Descricao + ‘ -> ‘ +
        F.Descricao, P.Caminho + REPLICATE(‘0’,3-ROUND(LOG10(F.ID),0,1)) +
        CAST(F.ID As VARCHAR(MAX)) + ‘.’
    FROM Res As P
    INNER JOIN PlanoContas As F ON P.ID = F.IDSup)

SELECT ID, Descricao, Caminho FROM Res ORDER BY Caminho

ID Descricao Caminho
01 Ativo 0001.
02 Ativo -> Ativo Circulante 0001.0002.
03 Ativo -> Ativo Circulante -> Disponibilidades 0001.0002.0003.
04 Ativo -> Ativo Circulante -> Disponibilidades -> Caixa 0001.0002.0003.0004.
05 Ativo -> Ativo Circulante -> Disponibilidades -> Caixa -> Caixa Geral 0001.0002.0003.0004.0005.
06 Ativo -> Ativo Circulante -> Disponibilidades -> Contas Bancárias 0001.0002.0003.0006.
13 Ativo -> Ativo Circulante -> Disponibilidades -> Contas Bancárias -> Conta Corrente 0001.0002.0003.0006.0007.
07 Ativo -> Ativo Circulante -> Disponibilidades -> Contas Bancárias -> Conta Poupança 0001.0002.0003.0006.0008.
08 Ativo -> Ativo Circulante -> Disponibilidades -> Contas Bancárias -> Conta Investimento 0001.0002.0003.0006.0013.
09 Ativo -> Ativo Circulante -> Créditos 0001.0002.0009.
10 Ativo -> Ativo Circulante -> Créditos -> Duplicatas 0001.0002.0009.0010.
11 Ativo -> Ativo Circulante -> Créditos -> Duplicatas -> Duplicatas Descontadas 0001.0002.0009.0010.0011.
12 Ativo -> Ativo Circulante -> Créditos -> Duplicatas -> Duplicatas a Receber 0001.0002.0009.0010.0012.

Agora me parece que a estrutura está correta e adequada para apresentação. Utilizando a mesma estratégia da coluna materializada chamada caminho, podemos arrumar a consulta baseada em aninhamento.

;WITH Res (ID, IDSup, Descricao, Nivel, Caminho) As (
    SELECT
        ID, IDSup, Descricao, 1 As Nivel,
        REPLICATE(‘0’,3-ROUND(LOG10(ID),0,1)) +
        CAST(ID As VARCHAR(MAX)) + ‘.’ FROM PlanoContas
    WHERE IDSup IS NULL
    UNION ALL
    SELECT
        F.ID, F.IDSup, F.Descricao, Nivel + 1,
        P.Caminho + REPLICATE(‘0’,3-ROUND(LOG10(F.ID),0,1)) +
        CAST(F.ID As VARCHAR(MAX)) + ‘.’
    FROM Res As P
    INNER JOIN PlanoContas As F ON P.ID = F.IDSup)

SELECT
    ID, ‘|’ + REPLICATE(‘–‘,Nivel) + Descricao As Descricao,
    Nivel, Caminho
FROM Res
ORDER BY Caminho

ID Descricao Nível Caminho
01 |–Ativo 1 0001.
02 |—-Ativo Circulante 2 0001.0002.
03 |——Disponibilidades 3 0001.0002.0003.
04 |——–Caixa 4 0001.0002.0003.0004.
05 |———-Caixa Geral 5 0001.0002.0003.0004.0005.
06 |——–Contas Bancárias 4 0001.0002.0003.0006.
07 |———-Conta Corrente 5 0001.0002.0003.0006.0007.
08 |———-Conta Poupança 5 0001.0002.0003.0006.0008.
09 |———-Conta Investimento 5 0001.0002.0003.0006.0013.
10 |——Créditos 3 0001.0002.0009.
11 |——–Duplicatas 4 0001.0002.0009.0010.
12 |———-Duplicatas Descontadas 5 0001.0002.0009.0010.0011.
13 |———-Duplicatas a Receber 5 0001.0002.0009.0010.0012.

Agora sim, ambas as consultas estão corretas e acredito exibir adequadamente o resultado esperado. Espero que essa dica seja útil aqueles que tem de lidar com uma representação hierárquica baseada no modelo adjacente e que tenham que apresentar os resultados com base na posição hierárquica.

[ ]s,

Gustavo

7 Respostas para “Utilizando Common Table Expressions (CTEs) para estruturação de MENUs – Parte I

  1. Excelente artigo Gustavo. Apesar de existir o tipo Hierarchy ID, o mesmo é um pouco complicado e ainda não vi o seu uso em ambientes de produção.Dica simples e rápida, parabéns.Abraço,Demétrio Silva

  2. Oi Demétrio,Pois é. Apesar de existir o HierarchyID ele é bastante complicado (hehehe). Acho que tirando a questão dos índices, esse tipo de dados é uma tentativa meio frustrada, pois, não vejo ninguém utilizá-lo. Há coisas que a CTE faz que não imagino como fazer com o HierarchyID. Mas enfim, mas uma possibilidade.[ ]s,

  3. Renan Henrique

    ;WITH Res (ID, IDSup, Descricao, Caminho) As (
    SELECT
    ID, IDSup, CAST(Descricao As VARCHAR(MAX)),
    RIGHT(‘0000’ + CAST(ID AS VARCHAR(MAX)),4) + ‘.’
    FROM PlanoContas
    WHERE IDSup IS NULL
    UNION ALL
    SELECT
    F.ID, F.IDSup, P.Descricao + ‘ -> ‘ +
    F.Descricao, P.Caminho + REPLICATE(‘0’,3-ROUND(LOG10(F.ID),0,1)) +
    CAST(F.ID As VARCHAR(MAX)) + ‘.’
    FROM Res As P
    INNER JOIN PlanoContas As F ON P.ID = F.IDSup)

    SELECT ID, Descricao, Caminho FROM Res ORDER BY Caminho

    Assim não fica mais facil? Quando vi aquele, log10, round, replicate, até assustei, até conseguir ler aquilo e entender o que está trazendo, complica.

    De qualquer forma, belo post.

    • Oi Renan,

      A idéia do RIGHT é legal, mas se você fixar 0000 estará limitando suas possibilidades. Irá funcionar em 99% dos casos, pois, será difícil uma estrutura hierárquica tão grande. Entretanto, se acontecer, o RIGHT pode não funcionar. Ainda assim, valeu pela sugestão. Aplicável na esmagora maioria.

      [ ]s,

      Gustavo

  4. Parabens mto bom e util este post

  5. Olá Gustavo bom dia. Andei estudando as CTEs mas ainda restam algumas dúvidas principalmente na execução das CTEs recursisvas. Expliquei a minha dúvida no forum do sql server. Você poderia me ajudar tirando essa dúvida? Obrigado, o link é:

    http://social.msdn.microsoft.com/Forums/pt-BR/transactsqlpt/thread/b092a634-5130-425b-9cba-479b586c9d6d

Deixe uma resposta

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair / Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair / Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair / Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair / Alterar )

Conectando a %s