In data warehousing where aggregation (GROUP BY) is one of the most common things done in queries, we often get the warning: Warning: Null value is eliminated by an aggregate or other SET operation In most cases what is happening is harmless. But what is happening? NULL in aggregation First we have to better understand…
I recently had a challenge mapping attributes using a lookup table. I had the same customer name in two tables “bobs widgets”, but when I tried to join on the names, there was no match. To cut a long story short, there is more than one way to encode a space. This picks up from the…
When using bash, ps is a very powerful tool for viewing running processes. I have built a script/library for manipulating its output to create trees for either running shell scripts, or user sessions. This allows me to see who is doing/executing what on a linux host at a point in time. Continuing with some of…
Sometimes I want to run a shell script or command for a series of dates, so I have created a bash library function which takes a start date and an end date, and iterates over the dates passing each date to another command/script. This is moving away from SQL/Database specific content. Much of what I…
Today I’m looking at solutions to Magic Numbers. Magic numbers are hard coded numbers (or references) which appear in scripts and procedures, they creep in very easily. I don’t think that magic numbers are entirely bad. If you have a stored procedure which modifies data for a specific customer, having the one customer ID in…
If an ID or reference is used more than once in a process, it’s best to store it in a variable and reference that. This can slow down debugging, but makes a process far more portable. It also reduces the number of magic numbers in a process (or at least times they are defined). I…
Part of managing data is cleaning text. Tabs, spaces and line breaks can sneak in, and confuse output. When working with whitespace characters and line breaks, we can’t see them. This can cause a lot of confusion for users (and developers) when unexpected behavior occurs. Keep in mind we have to be careful, often we…
A new project, incomplete documentation, and the original author gone. I need to know “what updates and uses this object?”. So, how do I search for it?. If there is good source control and code management, a simple search will work. In many cases, there isn’t the luxury of code management. Search the code behind…
When are two values the same but different? When they are both NULL. Here I compare equality checks, including my datatype neutral method. The Problem Equality (or lack thereof) between values is an easy check. Is 1 = 3, or is 4 <> 5? Many people (and processes) get unexpected results when they try to…
Here is a quick look at number sequences, which are a brilliant (and fairly database neutral) method to create numbers (and rows) to build datasets from. While working on the dates articles, I used number sequences a lot and thought they were worth coming back to. What is a number sequence A number sequence can…