Analytics Custom Variable

[kublait]

Hi All,
We have Siebel Analytics 7.5 and Oracle 9.2.0

Recently I created a variable in Analytic call “USER_SUPERVISOR”. The definition for this variable is as follow:

select login from siebel.s_user where row_id = (select pr_emp_id from siebel.s_postn where row_id = (select party_id from siebel.s_party_rpt_rel where rel_type_cd = 'DIRECT' and sub_party_id = (select pr_held_postn_id from siebel.s_contact where row_id = (select row_id from siebel.s_user where login = ':USER'))))

The ':USER' is a session variable and the code returns the value of the user’s supervisor.

For example Joe reports to Tim. When Joe login the USER variable is Joe and the USER_SUPERVISOR variable is Tim. However if Tim login and Tim does not have a supervisor I want the code to return Tim as the variable for both USER and USER_SUPERVISOR. I though I could accomplish this with the following:

select NVL(login,':USER') from siebel.s_user where row_id = (select pr_emp_id from siebel.s_postn where row_id = (select party_id from siebel.s_party_rpt_rel where rel_type_cd = 'DIRECT' and sub_party_id = (select pr_held_postn_id from siebel.s_contact where row_id = (select row_id from siebel.s_user where login = ':USER'))))

As you might have guessed it’s not working. It’s returning an empty result set instead of “Tim”. Any insight or suggestion will be greatly appreciated.

Thanks

 

[kublait]

We found the answer in case anyone is interested. The reason why the NVL function does not returns the expected value is because the MS OLEDB provider allows Oracle to add the pseudo ROWID column to the end of the query and it does this in order to return an updateable dataset to the ADO client. The DBPROPSET_MSDAORA_ROWSET property DBPROP_MSDAORA_DETERMINEKEYCOLUMNS needs to be set to false, or the rowid is requested in order to prevent duplicate rows from being returned from the query.

Our work around was to use the CASE/WHEN in conjuction with the count function. The final codes are as follow:

select(case
(select count(login) from siebel.s_user where row_id =
(select pr_emp_id from siebel.s_postn where row_id =
(select party_id from siebel.s_party_rpt_rel where rel_type_cd = 'DIRECT' and sub_party_id =
(select pr_held_postn_id from siebel.s_contact where row_id =
(select row_id from siebel.s_user where login = ':USER')))))
when 0
then ':USER'
else
(select login from siebel.s_user where row_id =
(select pr_emp_id from siebel.s_postn where row_id =
(select party_id from siebel.s_party_rpt_rel where rel_type_cd = 'DIRECT' and sub_party_id =
(select pr_held_postn_id from siebel.s_contact where row_id =
(select row_id from siebel.s_user where login = ':USER')))))
end) login
from dual