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.
Is there any benefit for power users to learn SQL?
I say Yes.
SQL is used inside Query statements within the applications.
MAXIMO does sorta build this for you when performing word searches from filter line.
But sometimes it is helpful to understand and build your own.
And you can do a "nested select", for example.
Nested selects help you reference values in child tables.
Let's say you wanted to find all Assets that had a Meter applied.
I would first take a look at my "table-attribute" listing in Excel.
ASSETMETER ASSETNUM
ASSETMETER METERNAME
ASSETMETER SITEID
ASSETMETER ACTIVE
ASSETMETER ASSETMETERID
ASSETMETER AVERAGE
ASSETMETER AVGCALCMETHOD
ASSETMETER CHANGEBY
ASSETMETER CHANGEDATE
The above list shows the ASSETNUM as being present.
Then I goto the MAXIMO asset application (List screen), and pull up a blank Query dialog and try out my statement.
Where ASSETNUM in (select ASSETNUM from ASSETMETER)
I might first add a Meter to a given Asset record so that I know I will get at least "one hit". If your statement works, then you can start Saving these Queries.
Other examples might be:
1) Find all JobPlans missing Craft estimates
2) Find all open work orders where the last labor actual (hours) entry was > 3 months old
The last point is, by having SQL knowledge, you can periodically search the entire database (using a backend tool, e.g. SQL Developer) for data errors. Plus, I like to use this technique for "roughing out" a report design which I can then give to the BIRT guy to make pretty.
w/br
John Reeve
Manager, Practice Leader Maintenance and Reliability Solutions
Cell: 423 314 1312
http://www.linkedin.com/pub/john-reeve/11/644/9b2
I totally second this recommendation.
In my experience, users that learn SQL have definitely found it worth the
while, since almost everything in Maximo is SQL-based. At one client, they
have been teaching SQL in conjunction with QBR Report training, and the
users love it.
I use it myself, more often then not, when searching for something via the
GUI.
Shannon Rotz
From: MAXIMO@yahoogroups.com [mailto:MAXIMO@yahoogroups.com] On Behalf Of
John
Sent: January-18-13 9:45 AM
To: MAXIMO@yahoogroups.com
Subject: [MAXIMO List] Tip of the Day #268
Is there any benefit for power users to learn SQL?
I say Yes.
SQL is used inside Query statements within the applications.
MAXIMO does sorta build this for you when performing word searches from
filter line.
But sometimes it is helpful to understand and build your own.
And you can do a "nested select", for example.
Nested selects help you reference values in child tables.
Let's say you wanted to find all Assets that had a Meter applied.
I would first take a look at my "table-attribute" listing in Excel.
ASSETMETER ASSETNUM
ASSETMETER METERNAME
ASSETMETER SITEID
ASSETMETER ACTIVE
ASSETMETER ASSETMETERID
ASSETMETER AVERAGE
ASSETMETER AVGCALCMETHOD
ASSETMETER CHANGEBY
ASSETMETER CHANGEDATE
The above list shows the ASSETNUM as being present.
Then I goto the MAXIMO asset application (List screen), and pull up a blank
Query dialog and try out my statement.
Where ASSETNUM in (select ASSETNUM from ASSETMETER)
I might first add a Meter to a given Asset record so that I know I will get
at least "one hit". If your statement works, then you can start Saving these
Queries.
Other examples might be:
1) Find all JobPlans missing Craft estimates
2) Find all open work orders where the last labor actual (hours) entry was >
3 months old
The last point is, by having SQL knowledge, you can periodically search the
entire database (using a backend tool, e.g. SQL Developer) for data errors.
Plus, I like to use this technique for "roughing out" a report design which
I can then give to the BIRT guy to make pretty.
w/br
John Reeve
Manager, Practice Leader Maintenance and Reliability Solutions
Cell: 423 314 1312
http://www.linkedin.com/pub/john-reeve/11/644/9b2
I agree, teaching SQL to power users is almost a must now. Most organizations won't give users access to a full blown SQL query tool, but utilizing the Where Clause with custom SQL statements can give them almost all of the same functionality.
The best lesson I'd recommend is teaching users to use rolling date functions (getdate()-report date > 30) vs hard coded date functions (selecting dates in Adv Search). This way any queries used in QBR reports don't require reports to be redesigned each time its needed.
--- In MAXIMO@yahoogroups.com, "Incomm Solutions Inc." wrote:
>
> I totally second this recommendation.
>
>
>
> In my experience, users that learn SQL have definitely found it worth the
> while, since almost everything in Maximo is SQL-based. At one client, they
> have been teaching SQL in conjunction with QBR Report training, and the
> users love it.
>
>
>
> I use it myself, more often then not, when searching for something via the
> GUI.
>
>
>
>
>
>
>
> Shannon Rotz
>
>
>
>
>
> From: MAXIMO@yahoogroups.com [mailto:MAXIMO@yahoogroups.com] On Behalf Of
> John
> Sent: January-18-13 9:45 AM
> To: MAXIMO@yahoogroups.com
> Subject: [MAXIMO List] Tip of the Day #268
>
>
>
>
>
> Is there any benefit for power users to learn SQL?
> I say Yes.
>
> SQL is used inside Query statements within the applications.
> MAXIMO does sorta build this for you when performing word searches from
> filter line.
> But sometimes it is helpful to understand and build your own.
>
> And you can do a "nested select", for example.
> Nested selects help you reference values in child tables.
> Let's say you wanted to find all Assets that had a Meter applied.
> I would first take a look at my "table-attribute" listing in Excel.
> ASSETMETER ASSETNUM
> ASSETMETER METERNAME
> ASSETMETER SITEID
> ASSETMETER ACTIVE
> ASSETMETER ASSETMETERID
> ASSETMETER AVERAGE
> ASSETMETER AVGCALCMETHOD
> ASSETMETER CHANGEBY
> ASSETMETER CHANGEDATE
> The above list shows the ASSETNUM as being present.
>
> Then I goto the MAXIMO asset application (List screen), and pull up a blank
> Query dialog and try out my statement.
> Where ASSETNUM in (select ASSETNUM from ASSETMETER)
>
> I might first add a Meter to a given Asset record so that I know I will get
> at least "one hit". If your statement works, then you can start Saving these
> Queries.
>
> Other examples might be:
> 1) Find all JobPlans missing Craft estimates
> 2) Find all open work orders where the last labor actual (hours) entry was >
> 3 months old
>
> The last point is, by having SQL knowledge, you can periodically search the
> entire database (using a backend tool, e.g. SQL Developer) for data errors.
> Plus, I like to use this technique for "roughing out" a report design which
> I can then give to the BIRT guy to make pretty.
>
> w/br
> John Reeve
> Manager, Practice Leader Maintenance and Reliability Solutions
> Cell: 423 314 1312
> http://www.linkedin.com/pub/john-reeve/11/644/9b2
>
>
>
>
>
>
>
Two notes of caution on "power users" using SQL:
1. Be careful that only production quality SQL is executed in your production environment. Poorly written SQL queries are the fasted way to kill performance.
2. Enabling the "Where Clause" in Maximo applications provides an easy way for malicious users to gain access to data they should not have access to. SQL injection is so widely documented nowadays that you don't really even need to be that savvy to pull this off.
That being said, I do agree in principal that true "power users" can benefit from knowing SQL. Just make sure this level of access is handed out judiciously. We went so far as to put on a basic SQL class for some if our staff. This was only moderately successful as there is still a reliance on the IT for most query creation.
-Chris H
From: MAXIMO@yahoogroups.com [mailto:MAXIMO@yahoogroups.com] On Behalf Of John
Sent: Friday, January 18, 2013 11:45 AM
To: MAXIMO@yahoogroups.com
Subject: [MAXIMO List] Tip of the Day #268
Is there any benefit for power users to learn SQL?
I say Yes.
SQL is used inside Query statements within the applications.
MAXIMO does sorta build this for you when performing word searches from filter line.
But sometimes it is helpful to understand and build your own.
And you can do a "nested select", for example.
Nested selects help you reference values in child tables.
Let's say you wanted to find all Assets that had a Meter applied.
I would first take a look at my "table-attribute" listing in Excel.
ASSETMETER ASSETNUM
ASSETMETER METERNAME
ASSETMETER SITEID
ASSETMETER ACTIVE
ASSETMETER ASSETMETERID
ASSETMETER AVERAGE
ASSETMETER AVGCALCMETHOD
ASSETMETER CHANGEBY
ASSETMETER CHANGEDATE
The above list shows the ASSETNUM as being present.
Then I goto the MAXIMO asset application (List screen), and pull up a blank Query dialog and try out my statement.
Where ASSETNUM in (select ASSETNUM from ASSETMETER)
I might first add a Meter to a given Asset record so that I know I will get at least "one hit". If your statement works, then you can start Saving these Queries.
Other examples might be:
1) Find all JobPlans missing Craft estimates
2) Find all open work orders where the last labor actual (hours) entry was > 3 months old
The last point is, by having SQL knowledge, you can periodically search the entire database (using a backend tool, e.g. SQL Developer) for data errors. Plus, I like to use this technique for "roughing out" a report design which I can then give to the BIRT guy to make pretty.
w/br
John Reeve
Manager, Practice Leader Maintenance and Reliability Solutions
Cell: 423 314 1312
http://www.linkedin.com/pub/john-reeve/11/644/9b2
Hi Chris - you're quite right to remind us that a poorly-written SQL
statement can be dangerous . I'm sure we've all had cases where the
performance slowed to a crawl or stopped altogether because somebody forgot
to put a "(" and ")" around their "OR" clause .
:)
Shannon
From: MAXIMO@yahoogroups.com [mailto:MAXIMO@yahoogroups.com] On Behalf Of
Hanna, Christopher CTR
Sent: January-22-13 6:42 AM
To: MAXIMO@yahoogroups.com
Subject: RE: [MAXIMO List] Tip of the Day #268
Two notes of caution on "power users" using SQL:
1. Be careful that only production quality SQL is executed in your
production environment. Poorly written SQL queries are the fasted way to
kill performance.
2. Enabling the "Where Clause" in Maximo applications provides an easy way
for malicious users to gain access to data they should not have access to.
SQL injection is so widely documented nowadays that you don't really even
need to be that savvy to pull this off.
That being said, I do agree in principal that true "power users" can benefit
from knowing SQL. Just make sure this level of access is handed out
judiciously. We went so far as to put on a basic SQL class for some if our
staff. This was only moderately successful as there is still a reliance on
the IT for most query creation.
-Chris H
From: MAXIMO@yahoogroups.com <mailto:MAXIMO%40yahoogroups.com>
[mailto:MAXIMO@yahoogroups.com <mailto:MAXIMO%40yahoogroups.com> ] On Behalf
Of John
Sent: Friday, January 18, 2013 11:45 AM
To: MAXIMO@yahoogroups.com <mailto:MAXIMO%40yahoogroups.com>
Subject: [MAXIMO List] Tip of the Day #268
Is there any benefit for power users to learn SQL?
I say Yes.
SQL is used inside Query statements within the applications.
MAXIMO does sorta build this for you when performing word searches from
filter line.
But sometimes it is helpful to understand and build your own.
And you can do a "nested select", for example.
Nested selects help you reference values in child tables.
Let's say you wanted to find all Assets that had a Meter applied.
I would first take a look at my "table-attribute" listing in Excel.
ASSETMETER ASSETNUM
ASSETMETER METERNAME
ASSETMETER SITEID
ASSETMETER ACTIVE
ASSETMETER ASSETMETERID
ASSETMETER AVERAGE
ASSETMETER AVGCALCMETHOD
ASSETMETER CHANGEBY
ASSETMETER CHANGEDATE
The above list shows the ASSETNUM as being present.
Then I goto the MAXIMO asset application (List screen), and pull up a blank
Query dialog and try out my statement.
Where ASSETNUM in (select ASSETNUM from ASSETMETER)
I might first add a Meter to a given Asset record so that I know I will get
at least "one hit". If your statement works, then you can start Saving these
Queries.
Other examples might be:
1) Find all JobPlans missing Craft estimates
2) Find all open work orders where the last labor actual (hours) entry was >
3 months old
The last point is, by having SQL knowledge, you can periodically search the
entire database (using a backend tool, e.g. SQL Developer) for data errors.
Plus, I like to use this technique for "roughing out" a report design which
I can then give to the BIRT guy to make pretty.
w/br
John Reeve
Manager, Practice Leader Maintenance and Reliability Solutions
Cell: 423 314 1312
http://www.linkedin.com/pub/john-reeve/11/644/9b2
You can use webclient.ResultSetQueryTimeout (http://www-01.ibm.com/support/docview.wss?uid=swg21331229) to reduce the length of impact for poorly written queries. It only affects the list tab queries. As always, use caution when modifying these values.
@Chris, 2. SQL injection in where clause is scary. Surely the code would prevent malicious acts from occurring so easily? Much to my surprise it doesn't. In fact, I dropped the asset table from the work order tracking where clause!!! Excuse me while I pickup my jaw off the floor.
-Paul
--- In MAXIMO@yahoogroups.com, "Incomm Solutions Inc." wrote:
>
> Hi Chris - you're quite right to remind us that a poorly-written SQL
> statement can be dangerous . I'm sure we've all had cases where the
> performance slowed to a crawl or stopped altogether because somebody forgot
> to put a "(" and ")" around their "OR" clause .
>
> :)
>
> Shannon
>
> From: MAXIMO@yahoogroups.com [mailto:MAXIMO@yahoogroups.com] On Behalf Of
> Hanna, Christopher CTR
> Sent: January-22-13 6:42 AM
> To: MAXIMO@yahoogroups.com
> Subject: RE: [MAXIMO List] Tip of the Day #268
>
>
> Two notes of caution on "power users" using SQL:
>
> 1. Be careful that only production quality SQL is executed in your
> production environment. Poorly written SQL queries are the fasted way to
> kill performance.
>
> 2. Enabling the "Where Clause" in Maximo applications provides an easy way
> for malicious users to gain access to data they should not have access to.
> SQL injection is so widely documented nowadays that you don't really even
> need to be that savvy to pull this off.
>
> That being said, I do agree in principal that true "power users" can benefit
> from knowing SQL. Just make sure this level of access is handed out
> judiciously. We went so far as to put on a basic SQL class for some if our
> staff. This was only moderately successful as there is still a reliance on
> the IT for most query creation.
>
> -Chris H
>
> From: MAXIMO@yahoogroups.com
> [mailto:MAXIMO@yahoogroups.com ] On Behalf
> Of John
> Sent: Friday, January 18, 2013 11:45 AM
> To: MAXIMO@yahoogroups.com
> Subject: [MAXIMO List] Tip of the Day #268
>
> Is there any benefit for power users to learn SQL?
> I say Yes.
>
> SQL is used inside Query statements within the applications.
> MAXIMO does sorta build this for you when performing word searches from
> filter line.
> But sometimes it is helpful to understand and build your own.
>
> And you can do a "nested select", for example.
> Nested selects help you reference values in child tables.
> Let's say you wanted to find all Assets that had a Meter applied.
> I would first take a look at my "table-attribute" listing in Excel.
> ASSETMETER ASSETNUM
> ASSETMETER METERNAME
> ASSETMETER SITEID
> ASSETMETER ACTIVE
> ASSETMETER ASSETMETERID
> ASSETMETER AVERAGE
> ASSETMETER AVGCALCMETHOD
> ASSETMETER CHANGEBY
> ASSETMETER CHANGEDATE
> The above list shows the ASSETNUM as being present.
>
> Then I goto the MAXIMO asset application (List screen), and pull up a blank
> Query dialog and try out my statement.
> Where ASSETNUM in (select ASSETNUM from ASSETMETER)
>
> I might first add a Meter to a given Asset record so that I know I will get
> at least "one hit". If your statement works, then you can start Saving these
> Queries.
>
> Other examples might be:
> 1) Find all JobPlans missing Craft estimates
> 2) Find all open work orders where the last labor actual (hours) entry was >
> 3 months old
>
> The last point is, by having SQL knowledge, you can periodically search the
> entire database (using a backend tool, e.g. SQL Developer) for data errors.
> Plus, I like to use this technique for "roughing out" a report design which
> I can then give to the BIRT guy to make pretty.
>
> w/br
> John Reeve
> Manager, Practice Leader Maintenance and Reliability Solutions
> Cell: 423 314 1312
> http://www.linkedin.com/pub/john-reeve/11/644/9b2
>
>
>
>
>
>
>
>
>
This should prevent sql injection - never tried it though :)
http://www-01.ibm.com/support/docview.wss?uid=swg21389946
From: MAXIMO@yahoogroups.com [mailto:MAXIMO@yahoogroups.com] On Behalf Of pkroseman
Sent: 24 January 2013 15:41
To: MAXIMO@yahoogroups.com
Subject: [MAXIMO List] Re: Tip of the Day #268
You can use webclient.ResultSetQueryTimeout (http://www-01.ibm.com/support/docview.wss?uid=swg21331229) to reduce the length of impact for poorly written queries. It only affects the list tab queries. As always, use caution when modifying these values.
@Chris, 2. SQL injection in where clause is scary. Surely the code would prevent malicious acts from occurring so easily? Much to my surprise it doesn't. In fact, I dropped the asset table from the work order tracking where clause!!! Excuse me while I pickup my jaw off the floor.
-Paul
--- In MAXIMO@yahoogroups.com<mailto:MAXIMO%40yahoogroups.com>, "Incomm Solutions Inc." wrote:
>
> Hi Chris - you're quite right to remind us that a poorly-written SQL
> statement can be dangerous . I'm sure we've all had cases where the
> performance slowed to a crawl or stopped altogether because somebody forgot
> to put a "(" and ")" around their "OR" clause .
>
> :)
>
> Shannon
>
> From: MAXIMO@yahoogroups.com<mailto:MAXIMO%40yahoogroups.com> [mailto:MAXIMO@yahoogroups.com<mailto:MAXIMO%40yahoogroups.com>] On Behalf Of
> Hanna, Christopher CTR
> Sent: January-22-13 6:42 AM
> To: MAXIMO@yahoogroups.com<mailto:MAXIMO%40yahoogroups.com>
> Subject: RE: [MAXIMO List] Tip of the Day #268
>
>
> Two notes of caution on "power users" using SQL:
>
> 1. Be careful that only production quality SQL is executed in your
> production environment. Poorly written SQL queries are the fasted way to
> kill performance.
>
> 2. Enabling the "Where Clause" in Maximo applications provides an easy way
> for malicious users to gain access to data they should not have access to.
> SQL injection is so widely documented nowadays that you don't really even
> need to be that savvy to pull this off.
>
> That being said, I do agree in principal that true "power users" can benefit
> from knowing SQL. Just make sure this level of access is handed out
> judiciously. We went so far as to put on a basic SQL class for some if our
> staff. This was only moderately successful as there is still a reliance on
> the IT for most query creation.
>
> -Chris H
>
> From: MAXIMO@yahoogroups.com<mailto:MAXIMO%40yahoogroups.com>
> [mailto:MAXIMO@yahoogroups.com<mailto:MAXIMO%40yahoogroups.com> ] On Behalf
> Of John
> Sent: Friday, January 18, 2013 11:45 AM
> To: MAXIMO@yahoogroups.com<mailto:MAXIMO%40yahoogroups.com>
> Subject: [MAXIMO List] Tip of the Day #268
>
> Is there any benefit for power users to learn SQL?
> I say Yes.
>
> SQL is used inside Query statements within the applications.
> MAXIMO does sorta build this for you when performing word searches from
> filter line.
> But sometimes it is helpful to understand and build your own.
>
> And you can do a "nested select", for example.
> Nested selects help you reference values in child tables.
> Let's say you wanted to find all Assets that had a Meter applied.
> I would first take a look at my "table-attribute" listing in Excel.
> ASSETMETER ASSETNUM
> ASSETMETER METERNAME
> ASSETMETER SITEID
> ASSETMETER ACTIVE
> ASSETMETER ASSETMETERID
> ASSETMETER AVERAGE
> ASSETMETER AVGCALCMETHOD
> ASSETMETER CHANGEBY
> ASSETMETER CHANGEDATE
> The above list shows the ASSETNUM as being present.
>
> Then I goto the MAXIMO asset application (List screen), and pull up a blank
> Query dialog and try out my statement.
> Where ASSETNUM in (select ASSETNUM from ASSETMETER)
>
> I might first add a Meter to a given Asset record so that I know I will get
> at least "one hit". If your statement works, then you can start Saving these
> Queries.
>
> Other examples might be:
> 1) Find all JobPlans missing Craft estimates
> 2) Find all open work orders where the last labor actual (hours) entry was >
> 3 months old
>
> The last point is, by having SQL knowledge, you can periodically search the
> entire database (using a backend tool, e.g. SQL Developer) for data errors.
> Plus, I like to use this technique for "roughing out" a report design which
> I can then give to the BIRT guy to make pretty.
>
> w/br
> John Reeve
> Manager, Practice Leader Maintenance and Reliability Solutions
> Cell: 423 314 1312
> http://www.linkedin.com/pub/john-reeve/11/644/9b2
>
>
>
>
>
>
>
>
>
GDF SUEZ E&P UK Ltd (Company Number 3386464), registered in England and Wales with a registered office address at: 40 Holborn Viaduct, London, EC1N 2PB.
**************************************************************************************************************
This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed.
If you have received this email in error please notify the system manager.
**************************************************************************************************************
I'm dubious about a the ability of a filter to protect against this sort of hole. SQL Injection may not even truly be the right term for this. Typically SQL injection has a malicious user "tricking" the application into executing some SQL. You're not really even "injecting" your SQL, Maximo just allows you to run it. If anybody is using this method to protect against this sort of thing, I'd be interested to know the results.
I guess the key point to keep in mind is that SQL is very powerful, for both good and bad. Caution should be exercised as to whom is given direct access to the where clause. Security best practice of least privilege should apply.
-Chris H
-----Original Message-----
From: MAXIMO@yahoogroups.com [mailto:MAXIMO@yahoogroups.com] On Behalf Of Ian Wright
Sent: Thursday, January 24, 2013 10:52 AM
To: MAXIMO@yahoogroups.com
Subject: RE: [MAXIMO List] Re: Tip of the Day #268
This should prevent sql injection - never tried it though :)
http://www-01.ibm.com/support/docview.wss?uid=swg21389946
From: MAXIMO@yahoogroups.com <mailto:MAXIMO%40yahoogroups.com> [mailto:MAXIMO@yahoogroups.com <mailto:MAXIMO%40yahoogroups.com> ] On Behalf Of pkroseman
Sent: 24 January 2013 15:41
To: MAXIMO@yahoogroups.com <mailto:MAXIMO%40yahoogroups.com>
Subject: [MAXIMO List] Re: Tip of the Day #268
You can use webclient.ResultSetQueryTimeout (http://www-01.ibm.com/support/docview.wss?uid=swg21331229) to reduce the length of impact for poorly written queries. It only affects the list tab queries. As always, use caution when modifying these values.
@Chris, 2. SQL injection in where clause is scary. Surely the code would prevent malicious acts from occurring so easily? Much to my surprise it doesn't. In fact, I dropped the asset table from the work order tracking where clause!!! Excuse me while I pickup my jaw off the floor.
-Paul
--- In MAXIMO@yahoogroups.com <mailto:MAXIMO%40yahoogroups.com> , "Incomm Solutions Inc." wrote:
>
> Hi Chris - you're quite right to remind us that a poorly-written SQL
> statement can be dangerous . I'm sure we've all had cases where the
> performance slowed to a crawl or stopped altogether because somebody
> forgot to put a "(" and ")" around their "OR" clause .
>
> :)
>
> Shannon
>
> From: MAXIMO@yahoogroups.com <mailto:MAXIMO%40yahoogroups.com>
> [mailto:MAXIMO@yahoogroups.com <mailto:MAXIMO%40yahoogroups.com> ] On
> Behalf Of Hanna, Christopher CTR
> Sent: January-22-13 6:42 AM
> To: MAXIMO@yahoogroups.com <mailto:MAXIMO%40yahoogroups.com>
> Subject: RE: [MAXIMO List] Tip of the Day #268
>
>
> Two notes of caution on "power users" using SQL:
>
> 1. Be careful that only production quality SQL is executed in your
> production environment. Poorly written SQL queries are the fasted way
> to kill performance.
>
> 2. Enabling the "Where Clause" in Maximo applications provides an easy
> way for malicious users to gain access to data they should not have access to.
> SQL injection is so widely documented nowadays that you don't really
> even need to be that savvy to pull this off.
>
> That being said, I do agree in principal that true "power users" can
> benefit from knowing SQL. Just make sure this level of access is
> handed out judiciously. We went so far as to put on a basic SQL class
> for some if our staff. This was only moderately successful as there is
> still a reliance on the IT for most query creation.
>
> -Chris H
>
> From: MAXIMO@yahoogroups.com <mailto:MAXIMO%40yahoogroups.com>
> [mailto:MAXIMO@yahoogroups.com <mailto:MAXIMO%40yahoogroups.com> ] On
> Behalf Of John
> Sent: Friday, January 18, 2013 11:45 AM
> To: MAXIMO@yahoogroups.com <mailto:MAXIMO%40yahoogroups.com>
> Subject: [MAXIMO List] Tip of the Day #268
>
> Is there any benefit for power users to learn SQL?
> I say Yes.
>
> SQL is used inside Query statements within the applications.
> MAXIMO does sorta build this for you when performing word searches
> from filter line.
> But sometimes it is helpful to understand and build your own.
>
> And you can do a "nested select", for example.
> Nested selects help you reference values in child tables.
> Let's say you wanted to find all Assets that had a Meter applied.
> I would first take a look at my "table-attribute" listing in Excel.
> ASSETMETER ASSETNUM
> ASSETMETER METERNAME
> ASSETMETER SITEID
> ASSETMETER ACTIVE
> ASSETMETER ASSETMETERID
> ASSETMETER AVERAGE
> ASSETMETER AVGCALCMETHOD
> ASSETMETER CHANGEBY
> ASSETMETER CHANGEDATE
> The above list shows the ASSETNUM as being present.
>
> Then I goto the MAXIMO asset application (List screen), and pull up a
> blank Query dialog and try out my statement.
> Where ASSETNUM in (select ASSETNUM from ASSETMETER)
>
> I might first add a Meter to a given Asset record so that I know I
> will get at least "one hit". If your statement works, then you can
> start Saving these Queries.
>
> Other examples might be:
> 1) Find all JobPlans missing Craft estimates
> 2) Find all open work orders where the last labor actual (hours) entry
> was >
> 3 months old
>
> The last point is, by having SQL knowledge, you can periodically
> search the entire database (using a backend tool, e.g. SQL Developer) for data errors.
> Plus, I like to use this technique for "roughing out" a report design
> which I can then give to the BIRT guy to make pretty.
>
> w/br
> John Reeve
> Manager, Practice Leader Maintenance and Reliability Solutions
> Cell: 423 314 1312
> http://www.linkedin.com/pub/john-reeve/11/644/9b2
>
>
>
>
>
>
>
>
>
GDF SUEZ E&P UK Ltd (Company Number 3386464), registered in England and Wales with a registered office address at: 40 Holborn Viaduct, London, EC1N 2PB.
**************************************************************************************************************
This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed.
If you have received this email in error please notify the system manager.
**************************************************************************************************************