Cognos SQL

[CCSlice]

Hello Everyone....

I am currently challenged with SQL in Cognos.  My challenge is creating a new column based on condition on what is found on an existing column.  I know in SQL (either MSSQL or Oracle, I can create new column based upon a business rule.  What I am trying to do is extract the city from a column and return it to a new column.    Here is the code:

With
Query25 as
    (select
           NMSPC_1.ID  as  CWL_ID,
           NMSPC_1.FIRST_NAME  as  CWL_Usr_FName,
           NMSPC_1.LAST_NAME  as  CWL_Usr_LName,
           NMSPC_1.USER  as  CWL_Usr,
           NMSPC_1.MANAGER  as  MyGrps_Mngr,
           NMSPC_1.S_ID  as  MyGrps_ID,
           NMSPC_1.ADJN  as  DLA_Lmt_Spl_Adjn
     from
           CWL...USER BI_CWL_USER
    ),
Query14 as
    (select
           NMSPC_2.ID  as  ID,
           NMSPC_2.LNBR  as  Case_Num,
           NMSPC_2.TASK  as  Task_Name,
           NMSPC_2.TASK_P  as  Task_Priority,
           NMSPC_2.Q_PRIORITY  as  Q_Priority,
           NMSPC_2.LAST_DATE  as  Last_Vwd_Date,
           NMSPC_2.HRS_EXN  as  Hrs_to_Exec,
           NMSPC_2.TASK_S  as  Task_Stat,
           NMSPC_2.TASK_T  as  Task_Typ,
           NMSPC_2.EXEC_DATE as  Exec_DateTime,
           NMSPC_2.TASK_DATE  as  Task_Creatn_Date,
           NMSPC_2.BUS_TYPE  as  Bus_Activity_Date,
           NMSPC_2.BUS_DESCR  as  Bus_Activity_Desc,
           NMSPC_2.P_BUS_ID  as  Parent_Bus_Step_Id,
           NMSPC_2.TSK_ST  as  Task_Strt_Date,
           NMSPC_2.TSK_EN  as  Task_End_Date,
           NMSPC_2.Q_WORK_ITEM_ID  as  Q_Wrk_Item_Id,
           NMSPC_2.Q_LOC  as  Q_Loc,
           NMSPC_2.ACT_END_TIME  as  Activity_End_Time,
           NMSPC_2.Q_NAME  as  Q_Name,
           NMSPC_2.GRP_CLASS  as  Grp_Classficatn,
           NMSPC_2.Q_CLASS  as  Q_Classficatn,
           NMSPC_2.A_STAT_DATE  as  App_Stat_Date,
           NMSPC_2.TSK_OWR  as  Tsk_Owner,
           from
           CWL...TASK BI_TASK
    )
select
         Query14.Bus_Activity_Desc  as  level0key,
         Query25.CWL_Usr as  level2key,
         to_char(Query14.Task_End_Date,'yyyy-mm-dd')  as  levelkey,
       XMIN(1  for Query14.Bus_Activity_Desc,Query14.Q_Name,Query25.CWL_Usr,to_char(Query14.Task_End_Date,'yyyy-mm-dd') )  as  rowCount,
       XCOUNT(Query14.ID  for Query14.Bus_Activity_Desc,Query14.Q_Name,Query25.CWL_Usr,to_char(Query14.Task_End_Date,'yyyy-mm-dd') )  as  ID,
        Case
               when Query14.Q_Name contains 'Tor' then 'Toronto'
               when Query14.Q_Name contains 'Vcr' then 'Vancouver'
               when Query14.Q_Name contains 'Mtl' then 'Montreal'
         Else 'No Location'
         End as level1Key
from
       Query25
        left outer join
       Query14
        on (Query14.Tsk_Owner = Query25.CWL_Usr)
where
       ((trunc(Query14.Task_End_Date) > trunc(_add_days({sysdate},(-13)))) and (trunc(Query14.Task_End_Date) < trunc(_add_days({sysdate},13)))) and
       (Query14.Task_Stat = 'Completed')
group by
       Query14.Bus_Activity_Desc,
       Query14.Q_Name,
       Query25.CWL_Usr,
       to_char(Query14.Task_End_Date,'yyyy-mm-dd')
order by
       levelkey   

I would appreciate your help in understanding what I may be doing incorrectly to not yield the results for this crosstab report.

Thanks

[Lynn]

What results are you getting? Is it an error message or does everything show up as 'No Location'?

Personally I'd look at the native SQL rather than the Cognos SQL to do preliminary debugging.

What does the content of the Q_Name column look like? If it is in all uppercase then that could be a problem in your expression. If the value is only the code (e.g., Tor or Vcr) then you don't really need to do a 'contains' operator. It is difficult to advise accurately without understanding these details.

Not sure if this expression will work in your case or not, but food for thought perhaps.

Code Select Expand


case upper( [YourQueryItem] )
when 'TOR' then 'Toronto'
when 'VCR' then 'Vancouver'
when 'MTL' then 'Montreal'
else
'No Location'
end


[blom0344]

Looks to me the group by part does not match the non-aggregate dataitems in the select part..   Levelkey is in the 'select' but not in the 'group by' for instance

[CCSlice]

To Lynn:  The data in the field is not uppercase it shows like "MtlGateKeeperProjects".  So I know that my Case structure is accurate.    To Blom:  Now that I look at the code, I believe that you may be right:  That level key is out of sorts!   I will retry and advise on the results.  Thanks.

[blom0344]

The structure of the group by is dictated by the outer select. The sorting is optional. The group by clause should contain all non-aggregates