Hierarchical Lookup similar to OBS

[Anon Anon]

Hi,  Can we have a lookup which is similar to the OBS hierarchical structure (with the complete path).  We must be able to select the OBS from a hierarchical structure and it should display the complete path after i select it.  Using 'OBS filter browse'  I do not get the complete path.Using "NBI_GET_OBS_FLAT_PATH_FCT" i get the complete path but the lookup is not hierarchical.  Is there a way where we can combine these both?    Thanks,Shruthi

[Anon Anon]

Shruthi,  i have craeted  an Lookup based on Nsql for this issue and working fine.Enclosed  an doc which have nsql,2 views sql (which i have created based on OBS Tables for our reporting) and the snapshots of lookup.we are using this for past 2 years.  Issues with Clarity OBS Browse  1. When an user clicks the OBS Browse it will show all the OBS and user has to select particular OBS and then select his untit / level.2.  When he select an obs which has 7 levels,that if he selects the 4th level ,it will show only the 4th level not the full Hierrachy.He do knows what he has selected.It is not at all user friendly.3. If he has the same name for different units imagine the confusion,he has re-do it again.4.Only the GUI is better.  Hope this one helps.  sundar

Attachment(s)

OBS Lookup.zip   345 KB 1 version

[Anon Anon]

Hi Sundar,  I think this is great. Is this wrriten in ORACLE. we use SQL . Let me try this out

[Anon Anon]

Hi Sundar,  Im getting an error message when I create this  ODF-0001: Unable to register dynamic query lookup: Could not retrieve or register the nsql::NSQL Syntax exception: NSQL_SYNTAX_ERROR_DB_ERROR, Info: [CA Clarity][SQLServer JDBC Driver][SQLServer]'DECODE' is not a recognized built-in function name. SQL Text: SELECT obs.obs_id obs_id, obs.obs_name obs_name, obs.RT_OBS RT_OBS FROM ( SELECT OBS_ID, DECODE(RT_OBS,NULL, 'Group/Business/Business Unit/Function/Team/Sub-Team/Sub-SubTeam','Group/Business/Business Unit/Function/Team/Sub-Team/Sub-SubTeam') obs_name ,RT_OBS FROM ( SELECT DISTINCT OBS_ID,SUBSTR(OL.OBS_PATH,1,LENGTH(OL.OBS_PATH)-1)RT_OBS,depth FROM UV_PRJ_RSM_OBS_DETAILS OD, UV_OBS_LEVELS OL WHERE OD.OBS_ID = OL.LEVEL1_ID AND OD.TABLE_NAME = 'SRM_RESOURCES' AND OD.TYPE_UNIQUE_NAME = 'RT_OBS' ) ORDER BY RT_OBS )obs .

[Anon Anon]

Hi Karen...  "DECODE" is a Oracle construct; you appear to be on SQL*Server so the syntax is going to be a little different.  [In Oracle terms, DECODE is a IF/THEN/ELSE shorthand, so DECODE(column_a,value,1,'Yes',2,'No','Other') means IF column_a = 1 then "Yes", ELSE if column_a = 2 then "No", Else "Other"]  Hopefully someone SQL*Server literate (not me!) can give you the equivalent syntax!

[Anon Anon]

Hi,  sorry not able to help u.sql server so long(7 yrs back...).More over i don't have sql server installed.i used this OBS Lookup in 7.5.x version.  From our Forums.  http://caforums.ca.com/t5/CA-Clarity-General-Discussion/NSQL-CASE-WHEN-THEN/m-p/9492  Taken some examples from net.  The equivalent of Oracle's DECODE function in SQL Server is the CASE function.

CASE input_expression
      WHEN when_expression_1 THEN result_expression_1
  [ WHEN when_expression_2 THEN result_expression_2
      WHEN when_expression_n THEN result_expression_n ]
  [ ELSE else_result_expression ]
END

Example:-

DECODE can be conveniently simulated using the T-SQL CASE expression.

SELECT Sport,
CASE Sport
WHEN 'Cricket' THEN 'England'
WHEN 'Hockey' THEN 'India'
WHEN 'Base Ball' THEN 'America'
ELSE NULL
END AS 'Originating Country'
FROM Sports    cheers,sundar

[Anon Anon]

Hi Sundar,

I can't seem to find the attachments you are refering to? I have a similar needs. Please help.

Vani

[Naman Patel]

I dont see any attached document.. How to get it?

[Anon Anon]

Hi,

I have developed this in 7.5.2 to suit our own needs.we used it for our custom reports.
The main reason for doing it is explained above Post.Issues with Clarity OBS Browse.

I don't know whether will help you but i have enclosed it.

To
CA Admin-->Where are the attachments gone ,is all the attachments in old ca forums is gone??????????????

cheers,
sundar

Attachment(s)

Custom Lookup for OBS.zip   42 KB 1 version

[Martin Snizek]

Hello,

 

we have got a problem with filtering in this lookupu in browse window. When I try to filter for RT_OBS field it always returns all values. Please have you got any idea how to solve this?

 

Our Clarity version is 13.2.0.472 06 14

 

Regards,

Martin

[Martin Snizek]

Hello,

 

I found the solution, there is missing WHERE @FILTER@ in the lookup query. After adding this to query it works as I expect.

 

Regards,

Martin