Topic
IC4NOTICE: developerWorks Community will be offline May 29-30, 2015 while we upgrade to the latest version of IBM Connections. For more information, read our upgrade FAQ.
No replies
Kendler
Kendler
5 Posts
ACCEPTED ANSWER

Pinned topic create a VIEW from 2 (or more) tables

‏2012-05-30T10:33:22Z |
Hi All,
I'm trying to create a view for the WORKLOG and TICKET table.
this table will include some (or all) fields from the WORKLOG table and some of the fields from the TICKET table (including a calculated field for SLA progress).

using Database Configuration i've created a new object.
  • this object extends WORKLOG (a VIEW must extend an object).
  • the attributes for this view were pulled automatically from the WORKLOG table.
  • the FROM field is TICKET,WORKLOG
  • the WHERE is something for my needs in implementation :
TICKETID = recordkey
and WORKLOGID in ( select WORKLOGID from MAXIMO.WORKLOG WL2 where WORKLOG.RECORDKEY = WL2.RECORDKEY and WORKLOG.CREATEBY = WL2.CREATEBY order by CREATEDATE desc fetch first 1 rows only )
  • the SELECT is the following:
WORKLOG.ROWSTAMP,
WORKLOG.RECORDKEY,
WORKLOG.WORKLOGID,
WORKLOG.CREATEBY,
WORKLOG.CREATEDATE,
WORKLOG.CLIENTVIEWABLE,
WORKLOG.DESCRIPTION,
WORKLOG.SITEID,
WORKLOG.ORGID,
WORKLOG.MODIFYBY,
WORKLOG.MODIFYDATE,
WORKLOG.CLASS,
WORKLOG.LANGCODE,
WORKLOG.HASLD,
TICKET.TICKETID,
CASE
WHEN (((current timestamp-CREATIONDATE)/(TARGETFINISH-CREATIONDATE)) >= 1 )
THEN 100
ELSE ((current timestamp-CREATIONDATE)/(TARGETFINISH-CREATIONDATE))*100
END as SLAPRECENT,
TICKET.ASSETNUM

(SLAPRECENT is a column added to TICKET table to display the SLA progress - as displayed here http://blog.taic.net/SAM/?p=382. this works).
the real problem is the following:
only the attributes (columns) that are persistent ( added to the view when the extend object was selected) really have values/data in them when trying to use this data in the SR/INC application.
the TICKETID, SLAPRECENT and ASSETNUM are not displayed for some reason.

when trying to query the DB (using control center) on the same VIEW - it works. the only problem is in maximo.

I guess this has something to do with the fact that the attributes (columns) are not persistent.

how can I add a persistent attribute?
actually, I only need to know how I can create a VIEW that will contain data from 2 tables?

thanks,
Alon