CA Clarity Tuesday Tip: Simplify Complex NSQL's

Discussion created by Shawn_Moore Employee on Aug 9, 2011
Latest reply on Nov 23, 2011 by gph1857
CA Clarity Tuesday Tip by Shawn Moore, Sr. Principal Support Engineer for 08/09/2011

Today's tip is one I learned indirectly from Paul Maxwell, one of our services architects in Australia.

In more complex situations where many columns are the results of functions, calculations or case-then-else-end often the better approach is to handle the base SQL query as a sub-query and then “translation” into NSQL is much simpler, such as this:

, @select:dim_prop:user_def:implied:MSPMAP:clarity_name:clarity_name@
, @select:dim_prop:user_def:implied:MSPMAP:mspname:mspname@
, @select:dim_prop:user_def:implied:MSPMAP:prtype:prtype@
, @select:dim_prop:user_def:implied:MSPMAP:prflags:prflags@
, @select:dim_prop:user_def:implied:MSPMAP:record_type:record_type@
, @select:dim_prop:user_def:implied:MSPMAP:behaviour:behaviour@
, @select:dim_prop:user_def:implied:MSPMAP:custom_attribute:custom_attribute@
, @select:dim_prop:user_def:implied:MSPMAP:is_customized:is_customized@
from (
m.prid prid
, m.prname clarity_name
, m.mspname mspname
, m.prtype prtype
, m.prflags prflags
, (
case m.prtype
when 1 then 'Project'
when 2 then 'Resource'
when 3 then 'Task'
when 4 then 'Assignment'
when 5 then 'Team'
else 'INVALID' end) record_type
, (
case m.prflags
when 1 then 'Import to Clarity'
when 2 then 'Export from Clarity'
when 3 then 'Bidirectional'
else 'INVALID' end) behaviour
, (
when m.prname = lower(m.prname) then 1
else 0 end) as custom_attribute
, (
when m.prid not in (1,2,3,4,9,17,18) then 1
else 0 end) as is_customized
from mspfield m
) sqlwrap
where @filter@

The added bonus is that the “guts” of the query remains untouched SQL.