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
