p. 1

tÉcnicas avanÇadas de select autor maxwell borges fernandes este artigo tem o objetivo de demonstrar através de exemplos práticos técnicas de select testadas e utilizadas por mim durante toda a minha experiência com bancos de dados relacionais os comandos aqui expostos foram testados em firebird 1.0 porém podem ser aplicados em outros outros servidores devendo-se tomar o cuidado de substituir as sentenças por outras compatíveis na nova plataforma os parágrafos com o fundo cinza são as instruções que devem ser digitadas e os com fundo azul são os resultados das consultas qualquer ferramenta que aceite a digitação de instruções sql como o isql podem ser utilizados 1 sumarização em colunas imagine que se tenha uma tabela com os campos tipo do animal raça sexo e quantidade e que se queira trazer em um único comando select uma coluna com o tipo do animal uma com a quantidade de animais fêmea e outras com a quantidade de animais macho os comandos a seguir tem o objetivo de demonstrar como resolver este problema de forma simples providenciando os dados necessários create table des_tipo nom_raca ind_sexo qtd_animal insert insert insert insert insert insert insert insert insert insert insert insert into into into into into into into into into into into into tab_animal varchar20 varchar20 char1 integer tab_animal tab_animal tab_animal tab_animal tab_animal tab_animal tab_animal tab_animal tab_animal tab_animal tab_animal tab_animal values values values values values values values values values values values values cao viralata m 6 cao viralata f 5 cao pastor alemao m 11 cao pastor alemao f 7 cao doberman m 3 cao doberman f 9 gato viralata m 13 gato viralata f 15 gato siamÊs m 3 gato siamÊs f 2 gato angorÁ m 1 gato angorÁ f 0 select from tab_animal des_tipo cao cao cao cao cao cao gato gato gato gato gato gato nom_raca viralata viralata pastor alemao pastor alemao doberman doberman viralata viralata siamÊs siamÊs angorÁ angorÁ ind_sexo mfmfmfmfmfmf qtd_animal 6 5 11 7 3 9 13 15 3 2 1 0

[close]

p. 2

entendendo o processo para resolver este problema iremos utilizar a função udf_pos da biblioteca tbudf que pode ser baixada no site da cflp esta função retorna a posição de um determinado caracter dentro de uma string É o que precisamos para fazer o nosso select select des_tipo ind_sexo qtd_animal udf_pos m ind_sexo as macho udf_pos f ind_sexo as femea from tab_animal des_tipo cao cao cao cao cao cao gato gato gato gato gato gato ind_sexo mfmfmfmfmfmf qtd_animal 6 5 11 7 3 9 13 15 3 2 1 0 macho 1 0 1 0 1 0 1 0 1 0 1 0 femea 0 1 0 1 0 1 0 1 0 1 0 1 conseguimos com este select fazer com que sempre que aparecer um animal macho a coluna macho venha com 1 e se for fême venha com 0 o mesmo acontecendo com a coluna fêmea chegando no resultado esperado select des_tipo sumqtd_animal udf_pos m ind_sexo as macho sumqtd_animal udf_pos f ind_sexo as femea from tab_animal group by des_tipo des_tipo cao gato macho 20 17 femea 21 17 2 totalização de valores/pesquisa de saldos esta técnica é baseada nos conceitos utilizados na técnica anterior mas se difere um pouco na forma de aplicação em sistemas de contabilidade e estoque é comum ter-se a necessidade de calcular o saldo de uma conta contábil ou de um item do estoque a solução mais comumente utilizada é a utilização de dois select s um pra buscar a soma das entradas e outro para buscar a soma das saídas diminuindose um do outro posteriormente esta técnica irá demonstrar como calcular o saldo de uma conta contábil utilizando-se apenas um select.

[close]

p. 3

providenciando os dados create table tab_lancamento_contabil cod_conta integer val_lancamento decimal12,2 ind_debito_credito char1 insert insert insert insert insert insert insert insert insert insert insert into into into into into into into into into into into tab_lancamento_contabil tab_lancamento_contabil tab_lancamento_contabil tab_lancamento_contabil tab_lancamento_contabil tab_lancamento_contabil tab_lancamento_contabil tab_lancamento_contabil tab_lancamento_contabil tab_lancamento_contabil tab_lancamento_contabil values values values values values values values values values values values 1 1 1 1 1 2 2 2 2 2 2 100 d 10 c 700 d 30 c 35.20 d 72.8 c 200 d 1100 c 120 d 325 c 1.30 d select from tab_lancamento_contabil cod_conta 1 1 1 1 1 2 2 2 2 2 2 val_lancamento 100 10 700 30 35,2 72,8 200 1100 120 325 1,3 ind_debito_credito dcdcdcdcdcd achando o multiplicador select cod_conta val_lancamento udf_pos d ind_debito_credito as val_debito udf_pos c ind_debito_credito as val_credito udf_pos c ind_debito_credito 2 1 as multiplicador from tab_lancamento_contabil cod_conta 1 1 1 1 1 2 2 2 2 2 2 val_lancamento 100 10 700 30 35,2 72,8 200 1100 120 325 1,3 val_debito 1 0 1 0 1 0 1 0 1 0 1 val_credito 0 1 0 1 0 1 0 1 0 1 0 multiplicador -1 1 -1 1 -1 1 -1 1 -1 1 -1

[close]

p. 4

chegando no resultado esperado basta agora multiplicar o valor do lançamento pelo multiplicador e agrupar por conta select cod_conta sumval_lancamento udf_pos c ind_debito_credito 2 1 as val_saldo from tab_lancamento_contabil group by cod_conta cod_conta 1 2 val_saldo -795,2 1176,5 3 descobrindo quais sequências faltam algumas vezes é necessário se descobrir quais as sequências de uma coluna estão faltando uma utilização para esta técnica é descobrir se todas as notas fiscais de um talão de notas foram utilizadas corretamente providenciando os dados create table tab_nota_fiscal num_nota integer insert insert insert insert insert insert insert insert into into into into into into into into tab_nota_fiscal tab_nota_fiscal tab_nota_fiscal tab_nota_fiscal tab_nota_fiscal tab_nota_fiscal tab_nota_fiscal tab_nota_fiscal values values values values values values values values 1 2 3 4 6 7 8 9 select from tab_nota_fiscal order by num_nota num_nota 1 2 3 4 6 7 8 9

[close]

p. 5

extraindo os dados select num_nota num_nota select maxnum_nota from tab_nota_fiscal where num_nota b.num_nota 1 qtd_falta from tab_nota_fiscal b order by 2 desc num_nota 6 2 3 4 7 8 9 1 qtd_falta 1 0 0 0 0 0 0

[close]

p. 6

create table tab_transporte des_transporte varchar20 insert insert insert insert into into into into tab_transporte tab_transporte tab_transporte tab_transporte values values values values trem de ferro carro aviao Ônibus select from tab_transporte des_transporte trem de ferro carro aviao Ônibus obtendo o resultado select nom_cidade des_transporte from tab_cidade tab_transporte order by nom_cidade nom_cidade brasilia brasilia brasilia brasilia rio de janeiro rio de janeiro rio de janeiro rio de janeiro sÃo paulo sÃo paulo sÃo paulo sÃo paulo des_transporte aviao carro Ônibus trem de ferro aviao carro Ônibus trem de ferro aviao carro Ônibus trem de ferro 5 consultas inteligentes algumas vezes é necessário oferecer uma opção de filtro ao usuário mas ao mesmo tempo permitir que aquele campo não seja filtrado e que sejam selecionados todos os registros uma forma de fazer isso é mudar a cláusula sql através de programação outra alternativa um pouco mais dinâmica é o uso de consultas inteligentes.

[close]

p. 7

providenciando os dados necessários create table des_tipo nom_raca ind_sexo qtd_animal insert insert insert insert insert insert insert insert insert insert insert insert into into into into into into into into into into into into tab_animal varchar20 varchar20 char1 integer tab_animal tab_animal tab_animal tab_animal tab_animal tab_animal tab_animal tab_animal tab_animal tab_animal tab_animal tab_animal values values values values values values values values values values values values cao viralata m 6 cao viralata f 5 cao pastor alemao m 11 cao pastor alemao f 7 cao doberman m 3 cao doberman f 9 gato viralata m 13 gato viralata f 15 gato siamÊs m 3 gato siamÊs f 2 gato angorÁ m 1 gato angorÁ f 0 select from tab_animal des_tipo cao cao cao cao cao cao gato gato gato gato gato gato nom_raca viralata viralata pastor alemao pastor alemao doberman doberman viralata viralata siamÊs siamÊs angorÁ angorÁ ind_sexo mfmfmfmfmfmf qtd_animal 6 5 11 7 3 9 13 15 3 2 1 0 montando a consulta inteligente select from tab_animal where ind_sexo :pindsexo or pindsexo t montando o funcionamento da consulta se ao parâmetro pindsexo for atribuido o valor t toda a cláusula where será verdadeira retornando todos os registros caso seja atribuído algum valor diferente de t somente serão retornados os registros em que o campo ind_sexo for igual a esse valor 6 conclusão espero ter conseguido mostrar com este artigo formas de se resolver problemas de extração de dados vivenciados no dia a dia me ative apenas às técnicas que considero o pulo do gato e que resolveram grandes dores de cabeça minha apesar de não ter feito comparações posso afirmar por experiência que a maioria das

[close]

p. 8

técnicas apresentadas teêm uma melhor performance se comparadas à técnicas alternativas qualquer dúvida ou sugestão pode ser feita através do meu email artigo original maxwell b fernandes colaborador da cflp maxwell@rezendesistemas.com.br comunidade firebird de língua portuguesa visite a comunidade em http www.comunidade-firebird.org a comunidade firebird de língua portuguesa foi autorizada pelo autor do original para divulgar este trabalho

[close]