Pesquisar este blog

domingo, 28 de novembro de 2010

Como tratar valores DATA (datetime) no SQL SERVER

Este artigo se aplica a qualquer versão do Microsoft SQL Server a partir da versão 2000.
TAGS: #DATEADD, #DATEPART, #GETDATE, #BISSEXTO, #SQL, #SQLSERVER

O SQL possui inúmeras ferramentas para o tratamento de datas. Aqui vou apresentar algumas dicas que considero as mais relevantes, que poderão ajudá-lo no desenvolvimento de suas querys.

CALCULAR DATA INICIAL E FINAL A PARTIR DO MÊS/ANO

Você está programando um código que utiliza uma procedure que já existe no banco de dados. O parâmetro para o cálculo que você possui é o mês e o ano, porém esta procedure recebe o parametro @data_inicial e @data_final. Como você faz para chamar este procedimento, se você só tem o mês e o ano?

Bom, o macete que vou apresentar aqui é muito simples. Vamos utilizar a função DATEADD. A função DATEADD permite adicionar ou subtrair valores específicos de uma data. Então, para começar, montamos a data inicial:

select @dt_inicial = rtrim(@ano) + '-' + right('00' + rtrim(@mes),2) + '-01'

Nesta query, estamos concatenando o ano, o mês e o dia 01 fixo para o @mes e @ano que temos como parâmetro. O código right(‘00’ + rtrim(@mes,2)  serve para sempre retornar o mês com dois dígitos (01, 05, 11, 12). O formato da data que devemos montar é YYYY-MM-DD.

Após calcularmos a data incial, vamos utilizar a função DATEADD duas vezes. Vamos somar um mês e subtrair um dia, nesta sequencia, para descobrirmos o último dia do mês:

select @dt_final = dateadd(day, -1, dateadd(month, 1, @dt_inicial))

A query dateadd(month, 1, @dt_inicial) retorna a data inicial com 1 mês a mais, e a função dateadd(day, –1, … ) pega este valor e subtrai um dia, desta forma teremos o último dia do @mes e @ano.

Segue abaixo o código completo para que possa copiar:


Code Snippet
  1. --
  2.  
  3. -- @mes = 3, @ano = 2010
  4. declare @mes smallint
  5. declare @ano smallint
  6. declare @dt_inicial smalldatetime
  7. declare @dt_final smalldatetime
  8.  
  9. select @mes = 2, @ano = 2012
  10.  
  11. select @dt_inicial = rtrim(@ano) + '-'
  12.                    + right('00' + rtrim(@mes),2) + '-01'
  13.  
  14. select @dt_final = dateadd(day, -1, dateadd(month, 1, @dt_inicial))
  15.  
  16. select @dt_inicial as 'Data inicial', @dt_final as 'Data final'
  17.  
  18.                     
  19. --


 image

 


CÁLCULO DE ANO BISSEXTO

Já vi programadores fazerem cada código bizarro para descobrir se o ano é bissexto. Primeiro, que eu realmente não vejo utilidade em saber se o ano é bissexto ou não. Ah, é para definir uma variável data com o último dia do mês, mas não sabe se termina com 28, 29, 30 ou 31 dias? Esqueça isso, utilize a dica que mostrei acima. Mas de qualquer forma, o truque para calcular se um ano é bissexto é que o resultado da divisão do ano por 4 (quatro) não pode ser maior que zero. No SQL, para se descobrir o resto de uma divisão, utilizamos o operador MOD (%). Veja a query abaixo:

image

Se o cálculo @ANO % 4 = 0, significa que o ano é bissexto, assim como no exemplo acima (ano 2012).

UTILIZE A FUNÇÃO DATEPART e CONVERT PARA OBTER QUALQUER VALOR DE UMA DATA ESPECÍFICA

Existem casos em que precisamos extrair o ano de uma data específica, ou hora e minutos, e assim por diante. Vou apresentar alguns exemplos úteis de utilização da função DATEPART e CONVERT.

1. Pegando valores individuais de uma data (dia, mes, ano, hora, minutos e segundos) –


Code Snippet
  1. --
  2.  
  3. declare @dia smallint, @mes smallint, @ano smallint
  4.       , @hr  smallint, @min smallint, @seg smallint
  5.  
  6. select @dia = datepart(day, getdate())
  7.      , @mes = datepart(month, getdate())
  8.      , @ano = datepart(year, getdate())
  9.      , @hr  = datepart(hour, getdate())
  10.      , @min = datepart(minute, getdate())
  11.      , @seg = datepart(second, getdate())
  12.  
  13. select getdate() as 'Getdate() original'
  14.      , @dia as 'Dia'
  15.      , @mes as 'Mes'
  16.      , @ano as 'Ano'
  17.      , @hr  as 'Hora'
  18.      , @min as 'Minuto'
  19.      , @seg as 'Segundo'
  20.  
  21.                     
  22. --



* A função GETDATE() retorna a data e hora atual do servidor SQL.

image 

2. Os tipos de CONVERT mais comuns com datas (#SQL = formato de data padrão do SQL YYYY-MM-DD).


Code Snippet
  1. --
  2.  
  3. select getdate() as 'Getdate() original'
  4.      , convert(char(10), getdate(), 120) '#SQL'
  5.      , convert(char(10), getdate(), 103) 'PT-BR'
  6.      , convert(char(10), getdate(), 101) 'US-EN'
  7.      , convert(char(8), getdate(), 14) 'TEMPO-FULL'
  8.      , convert(char(5), getdate(), 14) 'TEMPO-SHORT'
  9.  
  10. --



image 

3D GEARS GEAR7

sexta-feira, 26 de novembro de 2010

sysobjects, sys.objects, syscolumns e syscomments

Utilize as tabelas de sistema do SQL Server para turbinar seu trabalho

As tabelas de sistema contém informações sobre a estrutura do seu banco de dados. Estas estruturas permitem a criação de dinâmicas de código que facilitam o dia-a-dia do programador SQL. Todas estão disponíveis a partir do SQL 2000 (com excessão da tabela SYS.OBJECTS, disponível a partir da versão 2005). Vamos conhecer as tabelas.


SYSOBJECTS
Esta tabela armazena todos os objetos do banco de dados, tais como tables, procedures, triggers, views, functions, foreign keys, constraints, entre outros. Resumindo, todos os objetos de usuário estão nesta tabela. As principais colunas da tabela são:

    • Name: nome do objeto no banco de dados
    • crdate: data de criação do objeto
    • type: tipo do objeto.

Alguns exemplos de aplicação:

Code Snippet
  1. --
  2.  
  3. select name, crdate, type
  4.   from sysobjects
  5. where name like 'spcFAE%'
  6. order by crdate desc
  7. --

Retorna o nome, data de criação e tipo dos objetos que o nome comece com “spcFAE”, ordenando por data de criação descescente.

Code Snippet
  1. --
  2.  
  3. select name, crdate, type
  4.   from sysobjects
  5. where crdate > '2010-05-01'
  6. order by crdate desc
  7.   
  8. --

Retorna todos os objetos criados no banco de dados a partir de 01 de maio de 2010.

Code Snippet
  1. --
  2.  
  3. select name
  4.   from sysobjects
  5. where type in ('P','TR','FN')
  6. order by crdate desc
  7.   
  8. --

Retorna o nome dos procedimentos (P), triggers (TR) e functions (FN).


Estas pesquisas na tabela SYSOBJECTS são muito úteis, por exemplo, para buscar todos os procedimentos criados a partir de uma determinada data, ou relacionar rotinas por nome, etc. Existe ainda (somente a partir da versão 2005 do SQL), a tabela SYS.OBJECTS, que funciona como uma extensão da tabela SYSOBJECTS (relaciona pela coluna ID), porém possui uma coluna que é muito útil para desenvolvedores, a coluna MODIFY_DATE. Esta coluna armazena a data da última alteração de um objeto.

Imagine a seguinte situação, você trabalha 5 dias corrigindo em uma base teste dezenas de procedimentos. Você não precisa mais ficar anotando qual rotina alterou para criar o script no final, basta buscar da tabela sys.objects os objetos onde o modify_date seja maior ou igual ao dia que começou a trabalhar… bacana não é?

Os tipos conhecidos mais comuns de objetos na tabela (coluna type) são:

  • U  - TABLE (tabela)
  • F  - FOREIGN KEY (chaves estrangeiras)
  • C  - CHECK CONSTRAINT
  • D  - DEFAULT
  • P  - STORED PROCEDURE
  • V  - VIEW
  • TR – TRIGGER
  • TF - TABLE FUNCTION
  • FN - USER DEFINED FUNCTION
  • S  - SYSTEM TABLE
  • K  - PRIMARY KEY (chave primária)


A tabela sysobjects também pode ser utilizada em conjunto com outras tabelas de sistema, para outros tipos de pesquisa no banco de dados. Vamos conhecer agora a tabela SYSCOLUMNS.


SYSCOLUMNS

Esta tabela armazena armazena as colunas de tabelas, parametros de entrada de procedimentos, e assim por diante. Toda vez que é criada uma nova tabela ou procedimento no sistema, o SQL grava nesta tabela informações preciosas, que podemos utilizar a qualquer momento. As colunas que mais vamos utilizar neste exemplo são:

ID é a identificação do objeto a quem pertence estas colunas [ syscolumns.id = sysobjects.id ]
xtype determina o tipo de coluna (inteiro, decimal, char, varchar, bit, etc). O tipo é um código, encontrado em systypes [ syscolumns.xtype = systypes.xtype ]
length tamanho do campo (no caso char, varchar terá o tamanho específico).
xprec & xscale especifico para tipos decimais, informando a quantidade total de dígitos com decimais (xprec), e a quantidade de decimais (xscale)
allownulls valor bit (0/1) que informa se a coluna aceita valores nulos ou não.
collation Especifica o COLLATION da coluna (para tipos texto). Isto é necessário para solucionar problemas de conflitos de bancos de dados de diferentes idiomas.


A partir desta tabela, é possível ler qualquer estrutura do banco de dados. Qual a utilidade disso? Como uma pequena demonstração do que podemos fazer com a tabela SYSCOLUMNS, vamos criar um procedimento de apoio (?). Bom, um procedimento de apoio é um código feito para gerar código. É isto mesmo!

Vamos exemplificar.

Temos uma tabela chamada NotaItemLote (tabela onde são gravados os números de lote dos itens de uma nota fiscal). Você está criando uma rotina que irá inserir dados nesta tabela (gerando uma nota fiscal). Conhecendo a dinâmica de bancos de dados, quando construo uma query de insert, eu sempre especifico o nome dos campos da tabela que estou inserindo dados:


 

Code Snippet
  1. --
  2.  
  3. insert into tabela (coluna1, coluna2, coluna3) select 1, 2, 3
  4.   
  5. --


Isto garante que no caso de uma eventual atualização no sistema, se forem adicionadas colunas novas nesta tabela que aceitem nulo, minha rotina ficará estável e não dará erros. Se você não especifica o nome das colunas no insert, ao alterar a estrutura da tabela, todas as rotinas que geram insert sem especificar o nome darão erro. Isto é padronização básica da básica.

Pois bem, como vamos gerar um insert então? Muito simples! Altere o nome da tabela para uma que exista no seu banco de dados, e veja o resultado do select abaixo (utilizando syscolumns)

Code Snippet
  1. --
  2.  
  3. select syscolumns.name + ', '
  4.   from syscolumns, sysobjects
  5. where syscolumns.id = sysobjects.id
  6.    and sysobjects.name = 'NotaItemLote'
  7. order by syscolumns.colorder
  8.   
  9. --



image

A query irá retornar o nome das colunas da tabela, em sequência, e ainda colocando uma virgula ao nome do lado, para facilitar. Aí basta copiar, colar e montar a sua query de insert

Code Snippet
  1. --
  2.  
  3. insert into NotaItemLote (cd_empresa, nr_serie, nr_nota, cd_item
  4.                         , nr_lote, dt_validade, qt_lote, sinal) ...
  5.   
  6. --


Então vamos melhorar mais ainda, e facilitar a utilização deste procedimento, pois não teria sentido digitar uma query como essa toda vez que quisesse pegar as colunas de uma tabela. Então vamos criar um procedimento chamado sp_gerainsert. Segue o código dele abaixo:

Code Snippet
  1. --
  2.  
  3. create proc sp_gerainsert
  4.   @objname as sysname
  5. as
  6.  
  7.   select name + ', '
  8.     from syscolumns
  9.    where id = object_id(@objname)
  10.    order by colorder
  11.      
  12. --

*A função object_id retorna o ID do objeto passado como parâmetro (o mesmo ID da tabela sysobjects).

Após compilar o procedimento, vá até o menu TOOLS (ferramentas), OPTIONS (opções). Selecione KEYBOARD, e insira a função que criamos no atalho CTRL+5 (pode vincular em qualquer atalho que desejar):

image
  
Agora, toda vez que for criar uma query de insert em uma tabela, basta selecionar o nome da tabela na janela de query, e pressionar CTRL+5 (ou o atalho que tenha especificado):

image


SYSCOMMENTS
A tabela syscomments é onde ficam armazenados todos os textos de procedimentos, triggers, funções, etc. Quando você executa um CREATE PROCEDURE, por exemplo, é nesta tabela que fica armazenado todo o código da procedure que compilou.

Vamos então para a parte prática. Digamos que você esteja fazendo uma atualização no seu banco de dados. Uma tabela de uso frequente recebeu uma nova coluna, e esta coluna não aceita valores nulos. Isto significa que você precisa revisar todos os procedimentos que inserem dados nesta tabela, correto? Para entender a forma como vamos procurar isso na tabela syscomments, vamos primeiro analisar uma query de insert:

Code Snippet
  1. --
  2.  
  3. insert into NotaItemLote (cd_empresa, nr_serie, nr_nota, nr_lote
  4.                         , dt_validade, qt_lote, sinal)
  5.   select 1, 'NFF', 1525, 'A0001', '2010-12-31', 10.0 , 'E'
  6.      
  7. --


 

Muito bem, o que precisamos fazer então, é localizar todos os procedimentos, triggers ou funções que contenham o seguinte texto: “insert into NotaItemLote”. Vamos então montar a query de busca na syscomments da seguinte forma:

Code Snippet
  1. --
  2.  
  3. select name
  4.   from sysobjects
  5. where id in (select name
  6.                 from syscomments
  7.                where text like '%into notaitemlote%')
  8.                     
  9. --


O resultado da query é o nome de todos os procedimentos que contenham este texto “into notaitemlote”. Quem está acostumado com atualizações em bancos de dados que contém procedimentos, conhece bem esta rotina.

image
 

Estas dicas, aplicadas no dia-a-dia, garantem muito mais produtividade ao programador, pois não precisa ficar digitando nome de colunas, localiza os objetos alterados no sistema, etc. É muito mais controle sobre os objetos para o programador. E pode ter certeza, utilizar estes indicadores (como data de criação, data de alteração da rotina, etc) garantem muito mais precisão do que documentação manual.

HAPPY DESTROY

quarta-feira, 24 de novembro de 2010

Você já está coletando?

Soluções portáteis de baixo custo agregam alto valor operacional.

Gostaria de compartilhar um de meus projetos recentemente implementado. O case é da empresa Inovadoor Portas Industrias Ltda, localizada em Curitiba/PR. O objetivo do projeto era melhorar a operação do almoxarifado e da produção no sistema. O usuário perdia muito tempo sentando na frente do computador para pagar cada uma das dezenas de requisições que recebia diariamente.

Diante de centenas de diferentes itens, diferentes códigos e embalagens, o procedimento de controle de estoque também era complexo, exigia um profissional especializado para manter as informações em dia.

Nosso projeto foi dividido em duas partes importantes:

 

Identificação do estoque

argox

O primeiro passo foi adquirir uma impressora térmica para emissão de etiquetas.

A impressora de etiquetas (modelo utilizado: Argox OS-214 Plus) funciona através de transferência térmica para produção de etiquetas com textos, logotipos e código de barras. Uma solução ideal de baixo custo para pequenas e médias tiragens, utilizando mínimo espaço. Possui um sistema completo de gerenciamento de memória que permite o armazenamento controlado de formatos de etiquetas, gráficos e fontes. Além disso, tem incorporado uma poderosa linguagem de comandos para geração e controle dos formatos (PPLA).

Utilizando Visual Studio 2008, desenvolvi uma aplicação em Visual Basic que busca do banco de dados (no caso MS SQL Server) os itens, descrição, unidade, entre outras informações, para serem estampadas nas etiquetas. Desta forma, o almoxarifado foi totalmente identificado. O código de barras escolhido foi CODE 39, pois os códigos de itens são alfanuméricos (00.000.000).

Custo aproximado: R$ 800,00 impressora + ribbon + etiqueta (preferencialmente com customização de logo)

 

14042010227 14042010228

 

Coletor de dados Argox PT-60 image

Buscando uma solução de baixo custo, porém que atendesse a todas as necessidades do projeto, chegamos ao Argox PT-60.

O coletor de dados PT-60 possui o sistema operacional Windows CE 5.0. Suas características são er gonomia, display colorido de 2.4 polegadas TFT QVGA 320 x 240 display com touch screen, alto desempenho para leitura de código de barras (leitor CCD), comunicação sem fio tais como Bluetooth e a tecnologia 802.11 b/g, interface USB e RS-232(serial). Possui slot para cartão SD para expansão de memória.Leve, pequeno e resistente, possui como opcional o pistol grip, para maior facilidade de manuseio.

Custo aproximado: R$ 2.900,00 (com pistol grip + bateria auxiliar)

Características
Potência
Bateria: recarregável padrão 3.7V 1950mAh Li-ion - recarregável com Pistol Grip: 3.7V 4400mAh Li-ion
Autonomia: 8 horas com bateria padrão e 12 horas com bateria do Pistol Grip.
Retenção de Dados: Após removida a bateria, os dados são armazenados por 15 minutos.
Dimensões
Medidas: 169 mm (L) x 70.1 mm (W) x 34 mm (H)
Peso: 250g (com baterias)
Especificações Gerais
Velocidade de Leitura: 400 scanners/segundos
Distância de Leitura: 600mm (code 39, 20 mils, PCS 90%)
Sistema de Leitura: 2048 scanners pixels
CPU: ARM9 Core 266MHz
Memória: 128Mb de Flash ROM, 128Mb DDRAM
Tela: 2.4 polegadas LCD TFT, 320 x 240 QVGA com touch screen
Teclado de Borracha: 29 Teclas (Numérico)
Ângulo do Leitor: 0° a 45° graus
Alarmes
Indicador por Beep: Programável nas freqüências de 1KHz a 4KHz
Indicador com LED: Azul, verde e vermelho
Fonte de Luz: Visível operando em 670±15 nm
Ambiente
Temperatura de Operação: -10ºC a 60º C
Temperatura de Armazenagem: -20º C a 70º C
Indice de Proteção: IP54 (imunidade contra poeira e contra respingos, projeções d´água e jatos leve d´água)
Umidade: 5% a 95% (sem condensação)
Resistência a Queda: 1.20 m de queda livre sobre concreto
Regulamentação de Emissão Eletromagnética: FCC, CE, BSMI, CCC
Comunicação
Interface RS-232: Taxa de até 115.2k bps
Interface IrDA: Compatível com Standard 1.0, até 115.2k bps - High Speed IR Taxa de transmissão até 115.2k bps
Programação
Linguagem: embedded Visual C++ 4.0, Visual Studio.NET, Visual Studio 2005, Windows CE SDK Scanner Configuration Utility
Acessórios
Inclusos: Bateria 1950mAh, Cabo Carga & Comunicação USB, Fonte de Alimentação, Caneta Stylus, Alça de mão
Garantia 12 Meses 

*fonte www.hardstand.com.br (http://www.hardstand.com.br/coletor-de-dados-argox-pt60.htm)

  FOTOS DO EQUIPAMENTO

24112010217 24112010220 24112010221 24112010216  

 

imagePara a transmissão dos dados, foi adquirido um aparelho ROTEADOR WIRELESS DLINK DI-524 e instalada uma antena mais potente, de 12dbi, localizada bem na entrada do almoxarifado.O sinal de transmissão em todos os locais do almoxarifado é de sempre 80% a 100%.

Custo aproximado: R$ 100,00 roteador + R$ 50,00 antena.

 

 

Software do coletor

24112010222
24112010225
24112010223

Através do Microsoft Visual Studio 2008, desenvolvi o aplicativo para o coletor, contendo as seguintes operações:

Consumo
Permite ao almoxarife dar baixa do estoque. Ao clicar a tecla 1 (ou pressionar a opção na tela touch screen), basta ler o código de barras do item, e informar a quantidade. A movimentação do estoque é realizada no banco de dados do sistema.

Requisição
Funciona exatamente como o apontamento de consumo, porém solicita o número da requisição de materiais no começo para vincular as baixas à respectiva ordem de produção.

Ajuste
Ferramenta que permite ao almoxarife realizar ajustes no estoque, como inventário. Com o coletor em mãos, realiza a contagem do material e já faz a entrada do saldo, utilizando o código de barras.

Compra
Permite ao almoxarife solicitar a compra de qualquer material utilizando apenas o código de barras da etiqueta.

Consulta
Exibe na tela relatório de razão de estoque analítico do item, assim como saldos em depósitos alternativos, afim de analisar a disponibilidade do material.

Através destes 5 recursos, o almoxarifado é capaz de processar qualquer operação no sistema de gestão sem precisar se deslocar até o computador, assim como em qualquer lugar da fábrica pode consultar saldos, gerar pedido de compra, etc. O ganho operacional é gigantesco.

Valor aproximado do projeto

Impressora térmica

+/- R$ 800,00

Ribbon e etiquetas (50 rolos)

+/- R$ 200,00

Coletor de dados Argox PT-60

+/- R$ 2.900,00

Roteador D-Link DI-524

+/- R$ 100,00

Antena 12dbi

+/- R$ 50,00

Total

+/- R$ 4.050,00

O projeto teve aproximadamente 30 dias de duração, e já está em pleno funcionamento. Agora estamos iniciando o trabalho de expansão, através da aquisição de mais equipamentos para redundância, e adição de novas funções, como controle de expedição, apontamentos da produção, etc.

Interpretando código SQL de forma eficiente (segmentada)

imagePara este artigo, estou utilizando o Microsoft SQL Server 2008, porém se aplica a qualquer gerenciador de banco de dados.

Em muitos treinamentos que prestei, ou até mesmo acompanhando outros profissionais que ainda estão em estágio de aprendizado, percebo uma enorme dificuldade em conseguir enxergar realmente o que são todas aquelas pernas dentro de um único select. É left join pra cá, distinct pra lá, group em cima, e order em baixo. Existem select’s que chegam a ultrapassar páginas de código, para uma única consulta. E isso é muito comum, pois uma programação limpa no SQL consiste em não utilizar cursores, loops, tabelas temporárias, usando somente em casos muito específicos. O ideal é reduzir o custo das rotinas, reduzindo o número de operações no banco de dados.

O exemplo que vamos estudar nesta publicação é um trecho de uma Stored Procedure responsável por gerar o faturamento a partir de uma ordem de serviço (ADOOS). Este faturamento gera nota fiscal, títulos a receber, entre outras informações. De forma a minimizar o custo total da query, uni em um único select todos os dados necessários para executar este faturamento. Esta união resultou em um select que busca ao mesmo tempo, dados de 12 diferentes tabelas. O que vamos entender, é como fazer a interpretação de uma query extensa, utilizando a técnica de segmentação do código.

Vamos dar uma olhada no trecho do código:

image

Esta é uma query de nível fácil a intermediário, pois todas as tabelas possuem relacionamento um para um. Veja que utilizei apelidos para as tabelas (cadastro – e, cidade – f, cidade – g), repeti as tabelas CADASTRO e CIDADE pois preciso dos dados de cadastro e estado tanto do cliente quanto da empresa.

 

Se para você, esta query é assustadora, então você está no post certo!

 

Em primeiro lugar, e não sei por qual razão, a maioria dos programadores iniciantes NÃO LÊEM. É isso mesmo, não lêem o que está escrito na tela. Uma query exige uma leitura muito mais minuciosa que um texto, pois o SQL tem que ser exato, todos os laços devem ser fechados para que a query rode corretamente. É matemática, teoria dos conjuntos.

O primeiro passo, é identificar cada tabela, os dados que serão extraídos dela e qual o seu relacionamento com as demais.

image

ADOOS – Quando esta rotina é chamada, recebe como parâmetro o número da ordem de serviço. A tabela ADOOS é onde ficam armazenadas todas as ordens de serviço, contendo data de abertura, cliente, tipo de ordem de serviço, etc. A chave primária desta tabela é EMPRESA, SÉRIE E NR_OS.

ADOOSOutra – Tabela auxiliar à tabela principal, traz informações adicionais como programação de entrega, plano gerencial, centro de custo, etc. O seu relacionamento é de 1x1 (um registro em ADOOS para um registro em ADOOSOutra). Mesma chave primária que ADOOS.

ADOOSEncerra – Assim como no exemplo acima, esta tabela também é 1x1, tendo a mesma chave primária que a ADOOS.

Cadastro (d) – contém os dados de cadastro do cliente, pois na tabela ADOOS somente é especificado o código.

Cadastro (e) – contém os dados da EMPRESA que emitiu a ordem de serviço, a tabela ADOOS se relaciona com esta tabela através da chave EMPRESA.

Cidade (f) – Tabela que será relacionada à tabela Cadastro (d), para buscar o código do estado (UF) do cliente.

Cidade (g) – Tabela que será relacionada à tabela Cadastro (e), para buscar o código do estado (UF) da empresa.

Condicaovenda (h) – Tabela que contém informações inerentes à condição de venda estabelecida na tabela ADOOS (coluna cd_condicaovenda).

As demais tabelas não precisamos colocar neste exemplo, já temos o suficiente. Bom, já identificamos o papel de cada tabela. Este trabalho pode ser realizado lendo a estrutura de cada tabela, fazendo consultas, consultar a estrutura no modo design, e assim por diante. Até aqui não tem segredo, certo?

Depois que nós concluímos este levantamento, vamos então analisar todas as referencias na parte WHERE, onde estabelecemos os relacionamentos entre as tabelas:

ADOOS ~> É a tabela que nos serve de parametro, pois temos o número da ordem de serviço. É ela que receberá a comparação com os parâmetros recebidos pelo procedimento:

image

ADOOSOutra ~> Se relaciona com a tabela ADOOS através das chaves primárias. Já identificamos seu relacionamento.

image

ADOOSEncerra ~> mesmo caso da ADOOSOutra, relacionamento 1x1 através das chaves primárias.

image

Tabelas de cadastro e respectivas cidades ~> veja que o código de cadastro da tabela A (ADOOS) é igual ao código de cadastro da tabela D (CADASTRO DO CLIENTE). O código da cidade F é igual ao código da cidade da tabela CADASTRO DO CLIENTE D. Também relacionamos o código da empresa que emitiu a OS com a tabela CADASTRO E, que também relaciona o seu respectivo código de cidade com a tabela CIDADE G.

image

Leia atentamente o código. Nunca observe a query como um todo, leia de forma segmentada. Primeiro veja as tabelas, se concentre em entender qual o mapeamento de dados responsável por cada uma. Depois, identifique cada relacionamento entre as tabelas. Veja item por item, para identificar possíveis erros de JOIN (relacionamento). Mas é necessário que seja observado item por item. Importante lembrar para que sempre leia atentamente as mensagens de erro durante execuções no sql. Muitas vezes, ao executar um procedimento, o usuário vê a mensagem vermelha de erro, e imediatamente coloca na cabeça: deu erro, vou procurar… mas nunca lê a mensagem, que diz exatamente onde está o problema! Digo isso por que tive esta experiência com uma pessoa que está aprendendo nesta semana *-*

\o/

terça-feira, 23 de novembro de 2010

O truque do zero à esquerda

Olá caros sqlnautas. Este artigo é relacionado à T-SQL em Microsoft SQL Server. Este artigo é compatível com qualquer versão a partir do SQL Server 2000.

Existem algumas situações, em que precisamos concatenar zeros à esquerda de um número para transformar em string, geralmente necessário quando estamos fazendo layouts de arquivos de texto para exportação entre sistemas.

Exemplo:

O número 25 deve retornar como 00025, assim como o número 325 deve retornar 00325 (mesmo número de algarismos).

Mas como resolver isso facilmente? Muito simples!

 

Code Snippet
  1. declare @numero int
  2. declare @texto varchar(100)
  3.  
  4. select @numero = 23
  5.  
  6. select @texto = right('00000' + rtrim(@numero),5)
  7.  
  8. select @texto as [NTexto]


O resultado, não pode ser diferente:

 

image

 

Veja no exemplo abaixo, se o mês fosse janeiro ou outubro, ambos ficariam corretos com a concatenação do zero à esquerda:

image
 

A lógica é muito simples.

RTRIM ~> utilizado para remover espaços a direita de uma string, porém quando usado em um valor inteiro, o transforma em string.

RIGHT(str, qtd) ~> Pega os caracteres à direita da string informada conforme quantidade especificada.

“rtrim(@numero)” ~> transforma o valor da variavel @numero (inteiro) em string;

”right(‘00000’ + rtrim(@numero),5) ~> concateno ao número convertido em string o número fixo de zeros a esquerda que desejo (neste caso, estou utilizando 5 zeros), e depois corto à direita 5 digitos.

O número 25 ao concatenar os zeros fica “0000025” e com o right(str, 5), deixa somente 00025.

Esta técnica garante que independente do número, ele sempre irá preencher a quantidade correta de zeros a esquerda. Lembre-se de dimensionar corretamente a quantidade de zeros, pois se o número for maior que o limite, não ocorrerá erro, porém o número retornado será truncado. (exemplo: se está trabalhando com 5 zeros, e utilizar esta técnica no número ‘123456’, retornará à string somente ‘23456’)

That’s all folks!