Bitwise and Derived Table revisited

Categories: News, Professional, SSC
Tags: ,
Comments: No Comments
Published on: August 31, 2011

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:

  1. Bitwise Operations
  2. Derived Table Column Alias

Bitwise Operations

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
 
SELECT cp1.*
	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.

SELECT *
	FROM (
			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.

Msg 8155, Level 16, State 2, Line 4
No column name was specified for column 1 of 'MyTable'.
Msg 8155, Level 16, State 2, Line 4
No column name was specified for column 2 of 'MyTable'.

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).

No Comments - Leave a comment

Leave a comment

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>






Calendar
August 2011
M T W T F S S
« Jul   Sep »
1234567
891011121314
15161718192021
22232425262728
293031  
Content
SQLHelp

SQLHelp


Welcome , today is Thursday, April 17, 2014