Is there a case against the use of CASE?

Consider an example statement:

 

select field1, field2, field3, rt = case cond_field1  when 'B' then 'BUY'  when 'S' then 'SELL' end, field4
from some_table

 

This uses a conditional case keyword within the SQL statement, which has the potential to be evil.

It is possible that the statement be expanded and rewritten as such:

 

select field1, field2, field3, 'BUY', field4
from some_table
where cond_field1='B'
UNION
select field1, field2, field3, 'SELL', field4
from some_table
where cond_field1='S'

 

This removes the conditional case keyword and also has the additional benefit a performance improvement should cond_field1 also be part of an index! However, I admit that the second approach does become a bit more unwiedy when there are multiple case conditions within the original statement. The second approach can also be more difficult to maintain since changes to the query have to be done in multiple places (e.g. if a new field5 is to be retrieved as well, the statement has to be updated in 2 places, otherwise the UNION will fail)

In short, the use of CASE can be justified on a case-by-case basis. (smile)

Simpler Operations

Extracting this code I came across (field names have been redacted):

(CASE WHEN (a.Field1 - b.Field2) > 0 THEN a.Field1 - b.Field2 ELSE 0 END)

Logically not wrong, but the number of mathematical operations can be cut further to increase processing speed.

Zoom in here

(a.Field1 - b.Field2) > 0

There is an arithmetic operation and a comparison operation here. This can be reduced to a single comparison operation with no loss in logic. The arithmetic operation is only executed when the expression is true, otherwise the literal 0 is returned.

(CASE WHEN (a.Field1 > b.Field2) THEN a.Field1 - b.Field2 ELSE 0 END)

Of course I may be wrong as I have not done any benchmarking. (smile)

Sharing my technical articles

I have finally managed to get enough PDUs to renew my PMP status! This means that I can probably write on topics other then project management to vary things a little here.

In my coming posts, I will be sharing some technical articles that I wrote earlier (meaning about a year or two back) but have never published to this site. Although IT moves at a breakneck pace, good principles and concepts will still apply regardless. Or so I hope!

Equipping staff with the correct skill sets

Project_Management_(phases)Since I seem to be in the mood of drawing on my previous experience with the Air Force, I thought I will like to highlight another challenge that Project Managers in my region seem to be facing, and it is something that PMs need to be aware of as it can lead to major problems in project execution.

In the Armed Forces in general, there is an established structure and process where everyone starts from ground zero as a recruit and works his way up the system by picking up skills sets and being developed according to his aptitude (or at least that is how it is supposed to work!) If you are going to be assigned to a commando unit, they will make sure you go to commando school to learn the skills of the trade. Likewise, if you are identified to join the Air Force, you will be assigned to Air Force School to make sure you are grounded in the philosophies of the Air Force and have the necessary knowledge and expertise before being shipped out to the units.

Shockingly, in the corporate world where there are always concerns about expenditure and focus on budget cuts, staff may be deliberately put into situations that they are ill-prepared for. There is a general reluctance to spend on training staff due to the costs (both monetary and time) and the possibility that the staff may leave the organization before applying his skills fruitfully. Too often than I dare to count, I have seen people being assigned to projects that they may be ill-prepared for, but training is given lip service in the guise of “on-the-job training” where the poor soul is expected to be given a baptism of fire and come out as wisened and more competent — if he is not already burnt to a crisp.

Where possible, training should always be part of a project plan especially if the skill set may be lacking in the current resources. If it is unimaginable that an Air Force storeman can be deployed into a theatre of war as an Airborne Ranger since it may jeopardise the mission, it is also unimaginable that we should allow untrained personnel to be force-fit into the project to perform tasks that they are ill equipped for. Not training the staff to take on the project challenges is a risk. Period.

As I have seen in a somewhat humourous article before, there was this interesting conversation between a HR Manager and a Project Manager.

HR Manager: No, you cannot send this person for this expensive training course! What if he goes for the course and leaves us next month?

Project Manager: It will be even worse if he doesn’t go for the course and learns the necessary skills. And he stays with us for years…

Situational Awareness in Project Management

aircraft-380740_640Back when I was serving in the Air Force, one of the important principles that they drilled into all personnel — regardless of whether they were pilots — was “situational awareness”. For the pilots, this could be a matter of life and death as they had to be constantly aware of their environment to any possible dangers. Other than the obvious dangers of being engaged by enemy aircraft, there was also the challenge of keeping that chunk of metal in the sky!

Dangers can be posed by terrain, engine failure, inaccurate instruments, etc. For the non-pilots, the emphasis was more on safety — personnel and equipment safety. Any mistakes made during training or operations can result in expensive equipment being damaged or personnel (who are also expensively trained) being hurt.

In the context of project management, it will help to keep one’s ears up and have your personal “radar” switched on to detect if there may be any factor that can affect the success of your project. Possibly a merger with a larger competitor which already has implemented something you are working on? Some other project that senior management has initiated and will take resources away from your project? Some key personnel who are vital to your project’s success leaving the organization? These are undoubtedly factors that can contribute to the success or failure to your project and will have to be highlighted as risks if you want to keep the project on course.

You should not be shy to approach the steering committee or project sponsor if you feel that the project is coming to a point where it may be canned. It can still be considered a success if you practice situational awareness and pull precious company resources and time from a project in a timely fashion once you have confirmed that the project is no longer viable.

Taking a step back

The average workday can be so full of operational and project tasks that the PM is simply inundated and may be stuck in a “reactive” mode where he is only able to react to things happening around him rather than taking a proactive approach to nip problems in the bud.

Whenever such a situation threatens to take place with you, one of the approaches can be to simply stop whatever you are currently doing, then take a step back and reassess the situation and the predicament you are in. In warfare tactics, this is known as a “strategic retreat” where some ground is given up to the enemy in order to regroup your resources, reassess the situation and redeploy resources in a more effective manner. By doing this, you may be able to find that some of the tasks that you were heavily involved in may not be that important in the larger context of things or can even be handed over to another party who is in a better position to take on the task!

However, what if you are indeed in an unfortunate situation where all the tasks are of high priority and cannot be handled by any one other than yourself? In this case, you will probably have to raise a few flags and cut your losses by informing the stakeholders that the task pipeline is full and project triage will have to be enforced so that only those tasks that are highly valued by the stakeholders will remain in the pipeline.

Minute taking for Project Meetings

The taking of minutes always seems to be such a chore that there is almost a palpable sense of relief when one finds out that it is not his turn to take the minutes for a particular meeting.

While it may be possible for some meetings to have a minute taking schedule to allow rotation of people jotting the minutes, it may not be possible for some project meetings which are small or where the project manager (PM) is actually the lowest-ranked in the corporate hierarchy amongst the attendees — which means that the PM always ends up as the one taking the minutes.

I find this practise to be quite ineffective as it is difficult to be paying attention and being active in the discussion whilst jotting down notes for the meeting at the same time. Some of my colleagues have resorted to voice recording the meeting proceedings in order to participate actively in the discussions and write the minutes at a later time. However this also does not make good use of the PM’s time.

mindmapIn the ideal situation, there will be a Project Coordinator who will be able to assist the PM in such administrative tasks so that the PM can focus on actual project tasks, but that may be an impractical request in the current cost-cutting world.

 

 

My approach to this problem is to use a mind-mapping tool like Freemind to record the minutes so I can easily group the topics of discussion in a quick fashion and flesh out the meeting minutes at a later point in time. I can even send out the mindmaps as meeting notes if the attendees are receptive to it. In fact, some of the attendees have even approached me to find out how to use mind mapping tools as they have seen how effective it can be for note taking!