I have integrated Maximo 7.6 and Cognos.
I would like to create a query the shows ma all Assets that are not in use for a specific time period.
I have an asset table with assetnum as the key and a reservation table that has assetnum, startdate and enddate fields that specifies when an asset is scheduled.
I want to query all assets that are not scheduled for a given period between ?START_DATE? and ?END_DATE?.
In SQL it looks like
[-------------------------------------- scheduled---------------------------------------------------------------...-]
select assetnum from assets where assetnum not in (select assetnum from reserved where ?START_DATE? between startdate and enddate or ?END_DATE? between startdate and enddate)
My question is where/how do I add the nested select statement? In the framework manager or in the report studio?
If so how can I add a nested select? It does not seem to like the keyword select in the filter statements.