This is an archive of the Maximo Yahoo Community. The content of this pages may be a sometimes obsolete so please check post dates.
Thanks to the community owner Christopher Wanko for providing the content.
I am working on a method to have tie back how soon PM's should be done by autopopulating the WORKORDER.SCHEDFINISH field based on a PM's frequency and frequnit. I can create a view in Query Analyzer with the following statement:
select workorder.wonum, workorder.status, workorder.pmnum, workorder.jpnum, workorder.location, workorder.assetnum, pm.frequency, pm.frequnit,
case
when pm.frequnit='WEEKS' and pm.frequency < 3 then pm.frequency * 2
when pm.frequnit='WEEKS' and pm.frequency >= 3 and pm.frequency <=12 then pm.frequency * 1.25
when pm.frequnit='WEEKS' and pm.frequency > 12 and pm.frequency <= 26 then pm.frequency * 1
when pm.frequnit='WEEKS' and pm.frequency > 26 then pm.frequency * 0.85
when pm.frequnit='MONTHS' and pm.frequency <= 5 then pm.frequency * 5
when pm.frequnit='MONTHS' and pm.frequency >= 6 and pm.frequency <= 12 then pm.frequency * 4
when pm.frequnit='MONTH' and pm.frequency > 12 then pm.frequency * 30 * 0.1
when pm.frequnit='YEARS' and pm.frequency <= 1 then pm.frequency * 45
when pm.frequnit='YEARS' and pm.frequency > 1 then pm.frequency * 365 * 0.1
ELSE 7
end 'pm_schdate'
from workorder join pm on (workorder.pmnum=pm.pmnum and workorder.siteid=pm.siteid and pm.status='ACTIVE')
where workorder.istask='0' and workorder.historyflag='0' and workorder.pmnum is not null
The Query Analyzer will create this into a view and be perfect. But I want to creat this view so it is accessible within Maximo via DB Config.
Anyone got any pointers in create view with DB config?
Try creating the view first, then in DBConfig, add a new object and give it the same name. This should "import" the definition. I've done this for tables before, never tried it for a view.
-- Sean Clark-McCarthy
From: MAXIMO@yahoogroups.com [mailto:MAXIMO@yahoogroups.com] On Behalf Of Jason Verly
Sent: Wednesday, December 21, 2011 5:22 PM
To: MAXIMO@yahoogroups.com
Subject: [MAXIMO List] Creating custom database view in DB config (MX 7.1.x)
I am working on a method to have tie back how soon PM's should be done by autopopulating the WORKORDER.SCHEDFINISH field based on a PM's frequency and frequnit. I can create a view in Query Analyzer with the following statement:
select workorder.wonum, workorder.status, workorder.pmnum, workorder.jpnum, workorder.location, workorder.assetnum, pm.frequency, pm.frequnit,
case
when pm.frequnit='WEEKS' and pm.frequency < 3 then pm.frequency * 2
when pm.frequnit='WEEKS' and pm.frequency >= 3 and pm.frequency <=12 then pm.frequency * 1.25
when pm.frequnit='WEEKS' and pm.frequency > 12 and pm.frequency <= 26 then pm.frequency * 1
when pm.frequnit='WEEKS' and pm.frequency > 26 then pm.frequency * 0.85
when pm.frequnit='MONTHS' and pm.frequency <= 5 then pm.frequency * 5
when pm.frequnit='MONTHS' and pm.frequency >= 6 and pm.frequency <= 12 then pm.frequency * 4
when pm.frequnit='MONTH' and pm.frequency > 12 then pm.frequency * 30 * 0.1
when pm.frequnit='YEARS' and pm.frequency <= 1 then pm.frequency * 45
when pm.frequnit='YEARS' and pm.frequency > 1 then pm.frequency * 365 * 0.1
ELSE 7
end 'pm_schdate'
from workorder join pm on (workorder.pmnum=pm.pmnum and workorder.siteid=pm.siteid and pm.status='ACTIVE')
where workorder.istask='0' and workorder.historyflag='0' and workorder.pmnum is not null
The Query Analyzer will create this into a view and be perfect. But I want to creat this view so it is accessible within Maximo via DB Config.
Anyone got any pointers in create view with DB config?
I can confirm that this works for views as well.
On Thu, Dec 22, 2011 at 10:08 AM, Sean Clark-McCarthy <
smccarthy@cohesivesolutions.com> wrote:
> **
>
>
> Try creating the view first, then in DBConfig, add a new object and give
> it the same name. This should "import" the definition. I've done this for
> tables before, never tried it for a view.
>
> -- Sean Clark-McCarthy
>
> From: MAXIMO@yahoogroups.com [mailto:MAXIMO@yahoogroups.com] On Behalf Of
> Jason Verly
> Sent: Wednesday, December 21, 2011 5:22 PM
> To: MAXIMO@yahoogroups.com
> Subject: [MAXIMO List] Creating custom database view in DB config (MX
> 7.1.x)
>
>
> I am working on a method to have tie back how soon PM's should be done by
> autopopulating the WORKORDER.SCHEDFINISH field based on a PM's frequency
> and frequnit. I can create a view in Query Analyzer with the following
> statement:
>
> select workorder.wonum, workorder.status, workorder.pmnum,
> workorder.jpnum, workorder.location, workorder.assetnum, pm.frequency,
> pm.frequnit,
>
> case
>
> when pm.frequnit='WEEKS' and pm.frequency < 3 then pm.frequency * 2
> when pm.frequnit='WEEKS' and pm.frequency >= 3 and pm.frequency <=12 then
> pm.frequency * 1.25
> when pm.frequnit='WEEKS' and pm.frequency > 12 and pm.frequency <= 26 then
> pm.frequency * 1
> when pm.frequnit='WEEKS' and pm.frequency > 26 then pm.frequency * 0.85
> when pm.frequnit='MONTHS' and pm.frequency <= 5 then pm.frequency * 5
> when pm.frequnit='MONTHS' and pm.frequency >= 6 and pm.frequency <= 12
> then pm.frequency * 4
> when pm.frequnit='MONTH' and pm.frequency > 12 then pm.frequency * 30 * 0.1
> when pm.frequnit='YEARS' and pm.frequency <= 1 then pm.frequency * 45
> when pm.frequnit='YEARS' and pm.frequency > 1 then pm.frequency * 365 * 0.1
> ELSE 7
>
> end 'pm_schdate'
>
> from workorder join pm on (workorder.pmnum=pm.pmnum and
> workorder.siteid=pm.siteid and pm.status='ACTIVE')
>
> where workorder.istask='0' and workorder.historyflag='0' and
> workorder.pmnum is not null
>
> The Query Analyzer will create this into a view and be perfect. But I want
> to creat this view so it is accessible within Maximo via DB Config.
>
> Anyone got any pointers in create view with DB config?
>
>
>
>
>
--
Thanks,
Matt Holland
I'm not sure it will import complex views, it's worth a try for sure though.
One of the old "dirty tricks" is to create a table in Maximo that matches
your proposed view structure, create it using dbconfig and you have what
Maximo sees is an "official" object. You can then drop the table and create
a view with the same name - Maximo is blissfully unaware of what you did as
long as you only read data and never do any updates. It should be noted
that this is not in line with best practices and should only be a last
resort.
You can build some pretty complex view via database configuration, it just
takes a lot of trial and error. I have built views that use functions to
calculate values that they return, you can achieve some complex logic but
it is far from straightforward.
On Thu, Dec 22, 2011 at 7:08 AM, Sean Clark-McCarthy <
smccarthy@cohesivesolutions.com> wrote:
> **
>
>
> Try creating the view first, then in DBConfig, add a new object and give
> it the same name. This should "import" the definition. I've done this for
> tables before, never tried it for a view.
>
> -- Sean Clark-McCarthy
>
> From: MAXIMO@yahoogroups.com [mailto:MAXIMO@yahoogroups.com] On Behalf Of
> Jason Verly
> Sent: Wednesday, December 21, 2011 5:22 PM
> To: MAXIMO@yahoogroups.com
> Subject: [MAXIMO List] Creating custom database view in DB config (MX
> 7.1.x)
>
>
> I am working on a method to have tie back how soon PM's should be done by
> autopopulating the WORKORDER.SCHEDFINISH field based on a PM's frequency
> and frequnit. I can create a view in Query Analyzer with the following
> statement:
>
> select workorder.wonum, workorder.status, workorder.pmnum,
> workorder.jpnum, workorder.location, workorder.assetnum, pm.frequency,
> pm.frequnit,
>
> case
>
> when pm.frequnit='WEEKS' and pm.frequency < 3 then pm.frequency * 2
> when pm.frequnit='WEEKS' and pm.frequency >= 3 and pm.frequency <=12 then
> pm.frequency * 1.25
> when pm.frequnit='WEEKS' and pm.frequency > 12 and pm.frequency <= 26 then
> pm.frequency * 1
> when pm.frequnit='WEEKS' and pm.frequency > 26 then pm.frequency * 0.85
> when pm.frequnit='MONTHS' and pm.frequency <= 5 then pm.frequency * 5
> when pm.frequnit='MONTHS' and pm.frequency >= 6 and pm.frequency <= 12
> then pm.frequency * 4
> when pm.frequnit='MONTH' and pm.frequency > 12 then pm.frequency * 30 * 0.1
> when pm.frequnit='YEARS' and pm.frequency <= 1 then pm.frequency * 45
> when pm.frequnit='YEARS' and pm.frequency > 1 then pm.frequency * 365 * 0.1
> ELSE 7
>
> end 'pm_schdate'
>
> from workorder join pm on (workorder.pmnum=pm.pmnum and
> workorder.siteid=pm.siteid and pm.status='ACTIVE')
>
> where workorder.istask='0' and workorder.historyflag='0' and
> workorder.pmnum is not null
>
> The Query Analyzer will create this into a view and be perfect. But I want
> to creat this view so it is accessible within Maximo via DB Config.
>
> Anyone got any pointers in create view with DB config?
>
>
>
>
>
Depending on the size and complexity of the view you might run into size
constraints on the VIEWWHERE, VIEWSELECT, and VIEWFROM fields which would
require changing their length. You may also need to correct what the
system populates in these fields when configuring an imported object. I've
noticed with the more complex views it does a poor job at determining the
SELECT/FROM/WHERE from the view definition. Another option is to just
select from the complex database view with the view defined in the maximo
metadata.
On Thu, Dec 22, 2011 at 10:34 AM, Chris Lawless <lawlessc@gmail.com> wrote:
> I'm not sure it will import complex views, it's worth a try for sure
> though.
>
> One of the old "dirty tricks" is to create a table in Maximo that matches
> your proposed view structure, create it using dbconfig and you have what
> Maximo sees is an "official" object. You can then drop the table and create
> a view with the same name - Maximo is blissfully unaware of what you did as
> long as you only read data and never do any updates. It should be noted
> that this is not in line with best practices and should only be a last
> resort.
>
> You can build some pretty complex view via database configuration, it just
> takes a lot of trial and error. I have built views that use functions to
> calculate values that they return, you can achieve some complex logic but
> it is far from straightforward.
>
> On Thu, Dec 22, 2011 at 7:08 AM, Sean Clark-McCarthy <
> smccarthy@cohesivesolutions.com> wrote:
>
> > **
> >
> >
> > Try creating the view first, then in DBConfig, add a new object and give
> > it the same name. This should "import" the definition. I've done this for
> > tables before, never tried it for a view.
> >
> > -- Sean Clark-McCarthy
> >
> > From: MAXIMO@yahoogroups.com [mailto:MAXIMO@yahoogroups.com] On Behalf
> Of
> > Jason Verly
> > Sent: Wednesday, December 21, 2011 5:22 PM
> > To: MAXIMO@yahoogroups.com
> > Subject: [MAXIMO List] Creating custom database view in DB config (MX
> > 7.1.x)
> >
> >
> > I am working on a method to have tie back how soon PM's should be done by
> > autopopulating the WORKORDER.SCHEDFINISH field based on a PM's frequency
> > and frequnit. I can create a view in Query Analyzer with the following
> > statement:
> >
> > select workorder.wonum, workorder.status, workorder.pmnum,
> > workorder.jpnum, workorder.location, workorder.assetnum, pm.frequency,
> > pm.frequnit,
> >
> > case
> >
> > when pm.frequnit='WEEKS' and pm.frequency < 3 then pm.frequency * 2
> > when pm.frequnit='WEEKS' and pm.frequency >= 3 and pm.frequency <=12 then
> > pm.frequency * 1.25
> > when pm.frequnit='WEEKS' and pm.frequency > 12 and pm.frequency <= 26
> then
> > pm.frequency * 1
> > when pm.frequnit='WEEKS' and pm.frequency > 26 then pm.frequency * 0.85
> > when pm.frequnit='MONTHS' and pm.frequency <= 5 then pm.frequency * 5
> > when pm.frequnit='MONTHS' and pm.frequency >= 6 and pm.frequency <= 12
> > then pm.frequency * 4
> > when pm.frequnit='MONTH' and pm.frequency > 12 then pm.frequency * 30 *
> 0.1
> > when pm.frequnit='YEARS' and pm.frequency <= 1 then pm.frequency * 45
> > when pm.frequnit='YEARS' and pm.frequency > 1 then pm.frequency * 365 *
> 0.1
> > ELSE 7
> >
> > end 'pm_schdate'
> >
> > from workorder join pm on (workorder.pmnum=pm.pmnum and
> > workorder.siteid=pm.siteid and pm.status='ACTIVE')
> >
> > where workorder.istask='0' and workorder.historyflag='0' and
> > workorder.pmnum is not null
> >
> > The Query Analyzer will create this into a view and be perfect. But I
> want
> > to creat this view so it is accessible within Maximo via DB Config.
> >
> > Anyone got any pointers in create view with DB config?
> >
> >
> >
> >
> >
>
>
>
>
>
>
> ------------------------------------
>
> Email addresses you might need:
> Posting: MAXIMO@yahoogroups.com
> Join : MAXIMO-subscribe@yahoogroups.com
> Leave : MAXIMO-unsubscribe@yahoogroups.com
> Cry : MAXIMO-owner@yahoogroups.com
> Group : http://groups.yahoo.com/group/MAXIMOYahoo! Groups Links
>
>
>
>
--
Thanks,
Matt Holland
Thanks everyone for the suggestions! I'll test them out over the holiday weekend and report back next week.
--- In MAXIMO@yahoogroups.com, Sean Clark-McCarthy <smccarthy@...> wrote:
>
> Try creating the view first, then in DBConfig, add a new object and give it the same name. This should "import" the definition. I've done this for tables before, never tried it for a view.
>
> -- Sean Clark-McCarthy
>
> From: MAXIMO@yahoogroups.com [mailto:MAXIMO@yahoogroups.com] On Behalf Of Jason Verly
> Sent: Wednesday, December 21, 2011 5:22 PM
> To: MAXIMO@yahoogroups.com
> Subject: [MAXIMO List] Creating custom database view in DB config (MX 7.1.x)
>
>
>
> I am working on a method to have tie back how soon PM's should be done by autopopulating the WORKORDER.SCHEDFINISH field based on a PM's frequency and frequnit. I can create a view in Query Analyzer with the following statement:
>
> select workorder.wonum, workorder.status, workorder.pmnum, workorder.jpnum, workorder.location, workorder.assetnum, pm.frequency, pm.frequnit,
>
> case
>
> when pm.frequnit='WEEKS' and pm.frequency < 3 then pm.frequency * 2
> when pm.frequnit='WEEKS' and pm.frequency >= 3 and pm.frequency <=12 then pm.frequency * 1.25
> when pm.frequnit='WEEKS' and pm.frequency > 12 and pm.frequency <= 26 then pm.frequency * 1
> when pm.frequnit='WEEKS' and pm.frequency > 26 then pm.frequency * 0.85
> when pm.frequnit='MONTHS' and pm.frequency <= 5 then pm.frequency * 5
> when pm.frequnit='MONTHS' and pm.frequency >= 6 and pm.frequency <= 12 then pm.frequency * 4
> when pm.frequnit='MONTH' and pm.frequency > 12 then pm.frequency * 30 * 0.1
> when pm.frequnit='YEARS' and pm.frequency <= 1 then pm.frequency * 45
> when pm.frequnit='YEARS' and pm.frequency > 1 then pm.frequency * 365 * 0.1
> ELSE 7
>
> end 'pm_schdate'
>
> from workorder join pm on (workorder.pmnum=pm.pmnum and workorder.siteid=pm.siteid and pm.status='ACTIVE')
>
> where workorder.istask='0' and workorder.historyflag='0' and workorder.pmnum is not null
>
> The Query Analyzer will create this into a view and be perfect. But I want to creat this view so it is accessible within Maximo via DB Config.
>
> Anyone got any pointers in create view with DB config?
>
>
>
>
>
Hey Sean! I never thought of doing that. Good tip!
Shannon
From: MAXIMO@yahoogroups.com [mailto:MAXIMO@yahoogroups.com] On Behalf Of
Sean Clark-McCarthy
Sent: December-22-11 7:09 AM
To: MAXIMO@yahoogroups.com
Subject: RE: [MAXIMO List] Creating custom database view in DB config (MX
7.1.x)
Try creating the view first, then in DBConfig, add a new object and give it
the same name. This should "import" the definition. I've done this for
tables before, never tried it for a view.
-- Sean Clark-McCarthy
From: MAXIMO@yahoogroups.com <mailto:MAXIMO%40yahoogroups.com>
[mailto:MAXIMO@yahoogroups.com <mailto:MAXIMO%40yahoogroups.com> ] On Behalf
Of Jason Verly
Sent: Wednesday, December 21, 2011 5:22 PM
To: MAXIMO@yahoogroups.com <mailto:MAXIMO%40yahoogroups.com>
Subject: [MAXIMO List] Creating custom database view in DB config (MX 7.1.x)
I am working on a method to have tie back how soon PM's should be done by
autopopulating the WORKORDER.SCHEDFINISH field based on a PM's frequency and
frequnit. I can create a view in Query Analyzer with the following
statement:
select workorder.wonum, workorder.status, workorder.pmnum, workorder.jpnum,
workorder.location, workorder.assetnum, pm.frequency, pm.frequnit,
case
when pm.frequnit='WEEKS' and pm.frequency < 3 then pm.frequency * 2
when pm.frequnit='WEEKS' and pm.frequency >= 3 and pm.frequency <=12 then
pm.frequency * 1.25
when pm.frequnit='WEEKS' and pm.frequency > 12 and pm.frequency <= 26 then
pm.frequency * 1
when pm.frequnit='WEEKS' and pm.frequency > 26 then pm.frequency * 0.85
when pm.frequnit='MONTHS' and pm.frequency <= 5 then pm.frequency * 5
when pm.frequnit='MONTHS' and pm.frequency >= 6 and pm.frequency <= 12 then
pm.frequency * 4
when pm.frequnit='MONTH' and pm.frequency > 12 then pm.frequency * 30 * 0.1
when pm.frequnit='YEARS' and pm.frequency <= 1 then pm.frequency * 45
when pm.frequnit='YEARS' and pm.frequency > 1 then pm.frequency * 365 * 0.1
ELSE 7
end 'pm_schdate'
from workorder join pm on (workorder.pmnum=pm.pmnum and
workorder.siteid=pm.siteid and pm.status='ACTIVE')
where workorder.istask='0' and workorder.historyflag='0' and workorder.pmnum
is not null
The Query Analyzer will create this into a view and be perfect. But I want
to creat this view so it is accessible within Maximo via DB Config.
Anyone got any pointers in create view with DB config?