Maximo SQL BiRT reporting multiple columns of alnvalue

Question

I have a BiRT report to do with a SQL Server database. I need to have in this report multiple columns of Assetspec.alnvalue, filter by the Assetspec.assetattrid of the same table.

For those who don't know Maximo, alnvalue is like an "other thing" column. So if I do:

Select assetspec.alnvalue AS SetOne
  from assetspec 
  where assetspec.assetattrid = 'something'

I get all the information of something. I my case I need to have in separate columns 5 set of information "from" assetattrid and all of that in one query...

Is that even possible?

Answer 1

There are two ways to do this: with correlated subqueries or joins. Generally speaking, the subquery will cause problems if your subquery returns more than one row. Also, generally speaking, the join option will open more options to you for other things you can do in your query (for example, grouping).

Here is what your query looks like if we add a correlated subquery to return information about the 'something else' attribute.

Select assetspec.alnvalue AS SetOne
    ,(select as2.alnvalue 
        from assetspec as2
        where as2.assetnum = assetspec.assetnum
            and as2.siteid = assetspec.siteid
            and as2.assetattrid = 'something else'
        ) As SetTwo
from assetspec 
where assetspec.assetattrid = 'something'

And here is what your query looks like if we add joining to return that information.

Select assetspec.alnvalue AS SetOne
    ,as2.alnvalue As SetTwo
from assetspec 
    join assetspec as2
        on as2.assetnum = assetspec.assetnum
        and as2.siteid = assetspec.siteid
        and as2.assetattrid = 'something else'
where assetspec.assetattrid = 'something'

I hope that helps.