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

Publicado em 13/10/2010 | Deixe um comentário

Boa Noite Pessoal,

Na parte I do artigo, demonstrei como utilizar as recursive CTEs para exibir a montagem correta de um plano de contas. A maior dificuldade é realmente garantir a ordem correta de exibição dos itens, uma vez que itens posteriores podem ser inseridos no meio das contas já existentes. Minha solução foi baseada na materialização dos caminhos até a conta, motando assim uma coluna para ordenação, mas hoje pensei que talvez existam outras necessidades. Vou recolocar o plano para mostrar algumas outras consultas interessantes.

— 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’)
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’)

;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.

A montagem da coluna "Caminho" foi necessária para garantir a ordem correta, fazendo assim com que a conta investimento (ID 13) ficasse logo abaixo das contas correnta e poupança de acordo com a ordem de cadastro. Será no entanto, que a ordem de cadastro é a ordem desejada ? E se fosse necessário exibir os itens pode ordem alfabética ? A consulta abaixo mostra como fazê-lo:

;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,
    LEFT(Caminho,LEN(Caminho)-5) As CaminhoSuperior
FROM Res
ORDER BY
    CaminhoSuperior, Descricao

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

A montagem da coluna "CaminhoSuperior" reflete exatamente o caminho até o "pai" de uma determinada conta. Uma vez que determinadas contas tenham o mesmo pai (e por consequência o mesmo valor para a coluna "CaminhoSuperior"), basta ordená-las pela descrição da conta exatamente da mesma forma que o ORDER BY realiza. É possível evitar o uso do LEN, através de uma pequena adaptação na CTE aproveitando a montagem da coluna "Caminho" antes da recursividade:

;WITH Res (ID, IDSup, Descricao, Caminho, CaminhoSuperior) As (
    SELECT
        ID, IDSup, CAST(Descricao As VARCHAR(MAX)),
        REPLICATE(‘0’,3-ROUND(LOG10(ID),0,1)) +
        CAST(ID As VARCHAR(MAX)) + ‘.’,
        CAST(” 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)) + ‘.’, Caminho
    FROM Res As P
    INNER JOIN PlanoContas As F ON P.ID = F.IDSup)

SELECT
    ID, Descricao, Caminho, CaminhoSuperior
FROM Res
ORDER BY
    CaminhoSuperior, Descricao

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

Não que seja uma necessidade comum, mas caso alguém deseje converter um modelo adjacente para um modelo de caminhos materializados, segue a CTE que pode fazê-lo.

;WITH Res (ID, IDSup, Descricao, Caminho, CaminhoSuperior, CaminhoMaterializado) As (
    SELECT
        ID, IDSup, CAST(Descricao As VARCHAR(MAX)),
        REPLICATE(‘0’,3-ROUND(LOG10(ID),0,1)) +
        CAST(ID As VARCHAR(MAX)) + ‘.’,
        CAST(” As VARCHAR(MAX)),
        CAST(1 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)) + ‘.’, Caminho,
        CaminhoMaterializado + ‘.’ +
            CAST(ROW_NUMBER() OVER (
                PARTITION BY Caminho
                ORDER BY F.Descricao) As VARCHAR(MAX))
    FROM Res As P
    INNER JOIN PlanoContas As F ON P.ID = F.IDSup)

SELECT
    ID, Descricao, Caminho, CaminhoMaterializado As CM
FROM Res
ORDER BY CaminhoMaterializado

ID Descricao Caminho CM
01 Ativo 0001. 1
02 Ativo -> Ativo Circulante 0001.0002. 1.1
09 Ativo -> Ativo Circulante -> Créditos 0001.0002.0009. 1.1.1
10 Ativo -> Ativo Circulante -> Créditos -> Duplicatas 0001.0002.0009.0010. 1.1.1.1
12 Ativo -> Ativo Circulante -> Créditos -> Duplicatas -> Duplicatas a Receber 0001.0002.0009.0010.0012. 1.1.1.1.1
11 Ativo -> Ativo Circulante -> Créditos -> Duplicatas -> Duplicatas Descontadas 0001.0002.0009.0010.0011. 1.1.1.1.2
03 Ativo -> Ativo Circulante -> Disponibilidades 0001.0002.0003. 1.1.2
04 Ativo -> Ativo Circulante -> Disponibilidades -> Caixa 0001.0002.0003.0004. 1.1.2.1
05 Ativo -> Ativo Circulante -> Disponibilidades -> Caixa -> Caixa Geral 0001.0002.0003.0004.0005. 1.1.2.1.1
06 Ativo -> Ativo Circulante -> Disponibilidades -> Contas Bancárias 0001.0002.0003.0006. 1.1.2.2
07 Ativo -> Ativo Circulante -> Disponibilidades -> Contas Bancárias -> Conta Corrente 0001.0002.0003.0006.0007. 1.1.2.2.1
13 Ativo -> Ativo Circulante -> Disponibilidades -> Contas Bancárias -> Conta Investimento 0001.0002.0003.0006.0013. 1.1.2.2.2
08 Ativo -> Ativo Circulante -> Disponibilidades -> Contas Bancárias -> Conta Poupança 0001.0002.0003.0006.0008. 1.1.2.2.3

Bem pessoal, acho que por hoje é só. Amanhã terei um longo dia pela frente.

[ ]s,

Gustavo

Publicidade
Esse post foi publicado em DICAS. Bookmark o link permanente.

Deixe um comentário

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

Gravatar
Logo 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 )

Cancelar

Conectando a %s