Service Desk Practitioners Forum
cancel
Showing results for 
Search instead for 
Did you mean: 

SQL Statement - Change status

SOLVED
Go to solution
Highlighted
Fepelof_1
Regular Collector

SQL Statement - Change status

Hi there,

I´m facing some difficultes trying to link a change with its status. Does anyone knows which SQL query to use in order to bring changes and their respective statuses ?

thanks
Fep
6 REPLIES
Ramaprasad N
Esteemed Contributor

Re: SQL Statement - Change status

From the top of my head, RFC details are stored in ITSM_Changes table. This will have a link to the REP_CODES table, for all the code type fields (Status, category, closure code, etc). And the REP_CODES table will be related to the REP_CODES_TEXT table. REP_CODES_TEXT table will hold the Status text, in your case. So to get the changes and their status, select the changes from ITSM_Changes and then the status from REP_CODES_TEST. Remember to relate these tables in your sql statement.

-Ram
David Borojevic
Frequent Visitor

Re: SQL Statement - Change status

And you might need to join in Rep Codes Locale if you use another Language?.

There is a post that has Entity Relationship Visio diagrams on here somewhere - but it might take a few searches - sorry I can't remember it.

Cheers
Ivan Sakharov
Frequent Visitor
Solution

Re: SQL Statement - Change status

Hi

I have the same script for Incidents. I think that you can transform it for Changes without problems.

SELECT T1.INC_ID AS [ID]
,T3.RCT_NAME AS [Status]
FROM
ITSM_INCIDENTS T1
,REP_CODES T2
,REP_CODES_TEXT T3
WHERE (T3.RCT_LNG_OID = 1049) --this condition
--is necessary if you use localization.
--1049 - Russian code page
and T2.RCD_OID = T1.INC_STA_OID
and T3.RCT_RCD_OID = T2.RCD_OID
Fepelof_1
Regular Collector

Re: SQL Statement - Change status

thanks for all,

another point is that I want to link the Change Status with its Closure Code. Is the Closure Code at the REP_CODES table or at the ITSM_CODES table ??
I´m asking this because I don´t have the complete Data Dictionary of HP SD.

thanks
Fep
Ivan Sakharov
Frequent Visitor

Re: SQL Statement - Change status

Hi

ITSM_Changes.CHA_CLOSURECODE - it is OID of closure code. If you want to see the text, than you must link it to the table REP_CODES and than link to REP_CODES_TEXT as I posted in previous message.

select REP_CODES_TEXT.RCT_NAME
...
WHERE REP_CODES.RCD_OID = ITSM_Changes.CHA_CLOSURECODE
and REP_CODES_TEXT.RCT_RCD_OID = REP_CODES.RCD_OID

As to Data Dictionary see the attach in this post and next
Ivan Sakharov
Frequent Visitor

Re: SQL Statement - Change status

another visio diagram
//Add this to "OnDomLoad" event