Programming and Ignorance

Ignorance does not have to be good or bad.  It is.  No matter how much any single individual knows they are ignorant on some other topics.

In other words, there are no Renaissance men anymore.

Even within our chosen professions we can all suffer from ignorance.  Many years ago I was at a company and they asked a programmer to sort data that was retrieved from SQL Server.

Everyone connected back up sometime later to find out what was taking so long.  The programmer was writing a bubble sort and said it would be some more time before it could be finished.

We all had a big laugh and then taught the programmer the ORDER BY clause which sorts output from a query.

I had been programming in T-SQL under Microsoft SQL Server for many years before I decided to get a certification for Querying Microsoft SQL Server 2012.  I began to study and there were queries of mine in the past that I had always wondered why they didn’t work.

Queries using an expression twice in the select clause or using an aliased column name in the where clause.

It all came down to ignorance.  The order you write a SQL statement is different than the order it is processed.

Select Dept, Count(*) as CNT
From Employees
Where Sex = ‘F’
Group By Dept, Country
Having Country = ‘USA’
Order by CNT;

The above query is most frequently written top down just as you see it.  However; when it is process by SQL Server it is processed in this order:

  1. FROM
  2. WHERE
  3. GROUP BY
  4. HAVING
  5. SELECT
  6. ORDER BY

So, using the alias as above works since the ORDER BY clause is processed after the SELECT clause.  The below query fails because the WHERE clause contains an alias created in the SELECT clause, but the SELECT clause has not been processed yet.

Select Dept, Count(*) as CNT
From Employees
Where CNT > 1000
Group By Dept, Country
Having Country = ‘USA’
Order by CNT;

In addition, SQL Server processes clauses ‘all at once’ so you can’t even use the CNT alias in another part of the SELECT clause.  This type of error is shown below with the CNT + 1 in the SELECT clause.

Select Dept, Count(*) as CNT, CNT +1
From Employees
Where Sex = ‘F’
Group By Dept, Country
Having Country = ‘USA’
Order by CNT;

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s