O Peregrino

Erro: SQL0290N Table space access is not allowed. SQLSTATE=55039

2017-08-17T16:00:00.002-03:00

Erro: SQL0290N Table space access is not allowed. SQLSTATE=55039

Problema: O problema ocorre quando se vai persistir algo (tabela, etc) em alguma tablespace.

Comentário: O IBM DB2 é bastante seguro quanto a este requisito, pois em se tratando de novas tablespace criadas ou alteras, o IBM DB2 por segurança bloquea qualquer persistencia enquanto não for efetuado backup.

Requisitos:
SO: Windows
SGBDR: IBM DB2: v 9.5

Verificando o Problema:

cmd 
g:
cd\
mkdir backup
db2cmd 
db2 
db2 => connect to MEU_BANCO_DADOS user MEU_USUARIO_DB2_ADM
db2 => list tablespaces

Tablespace ID = 2
Name = USERSPACE1
Type = Database managed space
Contents = All permanent data. Large table space.
State = 0x0020
Detailed explanation:
Backup pending

Solução:

db2 => backup db MEU_BANCO_DADOS online to g:\backup

Verificando a Solução:

db2 => list tablespaces

Tablespace ID = 2
Name = USERSPACE1
Type = Database managed space
Contents = All permanent data. Large table space.
State = 0x0000
Detailed explanation:
Normal

Mais uma vez espero ter ajudado!
APSJC!

Bíblia Português João Ferreira de Almeida - Edição Revista e Corrigida em formato Linux Fortune

2015-04-23T16:57:00.001-03:00

Para quem gosta de usar o Linux Fortune Segue a Bíblia em Português João Ferreira de Almeida - Edição Revista e Corrigida em formato Linux Fortune, conforme link abaixo: BÍBLIA_JFARC_FORTUNES

Ultimo Dia Util Considerando Feriados em Oracle PL/SQL

2015-04-02T14:36:00.000-03:00

/* Geralmente é complicado obter o ultimo dia válido considerando os feriados nacionais,
muito usado no ramo financeiro. */

/* Esse artigo pretende dá uma opção de como obter o ultimo dia util dentre várias soluções
encontradas na net, com o uso de stores functions do Oracle PL/SQL */

/* Mostrando como criar uma função SQL Oracle isto é em PL/SQL para obter o ultimo dia útil, considerando os feriados nacionais dentre outros assuntos correlacionados. */

/* Primeiro criamos uma tabela feriado para melhor elucidar as store functions, conforme script abaixo: */


-- DROP TABLE FERIADO;
CREATE TABLE FERIADO
(
   ID_FERIADO                       NUMBER(10,0)       NOT NULL ENABLE
 , DATA                             DATE               NOT NULL ENABLE
 , DESCRICAO                        VARCHAR2(50 BYTE)  NOT NULL ENABLE
 , ID_TIPO_FERIADO                  NUMBER(3,0)        NOT NULL ENABLE -- 1 NACIONAL, 2 ESTADUAL, 3 MUNICIPAL, 4 OUTROS
 , OBS                              NVARCHAR2(15)
 , CONSTRAINT PK_FERIADO_ID_FERIADO PRIMARY KEY (ID_FERIADO)
 , CONSTRAINT UQ_FERIADO_DATA UNIQUE (DATA)
);

-- DROP SEQUENCE FERIADO_FCSEQ; 
CREATE SEQUENCE FERIADO_FCSEQ
INCREMENT BY 1
START WITH 1
NOCYCLE;

-- DROP TRIGGER TG_FERIADO_ID_FERIADO_BI;
CREATE OR REPLACE TRIGGER TG_FERIADO_ID_FERIADO_BI BEFORE INSERT ON FERIADO
FOR EACH ROW
 WHEN (new.ID_FERIADO IS NULL) BEGIN
  SELECT FERIADO_FCSEQ.NEXTVAL INTO :new.ID_FERIADO FROM dual;
END;
/
ALTER TRIGGER TG_FERIADO_ID_FERIADO_BI ENABLE;

/* Se desejar usar o o arquivo CSV dos feriados chamado FERIADOS.CSV com CRLF no final de cada linha, use o SQL Loader do Oracle para dá carga do arquivo, evitando assim digitação de datas, é possível obtê-lo CLICANDO AQUI! */
/* Criando o arquivo de controle chamado FERIADO.CTL salvo no diretorio C:\TEMP */

load data
  infile 'FERIADO.CSV'
  into table feriado
  fields terminated by "|"
  (
      data  DATE "YYYY-MM-DD"
    , descricao
    , id_tipo_feriado
    , obs
  )

/* Depois executar o SQL Loader, no Linux é igual ao do Windows considerando nome da pasta, no Windows fica assim: */
/* Lembrando de considerar, isto é, permutar seu usuario e senha, ip_servidor e SID do Oracle do seu servidor*/

sqlldr usuario/senha@ip_servidor/SID control=FERIADO.CTL log=X.LOG bad-Z.BAD READSIZE=10000000

/* Depois criamos uma store function para idenficar dias feriados usando a tabela feriado conforme script abaixo: */


CREATE OR REPLACE FUNCTION usf_feriado (d in date) 
RETURN integer 
AS
  
  x integer; 

BEGIN 

  SELECT count(*) 
    INTO x
    FROM feriado 
   WHERE to_char(data,'YYYY-MM-DD') = to_char(d,'YYYY-MM-DD'); 
   
  IF (x >= 1) THEN 
      RETURN 1;
  ELSE 
      RETURN 0; 
  END IF;
  
END usf_feriado;

/* Logo em seguida criamos um store function para tratar o ultimo dia util, conforme script abaixo: */


CREATE OR REPLACE FUNCTION usf_ultimo_dia_util ( dt_base in date ) 
RETURN date 
AS

  dt_basex date; 
  bo_fimx  boolean; 

BEGIN 

  dt_basex := dt_base; 
  bo_fimx  := false; 
  
  WHILE NOT (bo_fimx) LOOP 
  
      bo_fimx := to_char(dt_basex,'d') NOT IN ('1','7');
      
      IF usf_feriado(dt_basex) = 1 THEN 
          bo_fimx := false;
      END IF; 
      
      IF NOT (bo_fimx) THEN 
          dt_basex := dt_basex - 1; 
      END IF; 
      
  END LOOP; 
  
  RETURN dt_basex; 
  
EXCEPTION 

  WHEN others THEN 
  RAISE; 
  
  
END usf_ultimo_dia_util;

/* Testando o uso da function em SQL puro */


SELECT usf_feriado(sysdate) AS DT FROM DUAL; -- RETORNA 1 PARA FERIADO E 0 PARA OUTROS DIAS 

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD'; -- ALTERA DATA PARA FORMATO 'YYYY-MM-DD'

SELECT usf_ultimo_dia_util ('2015-04-05') from dual; -- RETORNA O ULTIMO DIA UTIL -> 2015-04-02

/* Espero ter ajudado! */

/* Seja Feliz!! */

/* APdSJC */

Vida de DBA

2015-03-02T10:27:00.000-03:00

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/

Como Listar Estrutura de Campos e Tabelas via query em SQL Server

2014-07-09T10:43:00.000-03:00

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.

Código, Algoritmo de um Plano de Contas escrito em VB

2013-12-26T10:42:00.001-03:00

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.

'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

Mais uma vez espero ter ajudado!
APdSJC!

Desenvolvendo Querys Compativeis com Todos os SGBDRs com Ajuda do SQL Fiddle

2013-11-29T17:53:00.003-03:00

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!

Converter várias linhas em uma única linha no SQL Server

2013-08-01T01:00:00.000-03:00

Como converter várias linhas em uma única linha no SQL Server, separando por ponto e virgula ";".


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
     ;

Mais uma vez espero ter ajudado ...

Remover caracteres especiais em campos do Postgres

2013-07-24T01:56:00.000-03:00

Function scalar para Postgres, com objetivo de remover caracteres especiais, exatamente 33 caracteres de controle (especiais) menos o NULL ficando 32 ao todo, recomendo usar essa store function em procedimentos de ETL, migração de dados ou em consultas que precisem remover esses tais caracteres da tabela ASCII.

Fica aqui o compartilhamento do conhecimento.

Abraços.

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

Test 2


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

Desenvolvendo querys SQL para Razão e Balancete Contábil.

2013-01-29T13:16:00.006-03:00

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)

-- 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!

Formatar CNPJ em SQL

2012-09-19T22:21:00.000-03:00

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

2012-09-19T22:00:00.000-03:00

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

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

2012-09-19T14:22:00.000-03:00

O cache de memória, é uma área de memória reservada pelo SQL Server, com o objetivo de se evitar ao máximo acesso ao disco magnético, pois o barramento deste periférico é bem inferior ao da memória e do processador, analogamente seria como sistema de encanamento com canos pequenos e grandes, mas para manter a vazão cria-se um caixa de água na mudança de diâmetro desses canos, o cache de memória é a caixa de água, sendo assim a entrada e a saída no meio magnético (Imput-Output, I/O) será reduzida, acelerando a execução de procedures, transações, querys que são processadas com maior maior frequência.

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!

Consulta SQL de Plano de Contas - Query Contabil - Query para Centro de Custo

2012-09-06T15:05:00.000-03:00

A idéia primordial desse artigo é demonstrar como desenvolver uma query sql usando um plano de contas ou centro de custo, o principio é o mesmo, subtotalizando de forma invertida, considerando uma estrutura de balancete ou centro de custo, mas sempre usando um plano de contas com contas analiticas e sintéticas e vários niveis de sub-contas.

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 !!!

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

2012-08-28T14:45:00.000-03:00

In SQL Server does not have the capability to export a varbinary (BLOB) images directly via SQL, for the Operating System (Windows), just as import, export is done via SSRS or using any programming language, but it was developed a stored procedure called usp_lo_export, below, follows the solution to the problem in question.

No SQL Server não tem o recurso para exportar um varbinary (blob), imagens, diretamente via SQL, para o Sistema Operacional (Windows), apenas como importar, a exportação é feita via SSRS ou usando alguma linguagem de programação, mas para isso foi desenvolvido uma store procedure chamada usp_lo_export, abaixo, segue a solução para o problema em questão.

Configuration for SQL Server 2008/2012

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

Algoritmos, Caixa Eletrônico em SQL SERVER

2012-08-03T12:35:00.000-03:00

Anteriormente em setembro/2010, havia escrito um script para caixa eletrônico em linguagem PL/PgSQL do Postgres, agora foi desenvolvido o mesmo algoritmo de caixa eletrônico para a linguagem Transact do SQL SERVER.

O parâmetro é o valor em inteiro no qual retorna as cédulas das notas em Real.

Com poucas adaptações, pode se remover a nota de 1 Real, para ficar com o novo padrão de cédulas da moeda Real do Brasil.

Mais uma vez, espero ter ajudado.

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

DECODE ZERO NO SQL SERVER

2012-06-29T12:15:00.000-03:00

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

Como alterar o COLLATION de um BD no SQL Server 2005 ou 2008

2012-04-20T15:27:00.001-03:00

Esse artigo se propõe a ensinar como alterar os collations de todos os bancos de dados de uma instancia SQL Server 2005/2008, inclusive tabelas e campos.

As vezes ou quase sempre não é possível executar o comando abaixo e mesmo que execute será necessário mudar os colations das tabelas e colunas:
--ALTER DATABASE nome_banco COLLATE Latin1_General_CI_AI;

Outro problema é que se os collations estiverem errados nos campos, dependendo da aplicação as consultas irão gerar resultados inesperados.

Figura 01 - Listando os Collations dos Bancos de Dados

Foi observado que caso o collation que queira se mudar seja igual ao do master será necessário informar outro collation diferente para que haja mudança total e depois informar novamente o collation que se deseja.

Por exemplo:

Se deseja mudar o collation para Latin1_General_CI_AI, mas o BD master está com o mesmo collation e o BD do usuário está com outro collation a mudança não será possível, então será necessário mudar o collation do BD master por exemplo: Latin1_General_CI_AS para depois mudar novamente Latin1_General_CI_AI.

Observar também que será necessário que o BD do usuário esteja na pasta padrão data do SQL Server. Exemplo de onde fica a pasta data do SQL Server 2008 com nome da instancia MSSQLSERVER (padrão) na pasta:
C:\Arquivos de Programas\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\data.

Figura 02 - Pasta DATA padrão do MS SQL SERVER 2008

Neste caso o procedimento para mudança do collation se faz necessário parar a instancia do SQL SERVER, mas para isso por segurança vamos parar todos os serviços do SQL SERVER, neste caso usei a forma nativa do mesmo, pelo Sql Server Configuration Manager, em Iniciar->Executar digite:

SQLServerManager10.msc

Figura 03 - Parando Todos os Serviços do MS-SQL Server

Exemplo do comando a ser executado em uma instancia com MS-SQL SERVER 2008 ou em Inicar->Executar

C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Binn\sqlservr -m -sMSSQLSERVER -T4022 -T3659 -q"Latin1_General_CI_AI"

Figura 04 - Comando Executado para Mudança de Collation em Iniciar->Executar

Figura 05 - Mudança de Collation sendo Processada

Figura 06 - Final do Processo de Mudança de Collation, Pressionar Y

Outro ponto a observar é que em casos que o BD do usuário tenha algum problema do tipo corrupção, mesmo que ainda não esteja no nível de SUSPECT será necessário dá um DBCC CHECKDB ('NomeBanco',repair_allow_data_loss); e em casos mais extremos, isto é paginação com problemas, usar DBCC UPDATEUSAGE (NomeBanco); depois de corrigidos os problemas no BD, repetir o processo de conversão de collation.

Figura 07 - Erro na Conversão do Collation por Motivo de BD com Problemas

Figura 08 - Executando reparação de erros no BD

Depois de concluido com sucesso a conversão do collation, iniciar o serviço do SQL SERVER pelo Sql Server Configuration Manager, em Iniciar->Executar digite:

SQLServerManager10.msc

Para verificar as mudanças efetuadas, liste os collations dos Bancos de Dados, conforme Figura 01.

Uma outra dica é desatachar os bancos de dados do ReportServer, caso não deseje também mudar o collation do mesmo,
lembrar de parar o serviço do ReportServer antes de desatachar esses bancos.

Segue script abaixo:

-- 0. Caso haja problemas de corrupção, mas ainda não está no nível de suspect com o Banco de Dados, será necessário executar os procedimentos elencados, se não pule essa parte.
ALTER DATABASE NomeBanco SET SINGLE_USER WITH ROLLBACK IMMEDIATE; 
DBCC CHECKDB ('NomeBanco',repair_allow_data_loss);
DBCC UPDATEUSAGE (NomeBanco);
ALTER DATABASE NomeBanco SET MULTI_USER WITH ROLLBACK IMMEDIATE; 

-- 1. Listar os tipos de collations Latin1_General%
SELECT * 
  FROM ::fn_helpcollations()
 WHERE name LIKE 'Latin1_General%'
  
-- 2. Listar os bancos com os seus respectivos collations (via query) 
SELECT getdate();
SELECT collation_name
     , * 
  FROM sys.databases 
 WHERE name LIKE '%nome_banco%';

-- 3. Listar os bancos com os seus respectivos collations (via store procedure) 
sp_helpdb; 

/* 
-- 4. Parar o serviço do MS-SQL SERVER em Configuration Manager ou usando o comando direto (sqlservermanager10.msc) ou usando services.msc 

-- 5. Rodar esse comando no prompt de comando (cmd.exe), ao executar esse comando o MS-SQL entrar em modo single user mode e altera todos os collations 
-- de todos os bancos, tabelas e campos, neste caso altera o "Nome_Instancia_MSSQL" e o "Nome_Collation" para o adequando nesse caso para Latin1_General_CI_AI

sqlservr -m -sNome_Instancia_MSSQL -T4022 -T3659 -q"Nome_Collation"

-- 6. Exemplo do comando a ser executado em uma instancia com MS-SQL SERVER 2005 ou em Inicar->Executar
C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn\sqlservr -m -sMSSQL -T4022 -T3659 -q"Latin1_General_CI_AI"

-- ou se a instancia que estiver com nome diferente 
C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn\sqlservr -m -sMSSQL.1 -T4022 -T3659 -q"Latin1_General_CI_AI"

-- ou pelo prompt do cmd já pasta binn informada acima digitar:
sqlservr -m -sMSSQL.1 -T4022 -T3659 -q"Latin1_General_CI_AI"


-- 7. Exemplo do comando a ser executado em uma instancia com MS-SQL SERVER 2008 ou em Inicar->Executar
C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Binn\sqlservr -m -sMSSQLSERVER -T4022 -T3659 -q"Latin1_General_CI_AI"

-- ou pelo prompt do cmd já pasta binn informada acima digitar:

sqlservr -m -sMSSQLSERVER -T4022 -T3659 -q"Latin1_General_CI_AI"


-- 8. Quando concluir encerrar, isto é, depois que aparacer a seguinte mensagem: 
-- The default collation wa successfully changed. 
-- Recovery is complete. This is an informational message only. No user action is required.
-- Pressionar CTRL + C e depois iniciar o serviço do MS-SQL SERVER (vê item 4, inciando o serviço)

-- Caso não consiga exito executar o comando DBCC CHECKDB, pois deve ter alguma corrupção no BD.
-- 9. Depois conferir se os collations dos bancos mudaram inclusive master, tempdb, msdb (vê item 3 ou item 2 mudando o nome do banco)

*/

Vale salientar que esse procedimento, não é documentado pela Microsoft, mas a conversão funciona no MS SQL Server 2005 e 2008 sem nenhum problema.

Mais uma vez... espero ter ajudado...

Shrink MSSQL Server 2005 e 2008

2012-04-03T13:09:00.000-03:00

Objetivo deste artigo tem com principio a orientação de como proceder redução de LOG e BD no MSSQL SERVER 2005 e 2008.

Lembro também que execução de SHRINK, impossibilita a recuperação do BD, caso ocorra alguma suspeita de corrupção do banco de dados (SUSPECT), todavia é recomendável configurar plano de manutenção, evitando assim muitas problemas.

Recomenda-se backup full antes de executar os scripts abaixo:

No MSSQL SERVER 2005 para MDF e LDF


--SHIRINK SQL SERVER 2005, formato 1, SHIRINK DE LDF e MDF 
--Alterar o [nome_banco] para seu banco de dados 
USE [nome_banco]; 
GO
BACKUP LOG [nome_banco] WITH TRUNCATE_ONLY;
GO
DBCC SHRINKDATABASE([nome_banco], 10, TRUNCATEONLY);

No MSSQL SERVER 2008 para MDF e LDF

--SHIRINK SQL SERVER 2008, formato 1, se tiver como recovery_mode full, SHIRINK DE LDF e MDF 
--Lista bancos com os modos de recuperação 
SELECT name, recovery_model, recovery_model_desc, state_desc, collation_name, user_access_desc, create_date 
FROM sys.databases;
GO

--Alterar o [nome_banco] para seu banco de dados 
USE [nome_banco]; 
GO

BACKUP LOG [nome_banco] TO DISK = 'NUL:';
GO

DBCC SHRINKDATABASE([nome_banco], 10, TRUNCATEONLY);
GO

Truncando apenas o LOG (LDF) MS SQL SERVER 2005


--SHIRINK SQL SERVER 2005, formato 2, SHIRINK DE LOG 
--Alterar o [nome_banco] para seu banco de dados 
USE [nome_banco];
GO

BACKUP LOG [nome_banco] WITH TRUNCATE_ONLY;
GO

DECLARE @log_file_logical_name sysname;
SELECT @log_file_logical_name=name FROM sys.database_files d WHERE type = 1;
PRINT @log_file_logical_name;

DBCC SHRINKFILE (@log_file_logical_name, 1);
GO

Truncando apenas o LOG (LDF) MS SQL SERVER 2008

--SHIRINK SQL SERVER 2008, formato 2, SHIRINK DE LOG 
--USANDO RECOVERY SIMPLE, isto é, desabilitando o LOG detalhado 
--Alterar o [nome_banco] para seu banco de dados 
SELECT name, recovery_model, recovery_model_desc, state_desc, collation_name, user_access_desc, create_date FROM sys.databases;
GO

USE [nome_banco];
GO
ALTER DATABASE [nome_banco] SET RECOVERY SIMPLE; 
GO

DECLARE @log_file_logical_name sysname;
SELECT @log_file_logical_name=name FROM sys.database_files d WHERE type = 1;
PRINT @log_file_logical_name;

DBCC SHRINKFILE (@log_file_logical_name, 1);
GO

-- Retornando para recovery_model para full 
ALTER DATABASE [nome_banco] SET RECOVERY FULL ;

Espero ter ajudado!

Store Function SQL Server para validar CHAVE de NFE

2012-03-29T17:02:00.001-03:00

Store Function SQL Server para validar CHAVE de NFE

Store function para validar CHAVE de NFE em SQL do MSSQL SERVER 2005 e 2008.
Essa function serve para validar Nota Fiscal Eletrônica, a entrada de parametro é um varchar
retorna 1 para CHAVE de NFE válida e 0 para CHAVE de NFE inválida e NULL quanto for NULO.

Segue a function....


IF EXISTS (
            SELECT * 
              FROM sys.objects 
             WHERE object_id = OBJECT_ID(N'[dbo].[usf_calcula_dv_nfe]') 
               AND type IN (N'FN')
           )
   DROP FUNCTION usf_calcula_dv_nfe;
 
GO
  
CREATE FUNCTION usf_calcula_dv_nfe(@number varchar(max)) RETURNS bit
-- Nome Artefato/Programa..: usf_calcula_dv_nfe.sql
-- Autor(es)...............: Emerson Hermann (emersonhermann [at] gmail.com) O Peregrino (http://www.emersonhermann.blogspot.com) 
-- Data Inicio ............: 29/03/2012
-- Data Atualizacao........: 29/03/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)  que calcula o digito verificador da chave de acesso NFe usando o modulo 11
-- OBS.....................: A entrada é um varchar e o retorno é um  bit 1 para valido e 0 para invalido 
-- ........................: 
--  
AS
BEGIN
 DECLARE @i       int,
         @ix      int, 
         @c       int,
         @keyx    int,
         @numberx varchar(44), 
         @r       int
 
      
 SET @c    = 2
 SET @i    = 1
 SET @ix   = 43 
 SET @keyx = 0 
 
 -- Pre-validacao 1, se e nulo, entao retorna nulo
 IF @number IS NULL BEGIN
    SET @r = NULL
    RETURN (@r)  
 END --fim_se      
 
 -- Pre-validacao 2, se e maior que 44 digitos , entao retorna 0 
 IF LEN(@number) < 44 BEGIN
    SET @r = 0 
    RETURN (@r)
 END --fim_se
 
 -- Pre-validacao 3, se e tem alguma letra, entao retorna 0 
 IF (SELECT CASE WHEN patindex('%[^0-9]%', @number) > 0 THEN 1 ELSE 0 END) = 1 BEGIN
    SET @r = 0
    RETURN (@r)
 END --fim_se  
 
 -- Pre-validacao 4, se e menor que 44 digitos , pode ser oriundo de bigint, então fazer tratamento de zeros 
 SET @numberx = @number
 IF LEN(@number) < 44 BEGIN
    SET @numberx = REPLICATE('0',44-LEN(@number))+@number
 END --fim se
  
 -- Loop por cada numero mutiplicando-o pelos valores de @c
 -- Simulando o for invertido com passo -1 
 WHILE (@i <= (len(@numberx)-1)) BEGIN 
   
    --Verifica se o valor de @c for maior que nove, entao passa o valor pra 2
    IF @c > 9 BEGIN
       SET @c = 2
    END --fim_se

    --Soma os valores mutiplicados
    SET @keyx = @keyx + (convert(int,substring(@number,@ix,1)) * @c) 
  
    --Controle de contadores
    SET @c = @c + 1
    SET @i = @i + 1 
    SET @ix = @ix - 1 
 
 END  --fim_enquanto 
  
 --Obtem o digito verificador
 IF ((@keyx % 11) = 0 OR (@keyx % 11) = 1) BEGIN
    SET @r = 0
 END ELSE BEGIN --senao   
    SET @r  = 11 - (@keyx % 11)
 END --fim_se
  
 IF @r = convert(int,substring(@numberx,len(@numberx),1)) BEGIN
    SET @r = 1 
 END ELSE BEGIN  --senao   
    SET @r = 0       
 END --fim_se
  
 RETURN (@r)
 
END;
GO
-- Chamada da function 

SELECT dbo.usf_calcula_dv_nfe('24110509540525000194550010000007091242050760');
SELECT dbo.usf_calcula_dv_nfe('24110509540525000194550010000007071681710981');

-- Habilitando no campo da CHAVE de NFE (chave_nfe)
/*
ALTER TABLE nota_fiscal_eletronica
  ADD CONSTRAINT ck_nota_fiscal_eletronica_usf_calcula_dv_nfe 
CHECK (dbo.usf_calcula_dv_nfe(chave_nfe)=1); 
*/ 
GO

Formatar CPF em SQL

2012-03-27T19:00:00.000-03:00

Store function para formatar CPF em SQL Server

IF EXISTS (
            SELECT * 
              FROM sys.objects 
             WHERE object_id = OBJECT_ID(N'[dbo].[usf_formata_cpf]') 
               AND type IN (N'FN')
           )
 DROP FUNCTION dbo.usf_formata_cpf;
GO

CREATE FUNCTION dbo.usf_formata_cpf(@cpf varchar(max), @mascara bit) RETURNS varchar(max) 
AS
-- Nome Artefato/Programa..: usf_formata_cpf.sql
-- Autor(es)...............: Emerson Hermann (emersonhermann [at] gmail.com) O Peregrino (http://www.emersonhermann.blogspot.com) 
-- Data Inicio ............: 21/02/2012
-- Data Atualizacao........: 21/02/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 CPF
-- OBS.....................: A entrada é um varchar e o retorno é um varchar formatado do cpf, parametro mascara setado em 0 apenas string com zeros, 1 formata o cpf de fato 
-- ........................: 
--  
BEGIN

  DECLARE @cpf_temp varchar(max) 
  
  -- Pre-validacao 1, se e nulo, entao retorna nulo
  IF @cpf IS NULL BEGIN
     RETURN (@cpf)  
  END --fim_se      

  -- Pre-validacao 2, se e diferente de 11 digitos , entao retorna 0 
  IF LEN(@cpf) > 11 BEGIN
     RETURN (@cpf)
  END --fim_se
  
  -- Pre-validacao 3, se e tem alguma letra no cpf, entao retorna 0 
  IF (SELECT CASE WHEN patindex('%[^0-9]%', @cpf) > 0 THEN 1 ELSE 0 END) = 1 BEGIN
     RETURN (@cpf)
  END --fim_se  

  -- Pre-validacao 4, se e menor que 11 dig, pode ser oriundo de um bigint, entao colocar zeros a frente
  SET @cpf_temp = @cpf 
  IF LEN(@cpf) < 11 BEGIN
     SET @cpf_temp = REPLICATE('0',11-LEN(@cpf))+@cpf
  END --fim_se 
  
  -- Se e para formatar mesmo 
  IF @mascara = 1 BEGIN
     SET @cpf_temp = SUBSTRING(@cpf_temp,1,3) + '.' + SUBSTRING(@cpf_temp,4,3) + '.' + SUBSTRING(@cpf_temp,7,3) + '-' + SUBSTRING(@cpf_temp,10,2) 
  END --fim_se 
  
  RETURN (@cpf_temp) 
  
END;
GO

-- chamada da function 

--SELECT dbo.usf_formata_cpf('481604472z',0);
--SELECT dbo.usf_formata_cpf('0541371479',1);
--SELECT dbo.usf_formata_cpf('05413714793',1);
--SELECT dbo.usf_formata_cpf(18404,1);
--SELECT dbo.usf_formata_cpf(18404,0);

Validar CPF em SQL

2012-03-27T00:45:00.000-03:00

Como validar CPF em SQL Server, store function para validar CPF em T-SQL.

Esta store function, foi desenvolvida pensando no armazenamento do CPF em BIGINT, mas pode ser usada facilmente sem adaptações com CPF tipo VARCHAR(11) sem mascaras ou formatações.

Recomendável persistência do CPF em BIGINT por motivo principal de desempenho.

Segue function....




IF EXISTS (
            SELECT * 
              FROM sys.objects 
             WHERE object_id = OBJECT_ID(N'[dbo].[usf_valida_cpf]') 
               AND type IN (N'FN')
           )
 DROP FUNCTION dbo.usf_valida_cpf;
GO

CREATE FUNCTION dbo.usf_valida_cpf(@cpf varchar(max)) RETURNS bit 
AS
-- Nome Artefato/Programa..: usf_valida_cpf.sql
-- Autor(es)...............: Emerson Hermann (emersonhermann [at] gmail.com) O Peregrino (http://www.emersonhermann.blogspot.com) baseado em Script feito por:
-- ........................: Cristiano Martins Alves em http://www.devmedia.com.br
-- Data Inicio ............: 19/05/2011
-- Data Atualizacao........: 21/02/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 CPF
-- OBS.....................: A entrada é um varchar e o retorno é um bit, 1 para válido, 0 para inválido e null para nulos 
-- ........................: 
--  
BEGIN
  DECLARE @index   int,
          @sumx    int,
          @dig1    int,
          @dig2    int,
          @cpf_temp   varchar(11),
          @cpfx             varchar(11), 
          @dig_equal  bit,  --0 para negativa e 1 para afirmativa
          @r    bit

  -- Pre-validacao 1, se e nulo, entao retorna nulo
  IF @cpf IS NULL BEGIN
     SET @r = NULL
     RETURN (@r)  
  END --fim_se      

  -- Pre-validacao 2, se e maior que 11 digitos , entao retorna 0 
  IF LEN(@cpf) > 11 BEGIN
     SET @r = 0 
     RETURN (@r)
  END --fim_se
  
  
  -- Pre-validacao 3, se e tem alguma letra no cpf, entao retorna 0 
  IF (SELECT CASE WHEN patindex('%[^0-9]%', @cpf) > 0 THEN 1 ELSE 0 END) = 1 BEGIN
     SET @r = 0
     RETURN (@r)
  END --fim_se  
  
  -- Pre-validacao 4, se e menor que 11 digitos , pode ser oriundo de bigint, então fazer tratamento de zeros 
  SET @cpfx = @cpf
  IF LEN(@cpf) < 11 BEGIN
     SET @cpfx = REPLICATE('0',11-LEN(@cpf))+@cpf
  END --fim se
   
  /*
  -- Pre-validcao 5, se a sequencia tem numeros iguais, entao retorna 0 
     Verificando se os digitos sao iguais.
     A Principio CPF com todos os numeros iguais sao invalidos,
      apesar de validar o calculo do digito verificador
     EX: O CPF 00000000000 é inválido, mas pelo calculo seria valido
  */

  SET @cpf_temp = SUBSTRING(@cpfx,1,1)

  SET @index = 1
  SET @dig_equal = 1

  WHILE (@index <= 11) BEGIN
     IF SUBSTRING(@cpfx,@index,1) <> @cpf_temp BEGIN
        SET @dig_equal = 0
     END 
     SET @index = @index + 1
  END;

  --Caso os digitos nao sejam todos iguais, comeca o calculo dos digitos
  IF @dig_equal = 0 BEGIN

     --Calculo do 1º digito
     SET @sumx = 0
     SET @index = 1
     
     WHILE (@index <= 9) BEGIN   
        SET @sumx = @sumx + CONVERT(int,SUBSTRING(@cpfx,@index,1)) * (11 - @index)       
        SET @index = @index + 1      
     END

     SET @dig1 = 11 - (@sumx % 11)

     IF @dig1 > 9 BEGIN     
        SET @dig1 = 0       
     END 

     -- Calculo do 2º digito
     SET @sumx = 0
     SET @index = 1
     
     WHILE (@index <= 10) BEGIN   
        SET @sumx = @sumx + CONVERT(int,SUBSTRING(@cpfx,@index,1)) * (12 - @index)     
        SET @index = @index + 1 
     END  --fim_enquanto 

     SET @dig2 = 11 - (@sumx % 11)

     IF @dig2 > 9 BEGIN    
        SET @dig2 = 0     
     END 

     -- Validando
     IF (@dig1 = SUBSTRING(@cpfx,LEN(@cpfx)-1,1)) AND (@dig2 = SUBSTRING(@cpfx,LEN(@cpfx),1)) BEGIN
        SET @r = 1 
     END ELSE BEGIN  --senao   
       SET @r = 0       
     END --fim_se
     
  END ELSE BEGIN --senao 

     SET @r = 0  -- invalido
    
  END --fim_se
  
  RETURN (@r) 
  
END;
GO

-- chamada da function 

--SELECT dbo.usf_valida_cpf('80481604472');  -- retorna 1
-- ou 
--SELECT dbo.usf_valida_cpf(80481604472); -- retorna 1 
-- ou 
--SELECT dbo.usf_valida_cpf('05413714793'); -- retorna 1
-- ou 
--SELECT dbo.usf_valida_cpf(05413714793); -- retorna 1

Validando Cadastro Específico do INSS - CEI em SQL Server

2012-03-12T23:27:00.000-03:00

Validando Cadastro Específico do INSS - CEI em SQL Server, como validar CEI em SQL Server usando functions do TRANSACT SQL

-- Nome Artefato/Programa..: usf_valida_cei.sql
-- Autor(es)...............: Emerson Hermann (emersonhermann [at] gmail.com) O Peregrino (http://www.emersonhermann.blogspot.com) 
-- Data Inicio ............: 11/03/2012
-- Data Atualizacao........: 11/03/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 validar numero de CEI (Cadastro Especifico de INSS) 
-- OBS.....................: A entrada é um varchar e o retorno é um bit, 1 para válido, 0 para inválido e null para nulos 
-- ........................: 
--
IF EXISTS (
            SELECT * 
              FROM sys.objects 
             WHERE object_id = OBJECT_ID(N'[dbo].[usf_valida_cei]') 
               AND type IN (N'FN')
           )
 DROP FUNCTION usf_valida_cei;
GO
CREATE FUNCTION  usf_valida_cei(@number varchar(max)) RETURNS bit AS
/*
CEI - Cadastro Específico do INSS 
O algorítmo abaixo demonstra como obter o dígito verificador da matricula CEI.
Formato : EE.NNN.NNNNN/AD
Onde:
EE - Número
NNNNNNNN - Número
A - Atividade
D - Dígito Verificador
a) Multiplicar os últimos 11 algarismos pelos seus respectivos pesos, conforme abaixo:
Pesos: 7,4,1,8,5,2,1,6,3,7,4
Algarismos: EENNNNNNNNA

Cálculo
7 * E = X1
4 * E = X2
1 * N = X3
8 * N = X4
5 * N = X5
2 * N = X6
1 * N = X7
6 * N = X8
3 * N = X9
7 * N = X10
4 * A = X11
D (posição do dígito)


b) Somar todos os produtos obtidos no item "a"

Soma = X1+2+X3+X4+X5+X6+X7+X8+X9+X10+X11

c) Com o total obtido no item "b", somar o algarismo da unidade com o algarismo da dezena.

Total = Dezena de soma + Unidade de soma

d) Subtrair de 10 o algarismo da unidade do obtido no item "c".

Resultado = 10 - Unidade de Total

O algarismo da unidade do resultado da subtração será o dígito verificador.

Digito verificador = Unidade de Resultado
*/  

BEGIN


 DECLARE @weight   varchar (11)
 DECLARE @total   int
 DECLARE @c    int
 DECLARE @result   int
 DECLARE @r    bit
 DECLARE @number_dig  int
 DECLARE @number_dig_c int
 DECLARE @number_dig_x varchar(2) 

 SET @weight = '74185216374'  -- peso estabelecido para gerar CEI 
 SET @total = 0 
 SET @c  = 1  
 SET @number_dig = substring(@number,len(@number),1) -- ultimo digito extraido do parametro informado

 --fazendo um uma pre-validação do CEI, validação 1 se é nulo
 IF @number IS NULL BEGIN
  SET @r = NULL
  RETURN (@r) 
 END --fim_se   

 --fazendo um uma pre-validação do CEI, validação 2 se o tamanho é diferente de 12 digitos 
 IF LEN(@number) > 12  BEGIN
  SET @r = 0
  RETURN (@r)
 END  --fim_se

 --fazendo um uma pre-validação do CEI, validação 3 se não for nulo e for mair que 12,
 -- ainda pode ser uma string 
 IF (SELECT CASE WHEN patindex('%[^0-9]%', @number) > 0 THEN 1 ELSE 0 END) = 1 BEGIN
  SET @r = 0
  RETURN (@r)
 END --fim_se   

 --fazendo um uma pre-validação do CEI, validação 4 se está vindo c/ 12 dig zerados
 IF CAST(@number AS bigint)=0 BEGIN
  SET @r = 0
  RETURN (@r)
 END  --fim_se  


 WHILE (@c<=11) BEGIN
  SET @result = cast (substring(@number, @c, 1) AS int) * cast(substring(@weight,@c, 1) AS int) 
  SET @total  = @total + @result
  SET @c = @c + 1 
 END -- fim_enquanto, neste caso, while simulando for 

 SET @number_dig_x = right(left(cast (@total as varchar(max)), 12), 2)
 SET @number_dig_c = (cast(left(@number_dig_x, 1) as int)) + (cast(right(@number_dig_x, 1) as int))
 SET @number_dig_c = 10 - (cast (Right(Left(@number_dig_c, 12), 1) as int))


 IF (@number_dig_c > 9) BEGIN
  SET @number_dig_c = 0
 END ELSE BEGIN 
  SET @number_dig_c = 10 - @number_dig_c
 END 

 IF (@number_dig <> @number_dig_c) BEGIN 
  SET @r = 0
 END ELSE BEGIN
  SET @r = 1 
 END 
    
 RETURN (@r)

END;
GO
-- Exemplo de chamada da function 
-- SELECT dbo.usf_valida_cei('741852163748');

Função para validar NIT PIS PASEP em MS SQL Server 2005 / 2008

2012-02-20T02:03:00.000-03:00

Como não encontrei nada na net, para validar NIT PIS PASEP em MS SQL Server 2005 / 2008, e diante da necessidade, verificando o algoritmo para validação do NIT PIS PASEP, resolvi desenvolver uma função em T-SQL para validar esse troço.

Na verdade só testei com o MS SQL Server 2008, não tive tempo de testar no 2005, mas acho que deva funcionar em ambas. Fica por conta e risco nas dua versões.

O moído é o seguinte:

-- Nome Artefato/Programa..: usf_valida_nit_pis_pasep.sql
-- Autor(es)...............: Emerson Hermann (emersonhermann [at] gmail.com) O Peregrino (http://www.emersonhermann.blogspot.com) 
-- Data Inicio ............: 19/02/2012
-- Data Atualizacao........: 21/02/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 numero de NIT PIS PASEP
-- OBS.....................: A entrada é um varchar e o retorno é um bit, 1 para válido, 0 para inválido e null para nulos 
-- ........................: 
--
IF EXISTS (
            SELECT * 
              FROM sys.objects 
             WHERE object_id = OBJECT_ID(N'[dbo].[usf_valida_nit_pis_pasep]') 
               AND type IN (N'FN')
           )
 DROP FUNCTION usf_valida_nit_pis_pasep;
GO

CREATE FUNCTION usf_valida_nit_pis_pasep(@number varchar(max)) RETURNS bit AS
BEGIN
 DECLARE @weight  varchar (10)
 DECLARE @total  int
 DECLARE @c   int
 DECLARE @rest       int
 DECLARE @result  int
 DECLARE @r   bit 
 /*
 FORMATO : NNNNNNNNNND 
 ONDE:
 NNNNNNNNN - Numero do Identificador
 D   - Digito Verificador

 a) Multiplicar os 11 ultimos algarismos pelos pesos conforme descrito abaixo:
 PESOS:  3, 2, 9, 8, 7, 6, 5, 4, 3, 2
 ALGORISMO: NNNNNNNNN  
 CALCULO:  
 3 * N = X1
 2 * N = X2
 9 * N = X3
 8 * N = X4
 7 * N = X5
 6 * N = X6
 5 * N = X7
 4 * N = X8
 3 * N = X9
 2 * N = X10
 D (posicao do digito)
 */

 SET @weight = '3298765432'  -- peso estabelecido para gerar NIT PIS PASEP 
 SET @total = 0 
 SET @c  = 1 
    
 --fazendo um uma pre-validação do NIT PIS PASEP, validação 1 se é nulo
 IF @number IS NULL BEGIN
     SET @r = NULL
     RETURN (@r)  
 END 

 --fazendo um uma pre-validação do PIS PASEP, validação 2 se o tamanho é diferente de 11 digitos 
 IF LEN(@number) <> 11  BEGIN
     SET @r = 0
     RETURN (@r)
 END  --fim_se
    
 --fazendo um uma pre-validação do PIS PASEP, validação 3 se não for nulo e for mair que 11, ainda pode ser uma string 
 IF (SELECT CASE WHEN patindex('%[^0-9]%', @number) > 0 THEN 1 ELSE 0 END) = 1 BEGIN 
     SET @r = 0
     RETURN (@r)
 END --fim_se 
    
 --fazendo um uma pre-validação do PIS PASEP, validação 4 se está vindo c/ 11 dig zerados
 IF CAST(@number AS bigint)=0 BEGIN
     SET @r = 0
     RETURN (@r)
 END  --fim_se
    
 /*
 b) Somar todos os produtos obtidos no item "a". 
 SOMA = X1 + X2 + X3 + X4 + X5 + X6 + X7 + X8 + X9 + X10
 */

 WHILE (@c<=10) BEGIN
     SET @result = CAST (SUBSTRING(@number, @c, 1) AS INT) * CAST(SUBSTRING(@weight,@c, 1) AS INT) 
     SET @total  = @total + @result
     SET @c = @c + 1 
 END -- fim_enquanto, neste caso, while simulando for 
 
 /*
 c) Dividir o somatório do item "b" por 11.
 DIVISAO = SOMA / 11    
 */ 
 -- dividindo, não é necessário, já vai ser feito direto no resto da divisao, apenas para acompanhar o algoritmo 
 SET @result = @total / 11 
 -- calculando resto da divisao
 SET @rest = @total % 11
 
 /*
 d) Subtrair de 11 o resto da divisao do item "c".
 RESULTADO = 11 - RESTO_DA_DIVISAO
 
 O resultado sera o digito verificador. 
 Caso o resultado da subtracao seja 10 ou 11, o digito verificador sera 0.    
 */

 SET @rest = 11 - @rest

 IF @rest = 10 OR @rest = 11 BEGIN
     SET @rest = 0 
 END --fim_se
  
 IF (@rest <> CAST (SUBSTRING(@number, 11, 1) AS INT)) BEGIN
     SET @r = 0  -- numero invalido
 END ELSE BEGIN
     SET @r = 1  -- numero valido 
 END --fim_se
    
 RETURN (@r)
    
END; 
GO

-- Fazendo chamada da function, exemplo:

SELECT dbo.usf_valida_nit_pis_pasep('12511508690'); -- retorna 1 pois é valido 
GO

-- Checando na tabela colaborador todos os NITs inválidos no campo nit, exemplo:

SELECT * 
  FROM colaborador 
 WHERE dbo.usf_valida_nit_pis_pasep(nit)=1;
GO

-- Pode se ainda implementar direto na tabela colaborador para validar o campo nit, por exemplo:

ALTER TABLE colaborador
  ADD CONSTRAINT ck_colaborador_usf_valida_nit_pis_pasep 
CHECK (dbo.usf_valida_nit_pis_pasep(nit)=1); 

GO

Mais uma vez, espero ter ajudado!
O Peregrino.

Configurando SyntaxHighlighter no Blogspot, ou usando cores para sintaxe de linguagens e scripts no seu Blogger

2011-12-02T00:49:00.018-03:00

Configurando SyntaxHighlighter no Blogspot, ou usando cores para sintaxe de linguagens e scripts no seu Blogger.

Uma das coisas legais para quem é blogueiro principalmente com o Blogger da Google é você usar scripts com cores dos
comandos destacados, principamente linguagens e scripts.

Esse artigo se propõe a ensinar a dica de como configurar isso no Blogger (Blogspot) da Google, para tal usamos um artefato, framework, chamado
SyntaxHighlighter em http://alexgorbatchev.com/SyntaxHighlighter/ desenvolvido em JavaScript por Alex Gorbatchev

Aqui vai o passo a passo para configurar o SyntaxHighlighter no seu blog da Blogger.

No seu Blogspot ou Blogger da Google, acesse a guia [Design] clicar no item [Editar HTML]

Por segurança clicar no item [Baixar modelo completo] antes de fazer qualquer alteração no modelo.

Já com uma cópia do seu template feito, acesse o campo [Editar Modelo], e encontre o texto </head> tag de fechamento.
Antes dessa tag, adicione o seguinte:

<!-- inicio configuracao do Syntax Highlighting -->
<link href='http://alexgorbatchev.com/pub/sh/current/styles/shCore.css' rel='stylesheet' type='text/css'/>
<!-- configuracao do tema, css usado, default, branco, inicio -->
<link href='http://alexgorbatchev.com/pub/sh/current/styles/shThemeDefault.css' rel='stylesheet' type='text/css'/>
<!-- configuracao do tema, css usado, default, branco, fim -->
<script src='http://alexgorbatchev.com/pub/sh/current/scripts/shCore.js' type='text/javascript'/>

<!-- adicionando Script para SyntaxHighlighter --> 
<script type='text/javascript'>
  SyntaxHighlighter.config.bloggerMode = true;
  SyntaxHighlighter.all();
</script>

<!-- configuracao de linguagens usadas, inicio -->
<script src='http://alexgorbatchev.com/pub/sh/current/scripts/shBrushJScript.js' type='text/javascript'/>
<script src='http://alexgorbatchev.com/pub/sh/current/scripts/shBrushBash.js' type='text/javascript'/>
<script src='http://alexgorbatchev.com/pub/sh/current/scripts/shBrushSql.js' type='text/javascript'/>
<script src='http://alexgorbatchev.com/pub/sh/current/scripts/shBrushXml.js' type='text/javascript'/>
<script src='http://alexgorbatchev.com/pub/sh/current/scripts/shBrushCpp.js' type='text/javascript'/>
<script src='http://alexgorbatchev.com/pub/sh/current/scripts/shBrushJava.js' type='text/javascript'/>
<script src='http://alexgorbatchev.com/pub/sh/current/scripts/shBrushPhp.js' type='text/javascript'/>
<script src='http://alexgorbatchev.com/pub/sh/current/scripts/shBrushPerl.js' type='text/javascript'/>
<script src='http://alexgorbatchev.com/pub/sh/current/scripts/shBrushDelphi.js' type='text/javascript'/>
<script src='http://alexgorbatchev.com/pub/sh/current/scripts/shBrushPlain.js' type='text/javascript'/>
<script src='http://alexgorbatchev.com/pub/sh/current/scripts/shBrushDiff.js' type='text/javascript'/>
<script src='http://alexgorbatchev.com/pub/sh/current/scripts/shBrushVb.js' type='text/javascript'/>
<!-- configuracao de linguagens usadas, fim -->

<!-- fim configuracao do Syntax Highlighting -->

Um breve comentário do que foi adicionado ao modelo original ...

A partir do comentário []
Nesse caso é o tema padrão (default) branco, mas tem vários temas a ser visto no site do SyntaxHighlighter em http://alexgorbatchev.com/SyntaxHighlighter/manual/themes/
No meu blog eu o uso um tema cinza preto, estilo console do linux, no qual seria este:
<link href='http://alexgorbatchev.com/pub/sh/current/styles/shThemeFadeToGrey.css'/>

A partir do comentário []
Adicione as linguagens que você planeja usar, no seu blog.

Por exemplo, estou habilitando as seguintes linguagens ou scripts (brushes ou escovas):
JavaScript, Bash, SQL, XML/HTML, C++, Java, PHP, Perl, Delphi, Plain, Diff, Vb.

Salve o seu modelo modificado, clicando em [SALVAR MODELO].

Agora você está habilitado para adicionar destaque de sintaxe para o seu blog.
Envolva seu código em uma tag <pre> e especifique a classe brush, utilizando o atributo de classe a linguagem ou script.
Por exemplo, para destacar um bloco de SQL:

<pre class="brush:sql">

SELECT *
FROM usuario
WHERE id_usuario = 777;

</pre>

Veja como aparece, quando formatado usando o SyntaxHighlighter:

SELECT * 
   FROM usuario
  WHERE id_usuario = 777;

O código que você está formatando deve ser HTML, já que o framework foi desenvolvido em JavaScript.

Espero ter ajudado, APSJ!