Maximo Open Forum

Hi All,

I have created the custom object structure (with same relationships as below)  and I want to add this query in the oslcclause ( testing this as data will be retreived by an integrated app) , the query works ok in DB.

Integrated app will fetch data based on  B.STATUSDATE.

Can this query work as this has 2 joins?

select b.BATCHTOTAL, b.BILLBATCHNUM, b.CUSTOMER ,c.wdcustomerid , b.DESCRIPTION, b.revenuefinancialperiod, b.orgid, b.status, a.jhg_projectid, a.glaccount,b.statusdate 
from pluspcustomer c 
left outer join 
pluspagreement a on  c.customer = a.customer and a.status = 'APPR'
 join 
pluspbillbatch b on a.customer = b.customer and a.orgid = b.orgid and b.agreement = a.agreement
and
b.revenuefinancialperiod >=  '202305' and b.revenuefinancialperiod <=  '202305'

But via postman the below error is coming if I add this query or as such any parts of this query. (attached) 

Has anyone tried similar config beofre any pointers would be helpful.

Note: I have tried similar setup with one and two OS's (one DB table and 2 DB tables') which is working fine. 

Thanks

Amit

osclause is similar to a saved query in WOTRACK or another application. It's only a where clause for the main object of the object structure. IE if your main object in the object structure is pluspcustomer think of the Maximo framework automatically doing:

"select * from pluspcustomer where "

You then take whatever you have in the OS clause and add it to the end of it. If it will run on the database server, you're good, otherwise you have a problem.

You on the other hand are trying to essentially build a view, not provide a where clause. You can create a view in Maximo like this (though I imagine you don't want the filter of financial periods in the view) and then create a new object structure that references this. You could also build your OSLC select and the where clauses to include the child tables but the structure wouldn't be the same as if you had the object structure against a view. What I mean by that is everything is top level, you would get something like:

{
"wdcustomerid":customerid,
"batchtotal":total,
"billbatchnum":billbatchnum,
"customer":customer,
....
}

But if you use child objects using the oslc.select like oslc.select=wdcustomerid,rel.pluspagreement{rel.pluspbillbatch{batchtotal,billbatchnum,customer..., you would get it like:

{
"wdcustomerid":customerid,
"pluspagreement":[{
"pluspbillbatch":[{
"batchtotal":total,
"billbatchnum":billbatchnum,
"customer":customer,
....
}]
}]
}

This is because each of those child objects could be a one to many. If it was a 1:1, you could alias the attributes to move them up to the top level 

Hi All,

I have created the custom object structure (with same relationships as below)  and I want to add this query in the oslcclause ( testing this as data will be retreived by an integrated app) , the query works ok in DB.

Integrated app will fetch data based on  B.STATUSDATE.

Can this query work as this has 2 joins?

select b.BATCHTOTAL, b.BILLBATCHNUM, b.CUSTOMER ,c.wdcustomerid , b.DESCRIPTION, b.revenuefinancialperiod, b.orgid, b.status, a.jhg_projectid, a.glaccount,b.statusdate 
from pluspcustomer c 
left outer join 
pluspagreement a on  c.customer = a.customer and a.status = 'APPR'
 join 
pluspbillbatch b on a.customer = b.customer and a.orgid = b.orgid and b.agreement = a.agreement
and
b.revenuefinancialperiod >=  '202305' and b.revenuefinancialperiod <=  '202305'

But via postman the below error is coming if I add this query or as such any parts of this query. (attached) 

Has anyone tried similar config beofre any pointers would be helpful.

Note: I have tried similar setup with one and two OS's (one DB table and 2 DB tables') which is working fine. 

Thanks

Amit