Developer Ninja's

Something has been on my mind ever since returning from Tech-Ed 2011. I then watched the “facebook” movie and I realised - I have, as have many of my co-workers strayed. Strayed from what you may ask? Well from being Ninja’s.

Somehow we have got caught up in other things, lost our edge – that bit of your personality that made you want to go out and just “kill it” - has been lost. Mediocrity has now set in and you are just content to role with the punches, follow the crowd, break the rules and deliver a substandard product.

Would a Ninja go into battle unprepared, not wanting to win the battle? Or even worse not care if he won? Not only that the true Ninja is not known he’s anonymous, seeks no glory. He plans, gets in and executes his plan as efficiently as possible, no distractions, no deviations and gets out. If during the execution he gets an unknown challenge or unexpected issue, the opposing force is rechanneled against that opponent and the problem is defeated. Other ninjas in the same operation are attuned to his situation and lend a hand. They are fighting their own battle BUT aware of each other and their common goal.

Do we arrive at work, plan our day, code to our best ability, remove our distractions and when the job gets done get the hell outta there? No! We arrive not knowing what we are going to do, we deliver poor code, watch movies, respond to tweets and so the list goes on and on. When the situation changes we call it scope creep and when a fellow Ninja is falling we are unaware. We work overtime, go home to have to come back to finish the job another day.

Ninja’s know their tools, they are masters and train to be so. We should be mastering our technology tools and once you’ve mastered it, you master another. The more you master the more powerful a Ninja you are.

I’m not saying Ninjas are only “techies” - by no means. It’s a mind-set, an approach, a delivery style.

“He ain't got no distractions
Can't hear those buzzers and bells
Don't see lights a flashin'
Plays by sense of smell
Always gets a replay
Never tilts at all” – Pinball Wizard, The Who.

As of today, I’m done with my distractions and interruptions – I’m a delivery boy, a serious Ninja Delivery Boy. I also only want to enter combat with Ninja’s at my side, Ninjas who have used opportunities to train and master their tools. I need them. I can’t master all the tools, so I really need them if we are to win. When the battle draws near, unfortunately, the unprepared will be left behind.

Today I want to find my inner Ninja, do you?

SQL : CASE Statements in ORDER BY clause....possible?

While digging through some SQL code, we discovered that you can in fact use CASE statements inside the ORDER BY clause. We have found that clients like to use the ORDER BY on various reports, but having the option to dynamically order the display or the results.

The old way of doing this, is to write dynamic SQL, but we know that is not as efficient as can be.
Here is an example of how to go about using CASE statements in the ORDER BY clause.

/**

Create Temp table and fill with sample data

**/

CREATE TABLE #Company

      (

            CompanyID int

            ,IndustryNumber varchar(20)

            ,RegisteredName varchar(50)

            ,TradingName varchar(50)

      )

/**

Insert the data into the temp table

**/ 

INSERT INTO #Company

SELECT 18,   'GP 00028/2005', 'DESIGN BY SORELLE CC',  'M&V INTERIORS'

INSERT INTO #Company

SELECT 19,   'GP 00029/2005', 'WILLIAM TELL INDUSTRIES (PTY) LTD', 'WILLIAM TELL INDUSTRIES (REUVEN ESTATES)'

INSERT INTO #Company

SELECT 24,   'NW 00034/2005', 'M C GHOOR & SON (PTY) LTD',   'GHOORS'

INSERT INTO #Company

SELECT 28,   'GP 00040/2005', 'LIVANGO (PTY) LTD', 'MEYERS CURTAINS'

INSERT INTO #Company

SELECT 30,   'GP 00043/2005', 'RAY ROWLEY OFFICE FURNITURE (PTY) LTD', 'SCEENCOR'

INSERT INTO #Company

SELECT 34,   'GPJ00048/2005', 'GEORGE SMITH', 'ANBEL ENTERPRISES'

INSERT INTO #Company

SELECT 42,   'MP 00059/2005', 'P.R. UPHOLSTERERERS', 'PIET RETIEF UPHOLSTERS'

INSERT INTO #Company

SELECT 45,   'GP 00063/2005', 'G G INTERIORS DECORATORS CC', 'ROSEMARIE INTERIORS DECORATORS'

/**

Method 1: Declare an order by variable (@OrderBy) in which can pass the desired Order By X field to.

Note: The data type of each ordered by item needs to be the same type or a casting error occurs - integers can be CAST to varchar but this causes incorrect ordering e.g result: 1, 11, 15, 2 ,23 , 30

**/

DECLARE @OrderBy varchar(100) = 'TradingName' --RegisteredName  -- Other -- TradingName

SELECT c.*

FROM #Company  c     WITH (NOLOCK)

ORDER BY

      CASE

            WHEN @OrderBy = 'TradingName' THEN c.TradingName

            WHEN @OrderBy = 'RegisteredName' THEN c.RegisteredName

      ELSE

            c.IndustryNumber

      END

/**

Method 2: By creating separate case statements no casting errors occur and can cater for various ordering.

If working with reporting services can create a dataset on the report or a stored procedure that passes what each Order By ID represents for the users to select.

e.g

CREATE PROCEDURE prReport_OrderByOptions

AS

SELECT

      1                                   AS OrderByID

      ,'CompanyID'                        AS OrderByOption

    

UNION

SELECT

      2                                   AS OrderByID

      ,'RegisteredName'                   AS OrderByOption 

     

UNION

SELECT

      3                                   AS OrderByID

      ,'IndustryNumber'                   AS OrderByOption

**/

DECLARE @OrderByID int

SET @OrderByID = 1

SELECT

      *

FROM #Company   c     WITH (NOLOCK)

ORDER BY

      CASE WHEN @OrderByID = 1 THEN c.CompanyID  END,

      CASE WHEN @OrderByID = 2 THEN c.RegisteredName   END,

      CASE WHEN @OrderByID = 3 THEN c.IndustryNumber END

     

DROP TABLE #Company     

Getting away from not hard coding at all, is a tough challenge, especially related to SQL. The next point to think of is ORDER BY multiple columns...?
That's all for now. Please feel free to comment or give any feedback on the post.