Service Desk Practitioners Forum

SQL report required

Go to solution
Peter Dent
Outstanding Contributor.

SQL report required


We have an Oracle database and I use Toad to access it.

I have a need for a report which I know cannot be retrieved via the GUI and we don't have a 3rd party reporting tool, (yet).

I was hoping some of the SQL experts out there would be able to tell me how to write a query that will return the info required?

I have made a start but am now stuck.

What is required is:

Config Item information, for CI's that have had their Status changed to either "Disposed" or "Removed awaiting disposal", from 21/05/07 onwards.

The data required is Searchcode, Date of Status Change, Serial Number, Location, Status and Category.

This is the SQL I have so far:

select cit_searchcode, cit_serialnumber,reg_created from itsm_configuration_items join itsm_historylines_config
on cit_oid = hcf_cit_oid where REG_CREATED BETWEEN TO_DATE('05/21/2007 00:00:01', 'MM/DD/YYYY HH24:MI:SS')
AND TO_DATE('08/29/2007 12:00:00', 'MM/DD/YYYY HH24:MI:SS')
and hcf_subject like '%"Removed awaiting disposal".%' or hcf_subject like '%to "Disposed".%'

This works but is returning duplicate rows because there is more than one history line for some CI's.

It only returns part of the info because I don't know how to link to the other tables.

Can anybody help or give some pointers?

George M. Meneg
Acclaimed Contributor.

Re: SQL report required

Hello Peter,

A visit to could help you solve your questions about fields, tables, columns and their relations.

In your case, status is in the column cit_sta_oid and must be joined on cit.cit_sta_oid=rep_codes_text.rct_rcd_oid where rep_codes_text.rct_lng_oid=1033 (or your language locale)

For location it's the column cit.cit_loc_oid
and must be joined to itsm_locations on cit_loc_oid=loc_oid
menes fhtagn
Peter Dent
Outstanding Contributor.

Re: SQL report required


Thanks, I'm fairly new to SQL so pointers like this are quite valuable to me.

I'm also learning about the relationships in the HPOV DB.