segunda-feira, 2 de março de 2015
Vida de DBA
Isso é a pura realidade de quem não sabe trabalhar com Postgres :)
Apenas compartilhando ...
Resultado, sobra pro DBA resolver a bagunça. Vida de DBA é assim!
...
Estraído do site www.vidadeprogramador.com.br
http://vidadeprogramador.com.br/2015/02/28/drop-database/
quarta-feira, 9 de julho de 2014
Como Listar Estrutura de Campos e Tabelas via query em SQL Server
Geralmente quando queremos saber informações sobre colunas e tabelas, no SQL Server usamos a store procedure sp_help [nome_tabela], mas o objetivo dessa query é filtrar determinadas condições a exemplo de tamanho de identificadores superiores a 30 caracteres, o clássico erro ORA-00972: identifier is too long. do Oracle, para quem trabalha com sistemas Multi SGBDR ou migração de dados, essa query resolve essa e outras perguntas.
Segue:
SELECT TABLE_CATALOG AS BD
, TABLE_SCHEMA AS ESQUEMA_TABELA
, TABLE_NAME AS TABELA
, COLUMN_NAME AS COLUNA
, ORDINAL_POSITION AS ORD_POS
, DATA_TYPE AS TIPO
, CHARACTER_MAXIMUM_LENGTH AS TAM_CARACTER
, DATETIME_PRECISION AS TAM_DATA
, NUMERIC_PRECISION AS TAM_NUMERICO
, CASE WHEN CHARACTER_MAXIMUM_LENGTH IS NOT NULL THEN
CAST(CHARACTER_MAXIMUM_LENGTH AS NUMERIC(16,6))
WHEN DATETIME_PRECISION IS NOT NULL THEN
CAST(DATETIME_PRECISION AS NUMERIC(16,6))
ELSE
CAST(CAST(NUMERIC_PRECISION AS VARCHAR) + '.' + CAST( NUMERIC_SCALE AS VARCHAR) AS NUMERIC(16,6))
END AS TAM
, CASE WHEN IS_NULLABLE = 'YES' THEN
'SIM'
ELSE
'NÃO'
END AS [NULO?]
, COLLATION_NAME AS COLLATION
, LEN(COLUMN_NAME) AS TAM_NOME_COLUNA
, LEN(TABLE_NAME) AS TAM_NOME_TABELA
FROM INFORMATION_SCHEMA.COLUMNS
WHERE 1=1
--Testa condição de identificadores maior que 30 caracteres usado na migração para Oracle ou sistemas multi SGBDR
--O clássico erro ORA-00972: identifier is too long. do Oracle, para quem trabalha com sistemas Multi SGBDR ou migração dados.
--AND LEN(COLUMN_NAME) > 30 OR LEN( TABLE_NAME) > 30
--Testa por nome de tabela
--AND TABLE_NAME = 'NOME_TABELA'
--Testa por coluna (campo)
--AND COLUMN_NAME = 'NOME_COLUNA'
Mais uma vez, espero ter ajudado.
Seja feliz e fique na Paz de Jesus Cristo.
quinta-feira, 26 de dezembro de 2013
Código, Algoritmo de um Plano de Contas escrito em VB
Complementando um artigo descrito em http://emersonhermann.blogspot.com.br/2012/09/consulta-sql-de-plano-de-contas-query.html
Consulta SQL de Plano de Contas - Query Contabil - Query para Centro de Custo, apenas exponho um simples código para elucidar como deveria ser feito, isto é, como seria implementado um algoritmo para totalizar um plano de contas em uma linguagem de programação, a exemplo aqui do VBA, em casos que não é possível usar uma query ou um SGBD sem um suporte mais abrangente ao SQL a exemplo do ACCESS 2010.
Mais uma vez espero ter ajudado!
APdSJC!
'Um código bem básico para uma estrutura de três níveis escrito em 'VB para o Access 2010
Option Compare Database
Sub centro_custo()
' funciona para uma estrutura de contas em 3 niveis
Dim rs1 As Recordset
Dim rs2 As Recordset
Dim soma_n1, soma_n2, soma_n3 As Double
Dim tamanho_conta As Integer
Dim strSQL As String
' lista todas as contas cadastradas em centro_custo
Set rs1 = CurrentDb.OpenRecordset("SELECT id_centro_custo, descricao, tipo_conta FROM centro_custo ORDER BY id_centro_custo DESC;")
Do While Not rs1.EOF
' contas nivel 3
' todas contas analiticas, nessa estrutura as contas tem o nivel 3
If (rs1("tipo_conta") = "A") Then
' processa o nivel 3 na query em strSQL
strSQL = "SELECT cc.id_centro_custo, cc.descricao, sum(m.valor_movimento) AS total_conta FROM centro_custo cc INNER JOIN movimento m ON m.id_centro_custo LIKE cc.id_centro_custo WHERE cc.id_centro_custo = '" & rs1("id_centro_custo") & "' GROUP BY cc.id_centro_custo, cc.descricao;"
Set rs2 = CurrentDb.OpenRecordset(strSQL)
If Not rs2.EOF Then
soma_n3 = rs2("total_conta")
Else
soma_n3 = 0
End If
soma_n2 = soma_n2 + soma_n3
Debug.Print rs1("id_centro_custo") & " - " & rs1("descricao") & " - " & rs1("tipo_conta") & " - " & soma_n3
Else
tamanho_conta = Len(rs1("id_centro_custo"))
' contas nivel 2
If tamanho_conta = 3 Then
Debug.Print rs1("id_centro_custo") & " - " & rs1("descricao") & " - " & rs1("tipo_conta") & " - " & soma_n2
soma_n1 = soma_n1 + soma_n2
soma_n2 = 0
Else
' contas nivel 1
If tamanho_conta = 1 Then
Debug.Print rs1("id_centro_custo") & " - " & rs1("descricao") & " - " & rs1("tipo_conta") & " - " & soma_n1
soma_n1 = 0
End If
End If
End If
' avanca proximo registro
rs1.MoveNext
Loop
End Sub
' Informo ainda que os níveis podem ser configurados via matrizes
' e código desenvolvido acima é procedural
' Recomendo definir uma mascara do plano de contas
Resultado do Código em VBA Acima Descrito
sexta-feira, 29 de novembro de 2013
Desenvolvendo Querys Compativeis com Todos os SGBDRs com Ajuda do SQL Fiddle
Desenvolvendo Querys Compativeis com Todos os SGBDRs com Ajuda do SQL Fiddle
Sabe quando um amigo lhe pede uma ajuda sobre como desenvolver uma determinada query e você não sabe como explicar, essa é a dica para quem já passou por esse tipo de problema chama-se SQL Fiddle
Muitas vezes não é possível testar uma determinada query por não ter o SGBDR instalado ou está sem uma conexão segura ao SGBDR, então a dica é usar o SQL Fiddle.
Nesse site http://sqlfiddle.com/ ainda existe a possibilidade de verificar como funciona a mesma query nos diversos SGBDRs e suas diversas versões, uma feature interessante para quem desenvolve sistemas multi-SGBDRs.
Segue a dica para trabalhar com SQL Fiddle.
Acessar o SQL Fiddle no seguinte site: http://sqlfiddle.com/
Na primeira coluna coloca-se os comandos DDL (de criação de estrutura, tabelas, views, etc).
Executar com o botão Run Schema.
Na segunda coluna coloca-se os comandos DML (insert, update, delete, selects).
Executar com o botão Run SQL.
Outra observação é que o SQL Fiddle não armazena o cache dos scripts, então há de se rodar toda a query de uma única vez.
Mais uma vez espero ter ajudado.
APdSJC!
Sabe quando um amigo lhe pede uma ajuda sobre como desenvolver uma determinada query e você não sabe como explicar, essa é a dica para quem já passou por esse tipo de problema chama-se SQL Fiddle
Muitas vezes não é possível testar uma determinada query por não ter o SGBDR instalado ou está sem uma conexão segura ao SGBDR, então a dica é usar o SQL Fiddle.
Nesse site http://sqlfiddle.com/ ainda existe a possibilidade de verificar como funciona a mesma query nos diversos SGBDRs e suas diversas versões, uma feature interessante para quem desenvolve sistemas multi-SGBDRs.
Segue a dica para trabalhar com SQL Fiddle.
Acessar o SQL Fiddle no seguinte site: http://sqlfiddle.com/
Na primeira coluna coloca-se os comandos DDL (de criação de estrutura, tabelas, views, etc).
Executar com o botão Run Schema.
Na segunda coluna coloca-se os comandos DML (insert, update, delete, selects).
Executar com o botão Run SQL.
Outra observação é que o SQL Fiddle não armazena o cache dos scripts, então há de se rodar toda a query de uma única vez.
Mais uma vez espero ter ajudado.
APdSJC!
quinta-feira, 1 de agosto de 2013
Converter várias linhas em uma única linha no SQL Server
Como converter várias linhas em uma única linha no SQL Server, separando por ponto e virgula ";".
Mais uma vez espero ter ajudado ...
USE tempdb;
-- DROP TABLE pessoa_fisica;
CREATE TABLE pessoa_fisica
(
id_pessoa_fisica int IDENTITY (1,1) PRIMARY KEY
, nome varchar(100) NOT NULL
, sexo varchar(1) NOT NULL
);
-- DROP TABLE email;
CREATE TABLE email
(
id_email int IDENTITY (1,1) PRIMARY KEY
, email varchar(100) NOT NULL
, id_pessoa_fisica int NOT NULL REFERENCES pessoa_fisica(id_pessoa_fisica)
);
-- inserindo na tabela pessoa_fisica
INSERT INTO pessoa_fisica (nome, sexo) VALUES ('Maria','F');
INSERT INTO pessoa_fisica (nome, sexo) VALUES ('Ieda','F');
INSERT INTO pessoa_fisica (nome, sexo) VALUES ('Ilma','F');
INSERT INTO pessoa_fisica (nome, sexo) VALUES ('Felipe','F');
-- listando todas as pessoas fisicas cadastradas
SELECT * FROM pessoa_fisica;
-- inserindo na tabela email
-- inserindo emails de Maria
INSERT INTO email (email, id_pessoa_fisica) VALUES ('maria@exemplo.com', 1);
INSERT INTO email (email, id_pessoa_fisica) VALUES ('maria.rosa@exemplo.com', 1);
INSERT INTO email (email, id_pessoa_fisica) VALUES ('maria.silva@exemplo.com', 1);
-- inserindo emails de Ieda
INSERT INTO email (email, id_pessoa_fisica) VALUES ('ieda@exemplo.com', 2);
INSERT INTO email (email, id_pessoa_fisica) VALUES ('ieda.lima@exemplo.com', 2);
-- inserindo emails de Felipe
INSERT INTO email (email, id_pessoa_fisica) VALUES ('felipe@exemplo.com', 4);
INSERT INTO email (email, id_pessoa_fisica) VALUES ('felipe.santos@exemplo.com', 4);
-- listando todos os emails cadastrados
SELECT * FROM email;
-- listando emails de Maria
-- listando emails de Maria em várias linhas
-- exemplo 1.1
SELECT email + '; ' FROM email WHERE id_pessoa_fisica = 1;
-- listando emails de Maria em uma única linha
-- exemplo 1.2
SELECT stuff( (SELECT email + '; ' FROM email WHERE id_pessoa_fisica = 1 FOR XML PATH('')),1,0,'');
-- listando todos os emails cadastrados com todas as pessoas cadastradas,
-- considerando vários emails em uma unica linha.
-- exemplo 2
SELECT pessoa_fisica.nome
, stuff (
(
SELECT email.email + '; '
FROM email
WHERE email.id_pessoa_fisica = pessoa_fisica.id_pessoa_fisica
FOR XML PATH('')
),1,0,''
) AS emails
FROM pessoa_fisica
;
quarta-feira, 24 de julho de 2013
Remover caracteres especiais em campos do Postgres
--
-- Nome Artefato/Programa..: usf_rm_special_chars.sql
-- Autor(es)...............: Emerson Hermann (emersonhermann at gmail.com)
-- ........................: O Peregrino / http://emersonhermann.blogspot.com
-- Data Inicio ............: 09/10/2008
-- Data Atual..............: 24/07/2012
-- Versao..................: 0.02
-- Compilador/Interpretador: PostgreSql
-- Sistemas Operacionais...: Linux/Windows
-- SGBD....................: PostgreSql 8.x/9.x
-- Kernel..................: Nao informado!
-- Finalidade..............: store function para remocao de caracteres especiais do ASCII (33) caracteres nao imprimiveis
-- OBS.....................:
--
--habilitando linguagem plpgsql
--CREATE TRUSTED PROCEDURAL LANGUAGE plpgsql HANDLER plpgsql_call_handler;
-- languages
--SELECT * FROM pg_language;
-- Apaga function se existir
DROP FUNCTION IF EXISTS usf_rm_special_chars (TEXT);
CREATE OR REPLACE FUNCTION usf_rm_special_chars (stexto TEXT) RETURNS TEXT AS
$$
DECLARE
stextonovo TEXT;
BEGIN
-- Tabela ASCII
-- Caracteres não imprimiveis, 33 ao total, menos o NUL ficam 32
-- hex d abr ctl descricao
-- 00 0 NUL ^@ Null - Nulo desativado, pois o nulo no postgres funciona em forma de cascata
stextonovo := translate(stexto, chr(1),''); -- 01 1 SOH ^A Start of Header - Início do cabeçalho
stextonovo := translate(stextonovo, chr(2),''); -- 02 2 STX ^B Start of Text - Início do texto
stextonovo := translate(stextonovo, chr(3),''); -- 03 3 ETX ^C End of Text - Fim do texto
stextonovo := translate(stextonovo, chr(4),''); -- 04 4 EOT ^D End of Tape - Fim de fita
stextonovo := translate(stextonovo, chr(5),''); -- 05 5 ENQ ^E Enquire - Interroga identidade do terminal
stextonovo := translate(stextonovo, chr(6),''); -- 06 6 ACK ^F Acknowledge - Reconhecimento
stextonovo := translate(stextonovo, chr(7),''); -- 07 7 BEL ^G Bell - Campainha
stextonovo := translate(stextonovo, chr(8),''); -- 08 8 BS ^H Back-space - Espaço atrás
stextonovo := translate(stextonovo, chr(9),''); -- 09 9 HT ^I Horizontal Tabulation - Tabulação horizontal
stextonovo := translate(stextonovo, chr(10),''); -- 0A 10 LF ^J Line-Feed - Alimenta linha
stextonovo := translate(stextonovo, chr(11),''); -- 0B 11 VT ^K Vertical Tabulation - Tabulação vertical
stextonovo := translate(stextonovo, chr(12),''); -- 0C 12 FF ^L Form-Feed - Alimenta formulário
stextonovo := translate(stextonovo, chr(13),''); -- 0D 13 CR ^M Carriage-Return - Retorno do carro (enter)
stextonovo := translate(stextonovo, chr(14),''); -- 0E 14 SO ^N Shift-Out - Saída do shift (passa a usar caracteres de baixo da tecla - minúsculas, etc.)
stextonovo := translate(stextonovo, chr(15),''); -- 0F 15 SI ^O Shift-In-Ent. no shift (passa a usar carac. de cima da tecla: maiúsculas, carac. especiais, etc.)
stextonovo := translate(stextonovo, chr(16),''); -- 10 16 DLE ^P Data-Link Escape
stextonovo := translate(stextonovo, chr(17),''); -- 11 17 DC1 ^Q Device-Control 1
stextonovo := translate(stextonovo, chr(18),''); -- 12 18 DC2 ^R Device-Control 2
stextonovo := translate(stextonovo, chr(19),''); -- 13 19 DC3 ^S Device-Control 3
stextonovo := translate(stextonovo, chr(20),''); -- 14 20 DC4 ^T Device-Control 4
stextonovo := translate(stextonovo, chr(21),''); -- 15 21 NAK ^U Neg-Acknowledge - Não-reconhecimento
stextonovo := translate(stextonovo, chr(22),''); -- 16 22 SYN ^V Synchronous Idle
stextonovo := translate(stextonovo, chr(23),''); -- 17 23 vETB^W End-of-Transmission Block
stextonovo := translate(stextonovo, chr(24),''); -- 18 24 CAN ^X Cancel
stextonovo := translate(stextonovo, chr(25),''); -- 19 25 EM ^Y End-Of-Medium
stextonovo := translate(stextonovo, chr(26),''); -- 1A 26 SUB ^Z Substitute
stextonovo := translate(stextonovo, chr(27),''); -- 1B 27 ESC ^[ Escape
stextonovo := translate(stextonovo, chr(28),''); -- 1C 28 FS ^\ File Separator
stextonovo := translate(stextonovo, chr(29),''); -- 1D 29 GS ^] Group Separator
stextonovo := translate(stextonovo, chr(30),''); -- 1E 30 RS ^^ Record Separator
stextonovo := translate(stextonovo, chr(31),''); -- 1F 31 US ^_ Unit Separator
stextonovo := translate(stextonovo, chr(127),''); -- 7F127 DEL ^? Delete
RETURN stextonovo;
END;
$$
LANGUAGE 'plpgsql' STABLE CALLED ON NULL INPUT SECURITY INVOKER;
Test 1
-- string com caracteres especiais, formato 2
SELECT 'SEM STRESS... ' || chr(1) || 'TECLA ENTER: ' || chr(13) || ' TECLA ESC: ' || chr(27) || ' TESTE OK ' AS test3;
-- string sem caracteres espciais usando a function, formato 2
SELECT usf_rm_special_chars('SEM STRESS... ' || chr(1) || 'TECLA ENTER : ' || chr(13) || 'TECLA ESC: ' || chr(27) || ' TESTE OK ') AS test4;
-- verificando tamanho da string, 53 caracteres, incluindo 3 especiais
SELECT length('SEM STRESS... ' || chr(1) || 'TECLA ENTER : ' || chr(13) || 'TECLA ESC: ' || chr(27) || ' TESTE OK ') AS test5;
-- verificando tamanho da string, 50 caracteres, removido 3 especiais
SELECT length(usf_rm_special_chars('SEM STRESS... ' || chr(1) || 'TECLA ENTER : ' || chr(13) || 'TECLA ESC: ' || chr(27) || ' TESTE OK ')) AS test6;
terça-feira, 29 de janeiro de 2013
Desenvolvendo querys SQL para Razão e Balancete Contábil.
Desenvolvendo querys SQL para Razão e Balancete Contábil.
Em um artigo escrito neste blog no dia 06-09-2012 Consulta SQL de Plano de Contas - Query Contabil - Query para Centro de Custo conforme link http://www.emersonhermann.blogspot.com.br/2012/09/consulta-sql-de-plano-de-contas-query.html, mostrei como desenvolver uma query em uma estrutura de plano de contas ou centro de custo, dessa vez irei apresentar de forma prática, gradual e por exemplos de como desenvolver uma query para relatório de Razão, Razão Sumarizado e Balancete Contábil.
Nivel de Complexidade: Intermediário, Avançado
Quem desenvolve sabe como é complicado criar relatórios para contabilidade ou centro de custos, seja codificando em alguma linguagem ou mesmo tentando encurtar o tempo ou a pressão, usando algum gerador de relatórios, a exemplo do MS Report Service, SAP Crystal Reports, Script Case, etc.
As querys scripts mostradas nesse artigo foram desenvolvidas para os seguintes SGBDRs em ordem alfabética:
Firebird 2.5.1
Oracle 11g R2
Postgres 9.1
SQL Server 2012
A modelagem aqui apresentada não seguiu o rigor acadêmico, a exemplo de implementação de constraints, chaves primárias, etc, bem como os exemplos citados para area Contabil.
O objetivo principal deste artigo é mostrar como desenvolver querys para Sistemas Contabeis, houve uma simplificação com intuíto de facilitar o entendimento, entretanto, com os exemplos expostos é possivel adotar em qualquer modelo relacional.
Houve também uma preocupação em apresentar os scripts desenvolvidos nos SGBDRs citados no inicio deste documento.
Pode-se chegar aos mesmos resultados de uma forma mais performática e simples; estou apto a sugestões.
Seguem os exemplos em scripts SQL:
Criando as tabelas ... (Script para Firebird 2.5.1, Oracle 11g R2, Postgres 9.1, SQL Server 2012)
Para facilitar, caso haja interesse, recomendo criar views ou functions ou procedures das querys acimas, pois são demasiadamente extensas.
Mais uma vez espero ter ajudado.
APDSJ!
-- USE tempdb; -- Descomentar caso use SQL Server, recomendo criar um banco de teste para os outros SGBDRs. --DROP TABLE plano_conta; CREATE TABLE plano_conta ( id_plano_conta varchar(12) PRIMARY KEY -- dados da conta ex. 1.02.01 , descricao varchar(50) -- descricao da conta cadastrada ex. Vendas Externas , tipo_conta varchar(1) -- tipo de conta do cc Analitica ou Sintética, dominio discreto: A ou S, em situação de produção merece uma constraint check ); --DROP TABLE lancamento; CREATE TABLE lancamento ( id_lancamento integer PRIMARY KEY -- id do lancamento, recomenda-se auto incremento, mas para simplifcar fica sem auto incremento , dt_lancamento date -- data do lancamento , numero_doc varchar(40) -- numero do documento a ser informado , id_plano_conta varchar(12) -- chave estrangeira para a tabela plano_conta, mas para simplificar apenas iremos convencionar, não será habilitado a FK, recomendo colocar not null , tipo_lancamento varchar(1) -- tipo de lancamento 'E' = Entrada 'S' = Saida , historico varchar(100) -- historico do lancamento , valor_lancamento numeric(15,2) -- valor informado );Povoando os tabelas...
-- Receitas
INSERT INTO plano_conta (id_plano_conta, descricao, tipo_conta) VALUES ('1','Receita','S');
INSERT INTO plano_conta (id_plano_conta, descricao, tipo_conta) VALUES ('1.1','Vendas Internas','S');
INSERT INTO plano_conta (id_plano_conta, descricao, tipo_conta) VALUES ('1.1.1','Escola','A');
INSERT INTO plano_conta (id_plano_conta, descricao, tipo_conta) VALUES ('1.1.2','Escritório','A');
INSERT INTO plano_conta (id_plano_conta, descricao, tipo_conta) VALUES ('1.2','Vendas Externas','S');
INSERT INTO plano_conta (id_plano_conta, descricao, tipo_conta) VALUES ('1.2.1','Livro','A');
INSERT INTO plano_conta (id_plano_conta, descricao, tipo_conta) VALUES ('1.2.2','Brinquedos','A');
-- Despesas
INSERT INTO plano_conta (id_plano_conta, descricao, tipo_conta) VALUES ('2','Despesas','S');
INSERT INTO plano_conta (id_plano_conta, descricao, tipo_conta) VALUES ('2.1','Fornecedores','S');
INSERT INTO plano_conta (id_plano_conta, descricao, tipo_conta) VALUES ('2.1.1','Nacional','A');
INSERT INTO plano_conta (id_plano_conta, descricao, tipo_conta) VALUES ('2.1.2','Importado','A');
INSERT INTO plano_conta (id_plano_conta, descricao, tipo_conta) VALUES ('2.2','Escritório','S');
INSERT INTO plano_conta (id_plano_conta, descricao, tipo_conta) VALUES ('2.2.1','Materiais de limpeza','A');
INSERT INTO plano_conta (id_plano_conta, descricao, tipo_conta) VALUES ('2.2.2','Materiais de Escritório','A');
-- Vamos povoar a tabela lancamento:
INSERT INTO lancamento (id_lancamento, dt_lancamento, numero_doc, id_plano_conta, tipo_lancamento, historico, valor_lancamento) VALUES (1,'2012-07-03','0000084','1.2.2','E',NULL,10.55);
INSERT INTO lancamento (id_lancamento, dt_lancamento, numero_doc, id_plano_conta, tipo_lancamento, historico, valor_lancamento) VALUES (2,'2012-07-03','0000084','1.2.2','S',NULL,2.50);
INSERT INTO lancamento (id_lancamento, dt_lancamento, numero_doc, id_plano_conta, tipo_lancamento, historico, valor_lancamento) VALUES (3,'2012-07-01','0000021','1.1.2','E',NULL,50.00);
INSERT INTO lancamento (id_lancamento, dt_lancamento,numero_doc, id_plano_conta, tipo_lancamento, historico, valor_lancamento) VALUES (4,'2012-07-01','0000042','1.2.2','E',NULL,100.00);
INSERT INTO lancamento (id_lancamento, dt_lancamento, numero_doc, id_plano_conta, tipo_lancamento, historico, valor_lancamento) VALUES (5,'2012-07-04','0000084','1.2.2','E',NULL,160.00);
INSERT INTO lancamento (id_lancamento, dt_lancamento, numero_doc, id_plano_conta, tipo_lancamento, historico, valor_lancamento) VALUES (6,'2012-07-04','0000084','1.2.2','S',NULL,80.00);
INSERT INTO lancamento (id_lancamento, dt_lancamento, numero_doc, id_plano_conta, tipo_lancamento, historico, valor_lancamento) VALUES (7,'2012-07-04','0000142','2.2.1','S',NULL,40.00);
INSERT INTO lancamento (id_lancamento, dt_lancamento, numero_doc, id_plano_conta, tipo_lancamento, historico, valor_lancamento) VALUES (8,'2012-07-07','0000210','2.2.2','S',NULL,80.00);
INSERT INTO lancamento (id_lancamento, dt_lancamento, numero_doc, id_plano_conta, tipo_lancamento, historico, valor_lancamento) VALUES (9,'2012-07-13','0000242','2.2.2','S',NULL,20.00);
INSERT INTO lancamento (id_lancamento, dt_lancamento, numero_doc, id_plano_conta, tipo_lancamento, historico, valor_lancamento) VALUES (10,'2012-07-13','0000284','2.2.1','S',NULL,15.00);
Vamos as querys relatórios...
Razão Detalhado (Script para Firebird 2.5.1, Oracle 11g R2, Postgres 9.1, SQL Server 2012)
-- razao detalhado (Script para Firebird 2.5.1, Oracle 11g R2, Postgres 9.1, SQL Server 2012)
SELECT lan.id_lancamento
, lan.dt_lancamento
, lan.numero_doc
, lan.id_plano_conta
, lan.tipo_lancamento
, lan.historico
,
coalesce(
(
SELECT sum
(
CASE WHEN tipo_lancamento = 'E' THEN
valor_lancamento
WHEN tipo_lancamento = 'S' THEN
valor_lancamento * -1
ELSE
0.00
END
)
FROM lancamento
WHERE dt_lancamento < lan.dt_lancamento
AND id_plano_conta = lan.id_plano_conta
),0) AS saldo_inicial
, CASE WHEN tipo_lancamento = 'E' THEN
valor_lancamento
ELSE
0.00
END AS entrada
, CASE WHEN tipo_lancamento = 'S' THEN
valor_lancamento
ELSE
0.00
END AS saida
,
coalesce(
(
SELECT sum
(
CASE WHEN tipo_lancamento = 'E' THEN
valor_lancamento
WHEN tipo_lancamento = 'S' THEN
valor_lancamento * -1
ELSE
0.00
END
)
FROM lancamento
WHERE dt_lancamento <= lan.dt_lancamento
AND id_plano_conta = lan.id_plano_conta
),0) AS saldo_final
FROM lancamento AS lan
JOIN plano_conta AS plc
ON plc.id_plano_conta = lan.id_plano_conta
WHERE lan.dt_lancamento >= '2012-07-04'
AND lan.dt_lancamento <= '2012-07-14'
AND lan.id_plano_conta = '2.2.2'
ORDER BY lan.dt_lancamento ASC
;
Razão Sumarizado Por Plano de Conta (Script para Firebird 2.5.1, Oracle 11g R2, Postgres 9.1, SQL Server 2012)
-- razao sumarizado por plano de conta (Script para Firebird 2.5.1, Oracle 11g R2, Postgres 9.1, SQL Server 2012)
SELECT x.id_plano_conta
, pcx.descricao
, coalesce(
(
SELECT sum
(
CASE WHEN tipo_lancamento = 'E' THEN
valor_lancamento
WHEN tipo_lancamento = 'S' THEN
valor_lancamento * -1
ELSE
0.00
END
)
FROM lancamento
WHERE dt_lancamento < '2012-07-04'
AND id_plano_conta = x.id_plano_conta
),0) AS saldo_inicial
, sum(x.entrada) AS entrada
, sum(x.saida) AS saida
, coalesce(
(
SELECT sum
(
CASE WHEN tipo_lancamento = 'E' THEN
valor_lancamento
WHEN tipo_lancamento = 'S' THEN
valor_lancamento * -1
ELSE
0.00
END
)
FROM lancamento
WHERE dt_lancamento <= '2012-07-14'
AND id_plano_conta = x.id_plano_conta
),0) AS saldo_final
FROM
(
SELECT lan.id_plano_conta
, CASE WHEN tipo_lancamento = 'E' THEN
valor_lancamento
ELSE
0.00
END AS entrada
, CASE WHEN tipo_lancamento = 'S' THEN
valor_lancamento
ELSE
0.00
END AS saida
FROM lancamento AS lan
JOIN plano_conta AS plc
ON plc.id_plano_conta = lan.id_plano_conta
WHERE lan.dt_lancamento >= '2012-07-04'
AND lan.dt_lancamento <= '2012-07-14'
AND lan.id_plano_conta = '2.2.2'
) AS x
JOIN plano_conta AS pcx
ON pcx.id_plano_conta = x.id_plano_conta
GROUP BY x.id_plano_conta
, pcx.descricao
ORDER BY x.id_plano_conta ASC
;
Balancete Contabil (Script para SQL Server 2012)
-- balancete contabil (Script para SQL Server 2012)
SELECT pcx.id_plano_conta
, pcx.descricao
, sum(xx.saldo_inicial) AS saldo_inicial
, sum(xx.entrada) AS entrada
, sum(xx.saida) AS saida
, sum(xx.saldo_final) AS saldo_final
FROM
(
SELECT x.id_plano_conta
, coalesce(
(
SELECT sum
(
CASE WHEN tipo_lancamento = 'E' THEN
valor_lancamento
WHEN tipo_lancamento = 'S' THEN
valor_lancamento * -1
ELSE
0.00
END
)
FROM lancamento
WHERE dt_lancamento < '2012-07-04'
AND id_plano_conta = x.id_plano_conta
),0) AS saldo_inicial
, coalesce(
(
SELECT sum
(
CASE WHEN tipo_lancamento = 'E' THEN
valor_lancamento
ELSE
0.00
END
)
FROM lancamento
WHERE dt_lancamento >= '2012-07-04'
AND dt_lancamento <= '2012-07-14'
AND id_plano_conta = x.id_plano_conta
),0) AS entrada
, coalesce(
(
SELECT sum
(
CASE WHEN tipo_lancamento = 'S' THEN
valor_lancamento
ELSE
0.00
END
)
FROM lancamento
WHERE dt_lancamento >= '2012-07-04'
AND dt_lancamento <= '2012-07-14'
AND id_plano_conta = x.id_plano_conta
),0) AS saida
, coalesce(
(
SELECT sum
(
CASE WHEN tipo_lancamento = 'E' THEN
valor_lancamento
WHEN tipo_lancamento = 'S' THEN
valor_lancamento * -1
ELSE
0.00
END
)
FROM lancamento
WHERE dt_lancamento <= '2012-07-14'
AND id_plano_conta = x.id_plano_conta
),0) AS saldo_final
FROM
(
SELECT pla.id_plano_conta
, lan.dt_lancamento
, CASE WHEN lan.tipo_lancamento = 'E' THEN
lan.valor_lancamento
ELSE
0.00
END AS entrada
, CASE WHEN lan.tipo_lancamento = 'S' THEN
lan.valor_lancamento
ELSE
0.00
END AS saida
FROM lancamento AS lan
JOIN plano_conta AS pla
ON pla.id_plano_conta = lan.id_plano_conta
WHERE 1=1
) AS x
GROUP BY x.id_plano_conta
) AS xx
JOIN plano_conta pcx
ON xx.id_plano_conta LIKE pcx.id_plano_conta + '%'
GROUP BY pcx.id_plano_conta
, pcx.descricao
ORDER BY pcx.id_plano_conta ASC
;
Balancete Contabil (Script para Firebird 2.5.1, Oracle 11g R2, Postgres 9.1)
-- balancete contabil (Script para Firebird 2.5.1, Oracle 11g R2, Postgres 9.1)
SELECT pcx.id_plano_conta
, pcx.descricao
, sum(xx.saldo_inicial) AS saldo_inicial
, sum(xx.entrada) AS entrada
, sum(xx.saida) AS saida
, sum(xx.saldo_final) AS saldo_final
FROM
(
SELECT x.id_plano_conta
, coalesce(
(
SELECT sum
(
CASE WHEN tipo_lancamento = 'E' THEN
valor_lancamento
WHEN tipo_lancamento = 'S' THEN
valor_lancamento * -1
ELSE
0.00
END
)
FROM lancamento
WHERE dt_lancamento < '2012-07-04'
AND id_plano_conta = x.id_plano_conta
),0) AS saldo_inicial
, coalesce(
(
SELECT sum
(
CASE WHEN tipo_lancamento = 'E' THEN
valor_lancamento
ELSE
0.00
END
)
FROM lancamento
WHERE dt_lancamento >= '2012-07-04'
AND dt_lancamento <= '2012-07-14'
AND id_plano_conta = x.id_plano_conta
),0) AS entrada
, coalesce(
(
SELECT sum
(
CASE WHEN tipo_lancamento = 'S' THEN
valor_lancamento
ELSE
0.00
END
)
FROM lancamento
WHERE dt_lancamento >= '2012-07-04'
AND dt_lancamento <= '2012-07-14'
AND id_plano_conta = x.id_plano_conta
),0) AS saida
, coalesce(
(
SELECT sum
(
CASE WHEN tipo_lancamento = 'E' THEN
valor_lancamento
WHEN tipo_lancamento = 'S' THEN
valor_lancamento * -1
ELSE
0.00
END
)
FROM lancamento
WHERE dt_lancamento <= '2012-07-14'
AND id_plano_conta = x.id_plano_conta
),0) AS saldo_final
FROM
(
SELECT pla.id_plano_conta
, lan.dt_lancamento
, CASE WHEN lan.tipo_lancamento = 'E' THEN
lan.valor_lancamento
ELSE
0.00
END AS entrada
, CASE WHEN lan.tipo_lancamento = 'S' THEN
lan.valor_lancamento
ELSE
0.00
END AS saida
FROM lancamento AS lan
JOIN plano_conta AS pla
ON pla.id_plano_conta = lan.id_plano_conta
WHERE 1=1
) AS x
GROUP BY x.id_plano_conta
) AS xx
JOIN plano_conta pcx
ON xx.id_plano_conta LIKE pcx.id_plano_conta || '%'
GROUP BY pcx.id_plano_conta
, pcx.descricao
ORDER BY pcx.id_plano_conta ASC
;
Figura 01 - Resultado das Querys
Para facilitar, caso haja interesse, recomendo criar views ou functions ou procedures das querys acimas, pois são demasiadamente extensas.
Mais uma vez espero ter ajudado.
APDSJ!
quarta-feira, 19 de setembro de 2012
Formatar CNPJ em SQL
Store function para formatar CNPJ em SQL Server
IF EXISTS (
SELECT *
FROM sys.objects
WHERE object_id = OBJECT_ID(N'[dbo].[usf_formata_cnpj]')
AND type IN (N'FN')
)
DROP FUNCTION dbo.usf_formata_cnpj;
GO
CREATE FUNCTION dbo.usf_formata_cnpj(@cnpj varchar(max), @mascara bit) RETURNS varchar(max)
AS
-- Nome Artefato/Programa..: usf_formata_cnpj.sql
-- Autor(es)...............: Emerson Hermann (emersonhermann [at] gmail.com) O Peregrino (http://www.emersonhermann.blogspot.com)
-- Data Inicio ............: 19/09/2012
-- Data Atualizacao........: 19/09/2012
-- Versao..................: 0.01
-- Compilador/Interpretador: T-SQL (Transact SQL)
-- Sistemas Operacionais...: Windows
-- SGBD....................: MS SQL Server 2005/2008
-- Kernel..................: Nao informado!
-- Finalidade..............: Store Procedure (Function) para mascara o numero do CNPJ
-- OBS.....................: A entrada é um varchar e o retorno é um varchar formatado do CNPJ, parametro mascara setado em 0 apenas string com zeros, 1 formata o CNPJ de fato
-- ........................:
--
BEGIN
DECLARE @cnpj_temp varchar(max)
-- Pre-validacao 1, se e nulo, entao retorna nulo
IF @cnpj IS NULL BEGIN
RETURN (@cnpj)
END --fim_se
-- Pre-validacao 2, se e maior que 14 digitos , entao retorna 0
IF LEN(@cnpj) > 14 BEGIN
RETURN (@cnpj)
END --fim_se
-- Pre-validacao 3, se e tem alguma letra no CNPJ, entao retorna 0
IF (SELECT CASE WHEN patindex('%[^0-9]%', @cnpj) > 0 THEN 1 ELSE 0 END) = 1 BEGIN
RETURN (@cnpj)
END --fim_se
-- Pre-validacao 4, se e menor que 14 dig, pode ser oriundo de um bigint, entao colocar zeros a frente
SET @cnpj_temp = @cnpj
IF LEN(@cnpj) < 14 BEGIN
SET @cnpj_temp = REPLICATE('0',14-LEN(@cnpj))+@cnpj
END --fim_se
-- Se e para formatar mesmo
IF @mascara = 1 BEGIN
SET @cnpj_temp = SUBSTRING(@cnpj_temp,1,2) + '.' + SUBSTRING(@cnpj_temp,3,3) + '.' + SUBSTRING(@cnpj_temp,6,3) + '/' + SUBSTRING(@cnpj_temp,9,4) + '-' + SUBSTRING(@cnpj_temp,13,2)
END --fim_se
RETURN (@cnpj_temp)
END;
GO
-- chamada da function
-- test 1
SELECT dbo.usf_formata_cnpj('00000000000191',1); -- Banco do Brasil Sede - 00.000.000/0001-91
SELECT dbo.usf_formata_cnpj('00000000000191',0); -- Banco do Brasil Sede - 00000000000191
-- test 2
SELECT dbo.usf_formata_cnpj('191',1); -- Banco do Brasil Sede - 00.000.000/0001-91
SELECT dbo.usf_formata_cnpj('191',0); -- Banco do Brasil Sede - 00000000000191
Validar CNPJ em SQL
Como validar CNPJ em SQL Server, store function para validar CNPJ em T-SQL (Transact-SQL)
Esta store function, foi desenvolvida pensando no armazenamento do CNPJ em BIGINT, mas pode ser usada facilmente sem adaptações com CNPJ tipo VARCHAR(14) sem mascaras ou formatações.
Recomendável persistência do CNPJ em BIGINT por motivo principal de desempenho.
Segue function....
Esta store function, foi desenvolvida pensando no armazenamento do CNPJ em BIGINT, mas pode ser usada facilmente sem adaptações com CNPJ tipo VARCHAR(14) sem mascaras ou formatações.
Recomendável persistência do CNPJ em BIGINT por motivo principal de desempenho.
Segue function....
IF EXISTS (
SELECT *
FROM sys.objects
WHERE object_id = OBJECT_ID(N'[dbo].[usf_valida_cnpj]')
AND type IN (N'FN')
)
DROP FUNCTION dbo.usf_valida_cnpj;
GO
CREATE FUNCTION dbo.usf_valida_cnpj(@cnpjx varchar(14)) RETURNS bit
AS
-- Nome Artefato/Programa..: usf_valida_cnpj.sql
-- Autor(es)...............: Emerson Hermann (emersonhermann [at] gmail.com) O Peregrino (http://www.emersonhermann.blogspot.com) baseado em Script feito por:
-- ........................: Baseado em código de Fernando Jacinto da Silva em http://www.devmedia.com.br
-- Data Inicio ............: 18/09/2011
-- Data Atualizacao........: 19/09/2012
-- Versao..................: 0.02
-- Compilador/Interpretador: T-SQL (Transact SQL)
-- Sistemas Operacionais...: Windows
-- SGBD....................: MS SQL Server 2005/2008
-- Kernel..................: Nao informado!
-- Finalidade..............: Store Procedure (Function) para validar o numero do CNPJ
-- OBS.....................: A entrada é um varchar e o retorno é um bit, 1 para válido, 0 para inválido e null para nulos
-- ........................:
--
BEGIN
DECLARE
@cnpj varchar(14)
, @indice int
, @soma int
, @dig1 int
, @dig2 int
, @var1 int
, @var2 int
, @resultado bit
SET @cnpj = @cnpjx
SET @soma = 0
SET @indice = 1
SET @resultado = 0
-- pre-validacao 1, se e nulo, entao retorna nulo
IF @cnpj IS NULL BEGIN
SET @resultado = NULL
RETURN (@resultado)
END --fim_se
-- pre-validacao 2, se e maior que 11 digitos , entao retorna 0
IF LEN(@cnpj) > 14 BEGIN
SET @resultado = 0
RETURN (@resultado)
END --fim_se
-- pre-validacao 3, se e tem alguma letra no cpf, entao retorna 0
IF (SELECT CASE WHEN patindex('%[^0-9]%', @cnpj) > 0 THEN 1 ELSE 0 END) = 1 BEGIN
SET @resultado = 0
RETURN (@resultado)
END --fim_se
-- pre-validacao 4, se e menor que 11 digitos , pode ser oriundo de bigint, então fazer tratamento de zeros
IF LEN(@cnpj) < 14 BEGIN
SET @cnpj = REPLICATE('0',14-LEN(@cnpj))+@cnpj
END --fim se
/* algorítimo para o primeiro dígito 543298765432 */
/* cálculo do 1º dígito */
/* cálculo da 1ª parte do algorítiom 5432 */
SET @var1 = 5 /* 1a parte do algorítimo começando de "5" */
WHILE (@indice <= 4) BEGIN
SET @soma = @soma + CONVERT(int,SUBSTRING(@cnpj,@indice,1)) * @var1
SET @indice = @indice + 1 /* navegando um-a-um até < = 4, as quatro primeira posições */
SET @var1 = @var1 - 1 /* subtraindo o algorítimo de 5 até 2 */
END
/* cálculo da 2ª parte do algorítiom 98765432 */
SET @var2 = 9
WHILE (@indice <= 12) BEGIN
SET @soma = @soma + CONVERT(int,SUBSTRING(@cnpj,@indice,1)) * @var2
SET @indice = @indice + 1
SET @var2 = @var2 - 1
END
SET @dig1 = (@soma % 11)
/* se o resto da divisão for < 2, o digito = 0 */
IF @dig1 < 2 BEGIN
SET @dig1 = 0
END ELSE BEGIN /* se o resto da divisão não for < 2*/
SET @dig1 = 11 - (@soma % 11)
END
/* cálculo do 2º dígito */
/* zerando o indice e a soma para começar a calcular o 2º dígito*/
SET @indice = 1
SET @soma = 0
/* cálculo da 1ª parte do algorítiom 65432 */
SET @var1 = 6 /* 2a parte do algorítimo começando de "6" */
SET @resultado = 0
WHILE (@indice <= 5) BEGIN
SET @soma = @soma + CONVERT(int,SUBSTRING(@cnpj,@indice,1)) * @var1
SET @indice = @indice + 1 /* navegando um-a-um até < = 5, as quatro primeira posições */
SET @var1 = @var1 - 1 /* subtraindo o algorítimo de 6 até 2 */
END
/* cálculo da 2ª parte do algorítiom 98765432 */
SET @var2 = 9
WHILE (@indice <= 13) BEGIN
SET @soma = @soma + CONVERT(int,SUBSTRING(@cnpj,@indice,1)) * @var2
SET @indice = @indice + 1
SET @var2 = @var2 - 1
END
SET @dig2 = (@soma % 11)
/* se o resto da divisão for < 2, o digito = 0 */
IF @dig2 < 2 BEGIN
SET @dig2 = 0
END ELSE BEGIN /* se o resto da divisão não for < 2*/
SET @dig2 = 11 - (@soma % 11)
END
-- validando
IF (@dig1 = SUBSTRING(@cnpj,LEN(@cnpj)-1,1)) AND (@dig2 = SUBSTRING(@cnpj,LEN(@cnpj),1)) BEGIN
SET @resultado = 1
END ELSE BEGIN
SET @resultado = 0
END
RETURN (@resultado)
END;
GO
-- chamada da function
-- test 1
SELECT dbo.usf_valida_cnpj('00000000000191'); -- Banco do Brasil Sede - 1 (válido)
-- test 2
SELECT dbo.usf_valida_cnpj('191'); -- Banco do Brasil Sede - 1 (válido)
-- test 3
SELECT dbo.usf_valida_cnpj('00000000000192'); -- Banco do Brasil Sede - 0 (inválido)
Cache de memória do SQL Server
Quando o SQL Server começa apresentar lentidão e as operações começam a mostrar erros devido à falta de memória, ou muitas operações de I/O, neste caso recomenda-se, verificar a necessidade de redimensionar a memória do servidor SQL Server.
No momento que o servidor SQL Server é reinicializado toda a memória cache é automaticamente zerada, isto é limpa, porém em servidor de produção, esse procedimento torna-se inviável, neste cenário o ideal seria redimensionar o servidor, porém como forma paliativa, são sugeridos três comandos que podem ser úteis, são eles:
-- limpa as entradas do cache do sistema DBCC FREESYSTEMCACHE; -- limpa as entradas do cache da sessao DBCC FREESESSIONCACHE; -- elimina todas as entradas do cache de procedures DBCC FREEPROCCACHE;Quando executado os três comandos acima no servidor, será limpado a memória cache e por conseguinte liberado memória de forma imediata para o SQL Server.
Outra forma de liberar memória cache é na otimização de consultas no SQL Server, bem comum compararmos diferentes formas de construção de uma consulta (query), normalmente dentro de uma mesma sessão no SQL Server, por examplo usando MS SQL Server Management Studio.
O grande moído é que a primeira consulta (query) a ser executada, sempre é mais rápido do que a primeira, pois na primeira vez em que é executada ela é armazenada na memória cache e já segunda em diante a execução se baseia na memória cache.
Analisando com critério a query executada, faz-se necessário sempre evitar o uso do cache, ao menos no momento de otimização de consultas.
Três comandos simples a ser executado na sessão Management Studio para solucionar, eliminar, desprezar o cache de memória, são eles:
-- elimina paginas de buffer limpas DBCC DROPCLEANBUFFERS; -- elimina todas as entradas do cache de procedures DBCC FREEPROCCACHE; -- limpa as entradas de cache nao utilizadas DBCC FREESYSTEMCACHE ( 'ALL' );
A dica, é executar esses três comandos supracitados, no momento de otimizar querys.
Fique na Paz do Senhor Jesus!
quinta-feira, 6 de setembro de 2012
Consulta SQL de Plano de Contas - Query Contabil - Query para Centro de Custo
O SGBDR testados foram:
Firebird 2.5.1
Postgres 9.1
SQL Server 2012
Oracle 11g R2
Expondo o problema, exemplo:
Montar uma query relatório que mostre o resultado totalizado por níveis de contas lançadas.
O centro de custo irá ser lançado sempre no ultimo nivel e/ou conta analitica, ou seja 1.1.1 ou 1.2.1, etc.
Conforme figura Figura 01, abaixo:
Figura 01 - Relatório Sumarizado por Plano de Contas
Temos uma estrutura de centro de custo modelada da seguinte maneira:
Script para Firebird 2.5.1, Postgres 9.1, SQL Server 2012, Oracle 11g R2
-- USE tempdb; -- Descomentar caso use SQL Server --DROP TABLE centro_custo; CREATE TABLE centro_custo ( id_centro_custo varchar(12) PRIMARY KEY -- dados da conta ex. 1.02.01 , descricao varchar(50) -- descricao da conta cadastrada ex. Vendas Externas , tipo_conta varchar(1) -- tipo de conta do cc Analitica ou Sintética, dominio discreto: A ou S, em situação de produção merece uma constraint check ); --DROP TABLE movimento; CREATE TABLE movimento ( id_movimento integer PRIMARY KEY -- id do movimento, recomenda-se auto incremento, mas para simplifcar fica sem auto incremento , numero_doc varchar(40) -- numero do documento a ser informado , id_centro_custo varchar(12) -- chave estrangeira para a tabela centro de custo, mas para simplificar apenas iremos convencionar, não será habilitado a FK, recomendo colocar not null , valor_movimento numeric(15,2) -- valor informado );
Agora iremos povoar a tabela de centro de custo
-- Receitas
INSERT INTO centro_custo (id_centro_custo, descricao, tipo_conta) VALUES ('1','Receita','S');
INSERT INTO centro_custo (id_centro_custo, descricao, tipo_conta) VALUES ('1.1','Vendas Internas','S');
INSERT INTO centro_custo (id_centro_custo, descricao, tipo_conta) VALUES ('1.1.1','Escola','A');
INSERT INTO centro_custo (id_centro_custo, descricao, tipo_conta) VALUES ('1.1.2','Escritório','A');
INSERT INTO centro_custo (id_centro_custo, descricao, tipo_conta) VALUES ('1.2','Vendas Externas','S');
INSERT INTO centro_custo (id_centro_custo, descricao, tipo_conta) VALUES ('1.2.1','Livro','A');
INSERT INTO centro_custo (id_centro_custo, descricao, tipo_conta) VALUES ('1.2.2','Brinquedos','A');
-- Despesas
INSERT INTO centro_custo (id_centro_custo, descricao, tipo_conta) VALUES ('2','Despesas','S');
INSERT INTO centro_custo (id_centro_custo, descricao, tipo_conta) VALUES ('2.1','Fornecedores','S');
INSERT INTO centro_custo (id_centro_custo, descricao, tipo_conta) VALUES ('2.1.1','Nacional','A');
INSERT INTO centro_custo (id_centro_custo, descricao, tipo_conta) VALUES ('2.1.2','Importado','A');
INSERT INTO centro_custo (id_centro_custo, descricao, tipo_conta) VALUES ('2.2','Escritório','S');
INSERT INTO centro_custo (id_centro_custo, descricao, tipo_conta) VALUES ('2.2.1','Materiais de limpeza','A');
INSERT INTO centro_custo (id_centro_custo, descricao, tipo_conta) VALUES ('2.2.2','Materiais de Escritório','A');
-- Vamos povoar a tabela movimento:
INSERT INTO movimento (id_movimento, numero_doc, id_centro_custo, valor_movimento) VALUES (1,'0000021','1.1.2',50.00);
INSERT INTO movimento (id_movimento, numero_doc, id_centro_custo, valor_movimento) VALUES (2,'0000042','1.2.2',100.00);
INSERT INTO movimento (id_movimento, numero_doc, id_centro_custo, valor_movimento) VALUES (3,'0000084','1.2.2',160.00);
INSERT INTO movimento (id_movimento, numero_doc, id_centro_custo, valor_movimento) VALUES (4,'0000142','2.2.1',40.00);
INSERT INTO movimento (id_movimento, numero_doc, id_centro_custo, valor_movimento) VALUES (5,'0000210','2.2.2',80.00);
INSERT INTO movimento (id_movimento, numero_doc, id_centro_custo, valor_movimento) VALUES (6,'0000242','2.2.2',20.00);
INSERT INTO movimento (id_movimento, numero_doc, id_centro_custo, valor_movimento) VALUES (7,'0000284','2.2.1',15.00);
Listando os centros de custos cadastrados ...
-- Listando conteudo de centro_custo SELECT * FROM centro_custo ORDER BY 1;
Figura 02 - Lista de Centro Custo
Listando os movimentos cadastrados, referenciando os centros de custos ...
-- Listando conteudo de movimento SELECT * FROM movimento;
Figura 03 - Lista de Movimento
Script apenas para SQL Server 2012
-- Query SQL Centro de Custo, para SQL Server 2012, exemplo:
SELECT cc.id_centro_custo
, cc.descricao
, sum(m.valor_movimento) AS total_conta
FROM centro_custo cc
JOIN movimento m
ON m.id_centro_custo LIKE cc.id_centro_custo + '%' -- O segredo está aqui, o campo id_centro_custo vem depois do LIKE
GROUP BY cc.id_centro_custo
, cc.descricao
ORDER BY cc.id_centro_custo ASC
;
Figura 04 - Query Centro Custo com Resultado em SQL Server 2012
Script apenas para Firebird 2.5.1, Postgres 9.1, Oracle 11g R2
-- Query SQL Centro de Custo, para Firebird 2.5.1, Postgres 9.1, Oracle 11g R2, exemplo:
SELECT cc.id_centro_custo
, cc.descricao
, sum(m.valor_movimento) AS total_conta
FROM centro_custo cc
JOIN movimento m
ON m.id_centro_custo LIKE cc.id_centro_custo || '%' -- O segredo está aqui, o campo id_centro_custo vem depois do LIKE
GROUP BY cc.id_centro_custo
, cc.descricao
ORDER BY cc.id_centro_custo ASC
;
Figura 05 - Query Centro Custo com Resultado em Oracle 11g R2
A grande dica está na junção dos campos de conta usando like concatenado com '%' no final
Como no exemplo com Oracle 11g R2, abaixo descrito:
ON m.id_centro_custo LIKE cc.id_centro_custo || '%'
Existem outras formas de se chegar ao mesmo resultado, uma delas seria implementar union em contas de grupo e depois criar uma view, mas, fica complicado e não elegante, já que, será necessário alterar a query toda vez que o plano de contas mudar a estrutura.
Outra forma seria usar querys recursivas, que seria bem mais elegante que a opção anterior, entretanto, mais complexa, mas quanto ao uso de recursividade em querys, nem todos os SGBDRs atualmente dão suporte a essa técnica, outro problema reside no fato do desempenho e consumo de recursos, pois quanto mais níveis tiver o plano de contas maior será a pilha, em outras palavras, consumo de memória e processamento no servidor do banco de dados alto.
Eu particularmente já tive a oportunidade fazer parte de uma equipe de desenvolvimento, em que o meu chefe e a própria equipe insistiram em traçar uma modelagem que usava querys recursivas, apesar de não concordar, como era novato na equipe, não me deram crédito, então me reservei a sabedoria do silêncio e mesmo não concordando aproveitei a oportunidade para fazer acontecer e vê como ficaria um sistema de custos com uso maciço de querys recursivas, foi divertido, mas ainda não recomendo, pois a complexidade é alta das querys recursivas e da codificação da aplicação também, tornando o compartilhamento do conhecimento difícil, como também a manutenção da aplicação, considerando ainda o consumo extremo de memória e processamento a nível de infra-estrutura.
Mais uma vez espero ter ajudado.
Fique na Paz do Senhor Jesus Cristo !!!
terça-feira, 28 de agosto de 2012
Storing and Retrieving Images from SQL Server using Store Procedures in Transact-SQL - Armazenando e Recuperando Imagens do SQL Server usando Store Procedures em Transact-SQL
-- -- Functionality over safety -- Funcionalidade em detrimento da segurança -- Routine Tested in SQL SERVER 2008 Enterprise and SQL SERVER 2012 Enterprise -- Procedimento Testado no SQL SERVER 2008 Enterprise e SQL SERVER 2012 Enterprise -- Configuration for SQL Server procedure call xp_cmdshell -- Configuracao do SQL Server para chamada de procedure xp_cmdshell USE master; GO EXEC sp_configure 'show advanced options', 1 GO RECONFIGURE GO EXEC sp_configure 'xp_cmdshell', 1 GO RECONFIGURE GO -- make dir c:\images EXEC xp_cmdshell 'mkdir c:\images'; -- list dir c:\images EXEC xp_cmdshell 'dir c:\images\*.*';Store Procedure usp_lo_export
USE master; -- change to your database - mudar para o seu bd
GO
-- Creating Stored Procedure
-- start procedure
IF EXISTS ( SELECT name FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_lo_export]') AND type IN (N'P') )
DROP PROCEDURE usp_lo_export;
GO
CREATE PROCEDURE usp_lo_export (@bdx varchar(65), @schemax varchar(65), @tablex varchar(65), @col_bin_imgx varchar(65), @pk_tablex varchar(65), @idx bigint, @file_namex varchar(255))
AS
-- Artifact Name/Program...: usp_lo_exportx.sql
-- Author..................: Emerson Hermann (emersonhermann [at] gmail.com) O Peregrino (http://www.emersonhermann.blogspot.com)
-- Date Start..............: 28/08/2012
-- Date Update.............: 08/12/2014
-- Version.................: 0.03
-- Compiler/Interpreter....: T-SQL (Transact SQL)
-- OS......................: Windows
-- RDBMS...................: MS SQL Server 2008/2012
-- Kernel..................: Not informed!
-- Porpuse - Finalidade....: Stored Procedure to export images (varbinary) to binary file database for SO - SP para exportar imagens (varbinary) para arquivo binário do banco de dados para o SO
-- Notice .................: The parameters are: db, schema, table name, column type binary, primary key, primary key id and name of the binary file -
-- ........................: Os parametros são: bd, esquema, nome da tabela, coluna do tipo binaria, chave primária, id da chave primaria e nome do arquivo binario
--
BEGIN
SET NOCOUNT ON
BEGIN TRY
DECLARE @sql_stmt varchar(4000)
, @sql_query varchar(4000)
, @v_sql varchar(4)
, @echo_v_sql varchar(255)
, @dir_bcp varchar(255)
-- example of using bcp into SQL Server 2008 to form Bcp.fmt file (set in Y last option)
-- exemplo de uso de bcp em SQL Server 2008, para formar archive bcp.fmt (setar Y na ultima opcao)
-- bcp testdb.dbo.uvw_img out c:\images\test1.png -T
-- bcp.fmt created - ja criado
-- bcp testdb.dbo.uvw_img out c:\images\test1.png -T -f c:\images\bcp.fmt
-- model fmt file bcp (bcp.fmt) to import binary, SQL SERVER 2008
-- modelo de arquivo fmt do bcp (bcp.fmt) para importação binaria, SQL SERVER 2008
--10.0
--1
--1 SQLBINARY 0 0 "" 1 archive ""
-- generating configuration file
-- gerando arquivo de configuracao
SET @v_sql = SUBSTRING(CAST (SERVERPROPERTY('productversion') AS VARCHAR(max)),1,4) -- version sql server
SET @echo_v_sql = 'ECHO ' + @v_sql + ' > c:\images\conf_blob.fmt'
EXEC xp_cmdshell @echo_v_sql, no_output
EXEC xp_cmdshell 'ECHO 1 >> c:\images\conf_blob.fmt', no_output
EXEC xp_cmdshell 'ECHO 1 SQLBINARY 0 0 "" 1 archive "" >> c:\images\conf_blob.fmt', no_output
IF object_id('tempdb.dbo.##blobx') IS NOT NULL BEGIN
DROP TABLE ##blobx
END
CREATE TABLE ##blobx
(
file_binx varbinary(max)
)
-- populating global temporary table with the table data, where the blob
-- povoando tabela temporária global com os dados da tabela, onde se encontra o blob
SET @sql_query = 'INSERT INTO tempdb.dbo.##blobx
SELECT ' + @col_bin_imgx + '
FROM ' + @bdx + '.' + @schemax + '.' + @tablex + '
WHERE ' + @pk_tablex + ' = ' + ltrim(str(@idx))
PRINT @sql_query
EXEC (@sql_query)
-- for use with dynamic sql xp_cmdshell SP - sql dinamico para uso com SP xp_cmdshell
-- bcp tempdb.dbo.##blobx out c:\images\test1.png -T -f c:\images\bcp.fmt
IF @v_sql = '10.0' BEGIN
SET @dir_bcp = '"C:\Program Files\Microsoft SQL Server\100\Tools\Binn\bcp"'
END IF @v_sql = '11.0' BEGIN
SET @dir_bcp = '"C:\Program Files\Microsoft SQL Server\110\Tools\Binn\bcp"'
END ELSE BEGIN
SET @dir_bcp = 'bcp'
END
SET @sql_stmt = @dir_bcp + ' tempdb.dbo.##blobx out c:\images\' + ltrim(str(@idx)) + '_' + @file_namex + ' -T -f c:\images\conf_blob.fmt'
PRINT 'Exporting binary ...'
EXEC xp_cmdshell @sql_stmt, no_output
END TRY
BEGIN CATCH
SELECT ERROR_NUMBER() AS error_number
, ERROR_SEVERITY() AS error_severity
, ERROR_STATE() AS error_state
, ERROR_PROCEDURE() AS error_procedure
, ERROR_LINE() AS error_line
, ERROR_MESSAGE() AS error_message;
END CATCH;
-- cleaning dirt, eliminating temporary table - limpando sujeira, eliminando tabela temporaria
IF object_id('tempdb.dbo.##blobx') IS NOT NULL BEGIN
DROP TABLE ##blobx
END
-- cleaning, eliminating the bcp format file - limpando sugeira, eliminando arquivo de formatacao do bcp
EXEC xp_cmdshell 'DEL c:\images\conf_blob.fmt', no_output
SET NOCOUNT ON
END;
GO
-- end procedure
Working with stored procedure usp_lo_export
USE testdb; -- change to your database - mudar para o seu bd
GO
-- Creating Table Test
IF EXISTS ( SELECT name FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[image]') AND type IN (N'U') )
DROP TABLE image;
GO
CREATE TABLE image
(
id_image int identity(1,1) NOT NULL PRIMARY KEY
, file_name nvarchar(1000) NOT NULL
, archive varbinary(max) -- binary type, implemented in sql2005 sp2
);
GO
-- Only test - Apenas para teste
IF EXISTS ( SELECT name FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[uvw_img]') AND type IN (N'V') )
DROP VIEW uvw_img;
GO
CREATE VIEW uvw_img AS
SELECT TOP 1 archive FROM image
GO
-- If you need to test again - Caso precise fazer teste novamente
--TRUNCATE TABLE image;
-- Populating table with images - Povoando tabela com imagens
INSERT INTO image (file_name, archive)
SELECT 'koala.jpg', BULKColumn FROM OPENROWSET(BULK N'C:\Users\Public\Pictures\Sample Pictures\koala.jpg', SINGLE_BLOB) AS image;
INSERT INTO image (file_name, archive)
SELECT 'desert.jpg', BULKColumn FROM OPENROWSET(BULK N'C:\Users\Public\Pictures\Sample Pictures\desert.jpg', SINGLE_BLOB) AS image;
INSERT INTO image (file_name, archive)
SELECT 'penguins.jpg', BULKColumn FROM OPENROWSET(BULK N'C:\Users\Public\Pictures\Sample Pictures\penguins.jpg', SINGLE_BLOB) AS image;
INSERT INTO image (file_name, archive)
SELECT 'plan_test.xlsx', BULKColumn FROM OPENROWSET(BULK N'C:\Users\Public\Documents\plan_test.xlsx', SINGLE_BLOB) AS Image;
GO
-- Listing images
SELECT * FROM image;
-- Call the Store Procedure - Examples
-- Ex 01 - correct data reported
EXEC master.dbo.usp_lo_export 'testdb','dbo','image','archive','id_image',1,'koala.jpg';
EXEC master.dbo.usp_lo_export 'testdb','dbo','image','archive','id_image',2,'desert.jpg';
EXEC master.dbo.usp_lo_export 'testdb','dbo','image','archive','id_image',3,'penguins.jpg';
SELECT * FROM image;
-- Ex 02 - Table wrongly informed - Tabela informada errada
EXEC master.dbo.usp_lo_export 'testdb','dbo', 'imagex','archive','id_image',3,'penguins.jpg';
-- Ex 03 - Correct data reported, electronically excel spreadsheet - Dados informados corretos, planilha eletronica do excel
EXEC master.dbo.usp_lo_export 'testdb','dbo','image','archive','id_image',4,'plan_excel.xlsx';
-- Ex 04 - Use with anonymous block - Uso com bloco anônimo
DECLARE @file_namex varchar(255)
, @idx bigint
SET @idx = 1
SELECT @file_namex = file_name FROM image WHERE id_image = @idx
EXEC master.dbo.usp_lo_export 'testdb','dbo','image','archive','id_image', @idx, @file_namex;
The peace of the Lord Jesus
sexta-feira, 3 de agosto de 2012
Algoritmos, Caixa Eletrônico em SQL SERVER
--Retornando notas do caixa eletrônico
--Notas de 1, 2, 5, 10, 20, 50 e 100
IF EXISTS (
SELECT *
FROM sys.objects
WHERE object_id = OBJECT_ID(N'[dbo].[usf_caixa_eletro]')
AND type IN (N'FN')
)
DROP FUNCTION usf_caixa_eletro;
CREATE FUNCTION usf_caixa_eletro (@pvalor int) RETURNS varchar(max) AS
--
-- Nome Artefato/Programa..: usf_caixa_eletro_sql_server.sql
-- Autor(es)...............: O Peregrino (emersonhermann at gmail.com)
-- Data Inicio ............: 02/08/2012
-- Data Atual..............: 03/08/2012
-- Versao..................: 0.01
-- Linguagem...............: TRANSACT
-- Compilador/Interpretador: T-SQL
-- Sistemas Operacionais...: Windows
-- SGBD....................: SQL SERVER 2005/2008/2012
-- Kernel..................: Nao informado!
-- Finalidade..............: Caixa Eletronico
-- OBS1....................: Caixa Eletronico
--
/*
Algoritmo Caixa Eletronico
notasSaída = [] #guardar as notas que saírão do caixa eletrônico
notas = [100, 50, 20, 10, 5, 1] #notas que podem ser sacadas
valor = 375 #valor a ser sacado
restante = valor #faz uma cópia do valor em "restante"
inota = 0 #índice da nota: 0 é 100, 1 é 50, 2 é 20, 3 é 10, ...
enquanto restante>0: #enquanto restante for maior que 0
resultado = restante-notas[inota] #calcula o resultado da subtração entre o valor e a nota
se resultado<0: #se for negativo:
inota++ #incrementa o índice para a próxima nota
senão: #se for positivo ou zero:
restante = resultado #deixa restante com o novo resultado
notasSaída.adicionar(notas[inota]) #adiciona a nota utilizada nas que devem sair do caixa
para nota em notasSaída: # escreve as notas que devem sair
escreva nota
*/
BEGIN
DECLARE
@sretorno varchar(max)
,@qnota1 integer
,@qnota2 integer
,@qnota5 integer
,@qnota10 integer
,@qnota20 integer
,@qnota50 integer
,@qnota100 integer
,@pvalorx integer
,@residual integer
,@restante integer
,@vet_notas1 integer
,@vet_notas2 integer
,@vet_notas3 integer
,@vet_notas4 integer
,@vet_notas5 integer
,@vet_notas6 integer
,@vet_notas7 integer
,@i integer
,@resultado integer
SET @vet_notas1=100
SET @vet_notas2=50
SET @vet_notas3=20
SET @vet_notas4=10
SET @vet_notas5=5
SET @vet_notas6=2
SET @vet_notas7=1
SET @i = 1
SET @qnota1 = 0
SET @qnota2 = 0
SET @qnota5 = 0
SET @qnota10 = 0
SET @qnota20 = 0
SET @qnota50 = 0
SET @qnota100 = 0
SET @pvalorx = 0
SET @resultado = 0
SET @sretorno = ''
SET @restante = @pvalor
WHILE (@i <= 7) BEGIN
IF @i = 1 BEGIN
SET @resultado = @restante - @vet_notas1
END ELSE IF @i = 2 BEGIN
SET @resultado = @restante - @vet_notas2
END ELSE IF @i = 3 BEGIN
SET @resultado = @restante - @vet_notas3
END ELSE IF @i = 4 BEGIN
SET @resultado = @restante - @vet_notas4
END ELSE IF @i = 5 BEGIN
SET @resultado = @restante - @vet_notas5
END ELSE IF @i = 6 BEGIN
SET @resultado = @restante - @vet_notas6
END ELSE IF @i = 7 BEGIN
SET @resultado = @restante - @vet_notas7
END
IF (@resultado < 0) BEGIN
SET @i = @i + 1
END ELSE BEGIN -- senao
SET @restante = @resultado
IF @i = 1 BEGIN
SET @qnota100 = @qnota100 + 1
END ELSE IF @i = 2 BEGIN
SET @qnota50 = @qnota50 + 1
END ELSE IF @i = 3 BEGIN
SET @qnota20 = @qnota20 + 1
END ELSE IF @i = 4 BEGIN
SET @qnota10 = @qnota10 + 1
END ELSE IF @i = 5 BEGIN
SET @qnota5 = @qnota5 + 1
END ELSE IF @i = 6 BEGIN
SET @qnota2 = @qnota2 + 1
END ELSE IF @i = 7 BEGIN
SET @qnota1 = @qnota1 + 1
END --fim_se
END -- fim_se
END -- fim_enquanto
SET @sretorno = 'Total: '
+ cast (@pvalor as varchar(max))
+ ' ' --chr(10)
+ 'Notas de 100:'
+ cast (@qnota100 as varchar(max))
+ ' ' --chr(10)
+ 'Notas de 50:'
+ cast (@qnota50 as varchar(max))
+ ' ' --chr(10)
+ 'Notas de 20:'
+ cast (@qnota20 as varchar(max))
+ ' ' --chr(10)
+ 'Notas de 10:'
+ cast (@qnota10 as varchar(max))
+ ' ' --chr(10)
+ 'Notas de 5:'
+ cast (@qnota5 as varchar(max))
+ ' ' --chr(10)
+ 'Notas de 2:'
+ cast (@qnota2 as varchar(max))
+ ' ' --chr(10)
+ 'Notas de 1:'
+ cast (@qnota1 as varchar(max))
RETURN (@sretorno) -- Retorna as linhas
END;
GO
--alguns testes, chamada da function
--SELECT dbo.usf_caixa_eletro(2678);
--SELECT dbo.usf_caixa_eletro(1078);
sexta-feira, 29 de junho de 2012
DECODE ZERO NO SQL SERVER
Segue um script para simular uso da função DECODE do SGBDR Oracle, nesta function, adaptei para situação em que o valor for zero,
por exemplo uma situação em que tem que dividir um valor por zero.
IF EXISTS (
SELECT *
FROM sys.objects
WHERE object_id = OBJECT_ID(N'[dbo].[usf_decode_zero]')
AND type IN (N'FN')
)
DROP FUNCTION usf_decode_zero;
GO
CREATE FUNCTION usf_decode_zero(@param1 float, @param2 float) RETURNS float AS
-- Nome Artefato/Programa..: usf_decode_zero.sql
-- Autor(es)...............: Emerson Hermann (emersonhermann [at] gmail.com) O Peregrino (http://www.emersonhermann.blogspot.com)
-- Data Inicio ............: 28/06/2012
-- Data Atualizacao........: 28/06/2012
-- Versao..................: 0.01
-- Compilador/Interpretador: T-SQL (Transact SQL)
-- Sistemas Operacionais...: Windows
-- SGBD....................: MS SQL Server 2005/2008
-- Kernel..................: Nao informado!
-- Finalidade..............: Store Procedure (Function) verifica se o valor do primeiro parametro é igual a zero, retornando o valor do
-- ........................: segundo parametro2, semalhante a função decode do Oracle, porém simplificado.
-- OBS.....................: O primeiro parametro é um float, retorna o valor do segundo parametro que também é flaot, caso o primeiro
-- ........................: parametro seja igual a zero, caso contrário retorna o valor do primeiro parametro.
-- ........................:
--
BEGIN
RETURN
(
SELECT CASE WHEN @param1 = 0 THEN
@param2
ELSE
@param1
END AS usf_decode_zero
)
END;
GO
-- exemplo do uso da function
SELECT dbo.usf_decode_zero (0,1200); --Retorno: 1200
SELECT dbo.usf_decode_zero (1100,1); --Retorno: 1100
SELECT dbo.usf_decode_zero (NULL,1); --Retorno: NULL
-- exemplo tratando, o erro de divisão por zero, Mensagem 8134 (CLÁSSICA)
SELECT 2400/dbo.usf_decode_zero(0,1); --Retorno: 2400
SELECT 2400/dbo.usf_decode_zero(2400,1); --Retorno: 1
SELECT 2400/dbo.usf_decode_zero(NULL,1); --Retorno: NULL
Assinar:
Comentários (Atom)