CA Service Management

Dear Team,

I need a report for Change order from MS SQL DATABASE showing :

• CO ref number
• Summary
• Category
• Status
• Phase
• Assigned to
• Change type
• Open date
• Close date
• Projected violation 
• Group 
• Risk
• Schedule Start date
• Schedule end date

My query:

SELECT chg.chg_ref_num as ChangeOrder_ref_num,

chg.Summary,

chg.Category,

chg.Status,

chg.Open_date,

chg.Close_date,

chg.Assignee,

chg.Risk,

chg.Sched_start_date,

chg.Sched_end_date

FROM chg;

Please help me for getting the Configuration items attached with Change order ticket in report along with phase, change type,Violation,Group.

Regards,

Jagatjyoti Dash

Hello  dasja02,

I see several request of building queries, I will suggest you to have a look at the reference documentation for the related data dictionary.

This is a multi-relation using a cross over table that maintain the relations (usp_lrel_asset_chgnr) 

Then you will have to link the chg table and the ca_owned_resource trough this table to retrieve the info you need 

Below is a example on how to build such query:

SELECT chg.chg_ref_num, ca_owned_resource.resource_name

FROM ca_owned_resource INNER JOIN

usp_lrel_asset_chgnr ON ca_owned_resource.own_resource_uuid = usp_lrel_asset_chgnr.nr RIGHT OUTER JOIN

chg ON usp_lrel_asset_chgnr.chg = chg.id

You will need to apply same technics for workflow task or others multi relation link.

Hope this help

/J

Hi ,

My previous query works fine.I need only change type, group,Configuration item and assignee field.

Regards,

Jagatjyoti Dash

Hi Jagatjyoti,

Run the below command:

bop_sinfo -dq chg

This will show you the tables you need to cross reference to get the rest of the data.

===

Kind Regards,

Brian

Understand that but may you want to get the CI's you will need to adapt your query with join to other tables.

My example show you how to do for the CI's

Learning by doing.... that why I let you do the home work

If you finally fully get stuck we will continue to help

/J

see also Brian Mathato to learn more on the SDM schema

Hi ,

I am not getting the column for joining, please help.

Regards,

Jagatjyoti Dash

Column for change type is chgtype

Column for group is group_id

Column for assignee is assignee

dasja02 

Do you have any further questions on this topic?

If not, please mark one of the answers provided as correct so that this thread can be closed.

Hi Paul,

Let me check the codes. 

Regards,

Jagatjyoti Dash