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.