Today I am going to revisit two posts from the past couple of weeks. I want to revisit them just to make some minor updates and clarifications. This is nothing earth-shattering but is good info to have.
The two posts to revisit are:
In this particular post, I shared a simple example of how to perform bitwise operations. The example involved the bit comparison of up to three values. I made the query overly complicated. Here is a less complicated method to get to the same results.
DECLARE @ColorType INT = 3
FROM ColorPlate cp1
WHERE cp1.colortype & @ColorType <> 0
ORDER BY ColorID;
Can you see the simplicity in that? Both methods work. Looking at this code, it is a little easier to follow and understand.
Derived Table Column Alias
In the post about subqueries and derived tables, there was an important piece of information that I neglected. In the first example I posted there is a good example of what was neglected. The first example was a derived table based on values rather than a query. Here is that example again.
VALUES (1, 2), (3, 4), (5, 6), (7, 8), (9, 10)
) AS MyTable(a, b);
If you were to try to write that query without the external column alias naming convention, you would get an error. The error message(s) would be like the following.
1234 <span style="color: #ff0000;">Msg 8155, Level 16, State 2, Line 4</span><span style="color: #ff0000;">No column name was specified for column 1 of 'MyTable'.</span><span style="color: #ff0000;">Msg 8155, Level 16, State 2, Line 4</span><span style="color: #ff0000;">No column name was specified for column 2 of 'MyTable'.</span>
Knowing this information could save you a bit of headache and time. When using a value set rather than query, the column alias is required after the table alias.
Like I said, nothing big or fancy today – just a quick revisit to clarify some previous posts. Oh, and I have some more good stuff coming down the pipe (like another bitwise related post).