Need help with queries

[mprisco]

We are using actuate 7 with Maximo 5.2. Our maximo application has been heavily customized. Maximo supplies an actuate component with code for the obtainselectstatement method of the datastream. When a report is run from maximo (Oracle db), the method appends the where clause that comes in from maximo to the whereclause in actuate. The problem is that the reports often join several tables and the where clause from maximo isn't fully qualified so you wind up with ambiguous field names. We were trying to parse the statement and insert the table or alias names but the statements are too complex and its too uncertain to try to figure out what all of the possible where clause statements might be. So, I'm trying to figure a way around this. I was thinking that maybe this could be done with a subreport, but I can't get it to work. I would think that we would need to run the query as is from Maximo and then do our joins based on what's returned from the DB. Is there some better way to do this? Any suggestions?

Thanks!
Margaret

 

[Kebabmeister]

Hi Margaret

You need to do some work in the ObtainSelectStatement method by the sound of it. Actuate builds the SQL statement from the selections you make in the data source editor. Any additions to the clauses of the statement can be made in ObtainSelectStatement. The ambiguous field names will probably come from the fact that more than one table has fields of the same name. If you post the statement it may help.

 

[PeteWalburn]

Margaret,

The way that I have got around the ambiguous field name problem is to have a simple query in the SQLquerySource - such as:

SELECT DESCRIPTION FROM SITE WHERE SITEID=:mroSite

Then I have a subreport with it's own SqlQuerySource with multiple tables. You will still need to override the ObtainSelectStatement function and parse the where clause if it is possible to still have ambiguous fieldnames within the tables of your query. You could have multiple subreports with simple queries in each subreport (depending on your report requirements).

I believe that there is a patch for Maximo (patch 4) I think that also fixes the problem by adding the tablename to the fields in the where clause.

Pete

 

[Kebabmeister]

Field qualifiers are added via options on the "Query Editor" tab. You should select owner.table in the "Table And View Name Qualification" section.

I must confess, I use Actuate 7 on Maximo 5.2 and have never had a situation where I need to adopt any special measures as described.

 

[mprisco]

Thanks - I understand why the ambiguity exists - Maximo constructs the sql with a single table so the where clause that is transferred to Actuate becomes ambiguous when tacked onto a sql statement with multiple tables. The problem is how to fix it. Maximo tells us to parse the statement and add the tablename to the fields that need it, but there are too many possible ways that the statement could be constructed.

For example, we parse for " location " and for "(location" which currently works but if someone modifies Maximo again and takes out the space in
location = locvar
we will not find it and will not append the tablename to the field and it becomes ambiguous and the db will barf.

I'd like to somehow use the where clause from Maximo and grab a result set and then use that set to get the correct data for the report but with thousands of rows returned, the overhead would be too much to have 2 db queries. I tried using a subreport - putting the maximo query in the main datastream and then the query for the report in the datastream of the subquery (which has a join in it) but its not working.... any other ideas would be appreciated.

 

[mprisco]

Thanks for both of your responses. The subreport is what I was thinking of trying. Since the problem is only the Maximo supplied where clause and not the one that I create (I beleive the setting in the query editor would only affect queries created in Actuate, not those modified in Obtainselectstatement, correct? Can someone explain how subreports work? Do they do a query for each report/subreport? How are they joined?

Thanks so much
Margaret