Inclident List call_req Table

[Legacy User]

Hi, 

 

I need some help with a sql query which will display all Incidents call_req.type = 'I' . Below are the columns which I am looking for. Any help will be greatly appreciated.

 

 

Thanks

[Kyle_R]

Hello Arnab,

 

• Where are you running the query (SQL Management Studio?)?
• Where did that list of columns come from? (It's not the SQL db names, which is probably where you're getting stuck.)
• What bit did you get stuck on? The query you tried and the result are always useful.

 

Here is a query that works:

 

SELECT ref_num, category, priority <<Keep adding in the list here>>

FROM mdb.dbo.call_req

WHERE call_req.type = 'I'

 

You need to build out the SELECT list.

Notice that your original list of names is using a mapped name, and not the SQL names. For example, you have "Area" meaning "Incident Area", whereas the SQL column is "category."

 

If you know the source of that original list, then you can work backwards to see what fields it is calling.

(Or use the DocOps documentation to get the field names. Or use "bop_sinfo" against the cr object.)

 

But a simpler "good enough" approach is just to right click in SQL against the "call_req" table name and accept the default "SELECT Top 1000 Rows" option.

This will generate a query with all of the column names, and then you can use that as your base.

With a bit of guesswork, you should be able to map your original list to the SQL names pretty quickly.

Here is the result:

/****** Script for SelectTopNRows command from SSMS ******/
SELECT TOP 1000 [id]
,[persid]
,[ref_num]
,[summary]
,[description]
,[status]
.

.

.
,[actual_comp_date]
,[current_wrkshft]
FROM [mdb].[dbo].[call_req]

 

Hope that helps.

 

Thanks, Kyle_R.

[Legacy User]

Hi Kyle,

 

The screenshot is from BOXI where I have a similar report built. I am looking to build this in Oracle Database. I need information related to User Information (Assignee etc which is not part of the call req table.

 

Thanks,

 

Arnab

[Kyle_R]

These two tables hold Contact information:

 

• ca_contact - Contact information common across CA products
• usp_contact - Contact information specific to the Service Management products

 

And they are linked to each other by the contact_uuid field.

 

There is an Assignee field in the call_req table, and it also uses the contact_uuid value.
So if you want to pull the Assignee name as seen through the SDM web client, you'll need to reference the other tables to get what you need.

Your BOXI report query will be doing something similar in the background.

 

You should see this documentation here for the links:

Call Request - CA Service Management - 17.2 - CA Technologies Documentation 

 

The details for Oracle are the same as for SQL for the most part.

 

Kyle_R.

[Legacy User]

Hi Arnab,

 

There is also a OOTB provided view, namely: dbo.View_Request. This will give you the information you need and more.

 

 

You can use this as the baseline as it will save you a lot of time and twig it here and there to omit the columns you are not interested in.

 

===

Kind Regards,

Brian

[Kyle_R]

Cool!