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
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.
case upper( [YourQueryItem] )
when 'TOR' then 'Toronto'
when 'VCR' then 'Vancouver'
when 'MTL' then 'Montreal'
else
'No Location'
end
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
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.
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