Cast é uma operação consideravelmente comum na programação e em algumas situações também aparece no SQL. Neste artigo compartilhamos um pouco de nossa experiência ao implementar cast no sql server em combinação com outras operações.
APRESENTANDO O PROBLEMA
Ao utilizar SQL para armazenar e gerir os dados de um software, eventualmente será necessário realizar operações “esotéricas”, como converter uma string em um número, um número inteiro em um número real, parte de uma string em data, etc.
Na programação chamamos de cast a essa operação de converter uma informação de uma determinada estrutura em outra estrutura e, dependendo da linguagem utilizada e dos tipos de dados envolvidos, é uma operação até bem tranquila de implementar. Mas é no SQL?
No SQL, a operação de cast depende do software de gestão de banco de dados utilizado, o famoso sgbd (ou dbms em inglês). E esse é o tema dessa artigo.
Em um projeto em que utilizamos o framework .net e o sql server (sim, um projeto em que utilizamos ferramentas microsoft em tudo) precisamos fazer o cast de uma informação armazenada como string para outros tipos como data (date) e números reais (float).
Nós já tínhamos implementado cast antes no mysql, que fornece algumas funções de conversão de dados muito próximas das que existe na linguagem C. No sql server, não tem nada parecido e aí começaram as peculiaridades e um problema que nos tomou uma semana inteira para resolver. Isso mesmo, uma semana inteira para resolver um ‘simples’ cast.
O TAL DO CONVERT
Converter dados no sql server, a princípio nem é tão complexo. Ele possui duas funções internas que fazem isso pra você, de forma bem transparente: a CAST e a CONVERT.
Ambas funcionam de modo parecido porém em nosso projeto, identificamos que a CAST é mais adequada para a conversão de string para números (reais ou inteiros) enquanto que a CONVERT é mais adequada para casos mais genéricos já que permite identificar na função a formatação dos dados, exatamente o que precisávamos no projeto.
Você pode se perguntar, já que a CONVERT aparentemente resolvia o nosso problema, porque gastamos tanto tempo nessa solução? Bom, a questão é que a CONVERT resolvia parte do nosso problema, que era uma conversão genérica.
A outra parte do problema era identificar no ‘bolo’ dos dados qual conversão exatamente precisávamos implementar, ou seja, ao mesmo tempo que tínhamos que converter a string 9.5 para decimal (ou float se preferir), também estavam presente na mesma amostra informações como ‘alguma string qualquer’ que evidentemente não precisa de conversão alguma e ‘2018-01-01 00:01:53’ que deve ser convertida para DateTime e não decimal.
“E agora José?”
Agora, vamos procurar uma maneira de identificar quando e qual tipo de conversão é necessária.
Nosso primeiro teste incluiu o uso de regex, afinal, qual a melhor maneira de identificar padrões em string? Contudo, porém, todavia, havia uma pedra no meio do caminho chamada quantidade de dados da amostra.
A query em que a conversão devia ser utilizada (potencialmente, claro), buscava uma quantidade realmente grande de dados. Algo em torno de alguns milhares de registros. Daí você já consegue imaginar que regex não era a melhor solução, afinal, teríamos que rodar cada regex possível sobre toda a amostra antes de executar a conversão.
No melhor dos casos, acharíamos a conversão correta de imediato. No pior, precisaríamos rodar todos os regex até achar a conversão. Ou seja, solução ruim.
Pra tornar as coisas ainda piores, o sql server não gerava um erro ou mesmo uma exceção que pudesse ser capturada no C# listando qual conversão exatamente estava dando problema. Dessa forma, se na query tivesse algo em torno de 1000 conversões e a 501ª desse problema ele ignorava os 500 resultados corretos anteriores e simplesmente gerava um erro pouco ou nada explicativo (convertido em exceção no C#) falando que a conversão dava erro, mas não falava qual conversão.
Depois de horas e horas testando e debuggando com amostras menores, descobrimos que podíamos fazer um ‘divide and conquer à la sparta’, criando várias subquerys para converter o mesmo dado nas conversões possíveis ao mesmo tempo e depois unificando o resultado.
Utilizando esse método, escrevíamos uma query (Q) que fazia o OR de uma subquery que tentava converter o campo para int (QInt) com outra que tentava a conversão para decimal (QDec) com outra que tentava a conversão para datetime (QDat) com outra que simplesmente pegava o dado como ele estava (QDad) (Q = QInt OR QDec OR QDat OR QDad).
Por algum motivo que nunca descobrimos ao certo (mas supomos que seja por conta do uso do operador OR), o sql server não gerava exceção quando encontrava erro de conversão (ou se gerava isso não chegava no C#) e os resultados de cada conversão correta sempre apareciam.
Problema resolvido?
Ainda havia outra pedra no meio do caminho: a conversão para DateTime. Ao contrário do mysql no qual você simplesmente informa a formatação da string para o sgbd converter a stringo para datetime, no sql server tem uma complicação adicional: o style.
No sql server a formatação é um tanto fixa e pelo menos na conversão de string pra datetime é necessário informar a formatação interna em que a data deve estar (na de string pra decimal não precisamos disso).
Uma vez que encontramos o style correto, o problema do cast estava resolvido de uma vez por todas. Aí apareceu outro problema.
VAMOS COMPLICAR UM POUCO: ADICIONE UM GROUP BY
Além de realizar o cast de dados, também era necessário agrupar e ordenar o resultado da query.
Num primeiro momento isso pode parecer algo bobo, porém existe um pequeno detalhe no sql server que complicava a nossa vida: ele não faz ordenação de colunas virtuais.
Como em nosso primeiro estágio da solução, geramos colunas virtuais com os dados já formatados no padrão correto e o sgbd não ordenava esse tipo de coluna e precisávamos da ordenação, nos encontramos com outro problema para resolvermos, o que nos leva a próxima seção desse artigo.
Antes de já passarmos pra próxima seção, um pequeno adendo: o postgresql também não ordena colunas virtuais por padrão e o mysql após a versão 5.6 também não. É necessário alterar as configurações do sgbd para permitir esse tipo de operação.
A TAL DA CTE
A solução que o sql server provê para ordenar um campo virtual é utilizar uma tabela virtual, de forma que o sgbd veja o campo não mais como ‘inexistente’, mas sim parte de uma tabela (que pra todos os efeitos não existe de fato no banco). Isso é implementado com o uso de CTE’s, as common table expression.
Em nossa solução, colocamos a query que gera o resultado com os dados já convertidos em uma CTE e depois aplicamos a ordenação nessa CTE por uma query externa. Acabou ficando uma query dentro de outra query, dentro de outra query, mas isso o sql server dá conta sem problemas.
Com isso, finalmente demos fim a horas e horas de testes e mais testes, debugging e mais debugging, que nos renderam uma solução consideravelmente interessante e um tanto inesperada, já que nem em nossas mais loucas especulações imaginaríamos quem um ‘simples cast’ se tornaria algo tão complexo e trabalhoso de implementar.
A tempo, pra quem quiser aprender mais sobre as CTE’s, confiram esses links [1] [2] [3]