Hi all,
Who hasn't experienced this? You're testing your SQL code, only to discover that you're getting more results than expected. In some cases, accidentally using the same alias within the same join- or where clause can lead to this scenario. For example, “where t1.customer_id = t1.customer_id”. This effectively creates a condition like “1 = 1”, which is always true.
In this small blog, I'll guide you through detecting this situation throughout your branch's codebase. To effectively follow this blog, you'll need access to a code editor that has built-in support for regular expressions. If anyone knows a more efficient way to achieve the same results, please share it in the comments. Hopefully this helps!
Software Factory
Generate the code files using the Software Factory:
- Start up your Software Factory.
- Switch to the desired model and branch, for which you want to check identical aliases, by starting the task Models > Switch branch.
- Go to the screen Deployment > Creation.
- Generate definitions.
- Generate source code. In the pop-up, select the option Full as the upgrade method, and Code files as the method to Write to storage.
- Navigate to the code files path, by clicking on the folder control displayed behind the path.
Windows: merge the code files
Optional step for convenience: merge the code files using the Windows Command Prompt:
- Copy the path in the address bar using the right-click mouse button and select ‘Copy’.
- Start up the command prompt (cmd.exe).
- If the default drive letter displayed doesn't contain the code files, you can optionally navigate to the correct drive by typing the corresponding drive letter followed by a colon. For instance, "T:".
- Paste the copied code files path using the right-click mouse button within the command prompt window.
- For convenience, copy all code files into a single SQL-file. This makes it easier to search for identical aliases. You can use this command:
copy *.sql adesired_name_all_code_files].sql
Code editor
Search for identical aliases in your code editor:
- Start your code editor.
- Open the file rdesired_name_all_code_files].sql (or individual code files when you've skipped the previous paragraph)
- Search for identical aliases by selecting the option ‘regular expression’. Use the following expression:
\b(\w+)\.(\w+)\s*=\s*\1\.\2\b - For every hit that is not a false positive (like t1.string = t1.string + addition), you can scroll above to find the control procedure name the code belongs to. This enables you to fix the situation in your branch in the Software Factory. Naturally, in the mean time I have done so for IAM as well.
For those who are interested, you can expand the following text for a breakdown of the expression:
- \b: This ensures a word boundary, so we match only whole words.
- (\w+): This matches one or more alphanumeric characters (letters, digits, underscores), capturing them as the first group pattern.
- \.: This matches a period, which is used to separate an alias from the column or table name.
- \s*=\s*: This matches the equals sign between the columns, with optional spaces on both sides.
- \1: This is a reference to the previously captured pattern, making it match the same alias as in the first part of the expression.
- \2: This is a reference to the second captured pattern, making it match the same column name as in the first part of the expression.
- \b: This again ensures a word boundary.