Databases
StackOverflow2013
Postsdbo
POSQL Trigger - Maximo Workorders - Supervisor/Owner Initialization
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POSQL Trigger - Maximo Workorders - Supervisor/Owner Initialization
    primarykey
    data
    text
    <p>I am working with an IBM Maximo database that issues workorders automatically. When the workorders are issued (aka Inserted into the database workorder table), I would like to auto-assign a supervisor, owner, and owner group based on a set of criteria. This only needs to happen IF the supervisor, owner, and owner group aren't already assigned. Often times a "parent workorder" has the information, but it needs to be copied into the "child" workorders (as you will see in the criteria below). The criteria for ALL of the triggers is:</p> <pre><code>WHERE status&lt;&gt;'COMP' AND historyflag=0 AND istask=0 </code></pre> <p>Here is the criteria for the trigger:</p> <p>-If the Owner Group and Supervisor have a value, skip the record. (Do nothing)</p> <p>-If the Owner Group and/or Supervisor is blank or null, and the corresponding PARENT Work Order field is not Null, copy the Owner Group and/or Supervisor from the PARENT Work Order record.</p> <p>-If the Parent Work Order Owner Group and/or Supervisor is blank or null, then assign the Owner Group and Supervisor per the table values below: (I have removed names for security's sake, but all the columns are correct, i.e. B3 is supposed to have SuperA as the supervisor)</p> <pre><code>Site / OwnerGroup / Supervisor ABC / @ABCGroup / @ABCSupervisor DEF / @DEFGroup / @DEFSupervisor </code></pre> <p>**NOTE: SITE is not a table column, it is really the first 3 characters of the workorder.location field. For example, the location could be ABC-1234, meaning it is at site ABC, building 1234 (unfortunately, these are NOT stored in separate columns, they are only present together in the location column). In this SQL query, all buildings at a location are serviced by the same ownergroup/supervisor, so all other queries we currently use are using workorder.location='ABC%' </p> <p>I've done plenty of selects, updates, and stored procedures, but this is my first trigger and want to make sure I don't royally screw up the database! Any and all help is greatly appreciated!</p> <p>For those unfamiliar with Maximo, the table is: dbo.workorder and the fields are: location,ownergroup,supervisor <hr> <strong>UPDATE1:</strong><br> Here is some additional information that may be of importance.<br> <em>Locations:</em><br> First off, workorder.location will contain values such as ABC-1234, meaning it is at site ABC, building 1234 (though these are NOT separate values, it's combined). In this SQL query, all buildings at a location are serviced by the same ownergroup/supervisor, so all queries use something similar to workorder.location='ABC%'. </p> <p>Here is what I would like the logic to look like for the final query:</p> <p><i> If the supervisor field is missing, first look to see if it has a parent, and if so, does the parent have a supervisor? If not, assign based on the table above.<br><br> If the ownergroup field is missing, first look to see if it has a parent, and if so, does the parent have an ownergroup? If not, assign based on the table above.<br> </i></p> <p>This is why I am thinking a case statement maybe the best option. Also, I currently have a list of variables such as "@ASupervisor, @B1Supervisor, @B2Supervisor,...etc" so that I can change them in the future if need be. To save a lot of redundant code, is it possible to do something like:<br> (in this example, location is ABC-1234, ownergroup SHOULD be @ABCGroup, supervisor should be @ABCSupervisor, where @ABCGroup and @ABCSupervisor are set earlier in the code)<br> </p> <p>If the supervisor field is missing, first look to see if it has a parent, and if so, does the parent have a supervisor (then copy it's supervisor)? If not, assign supervisor X.<br> Where X = '@' + '(the first three characters of the location)' + 'Supervisor' (in this example, X=@ABCSupervisor)<br><br></p> <p>Is this possible?? <hr> <strong>UPDATE 2:</strong><br><br></p> <p>I have spoken with the person who asked for this database change and we have changed some thinking here. First, parent locations and child locations should always be the same (if they're not, that's a WHOLE OTHER issue). All sites (first 3 letters of location) should have the same ownergroup and supervisor, so essentially we can just look to see if a workorder entry has a NULL value in either field, then assign it based on the location. I believe the following code will work (but would like someone to review it before I implement it on the system)<br></p> <pre><code>SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TRIGGER dbo.AutoAssign ON dbo.workorder AFTER INSERT,UPDATE AS BEGIN SET NOCOUNT ON; DECLARE @ABCSupervisor varchar(30) DECLARE @DEFSupervisor varchar(30) DECLARE @ABCOwnerGroup varchar(20) DECLARE @DEFOwnerGroup varchar(20) /*EDIT VARIABLES IF FUTURE CHANGES*/ --SET Supervisor values HERE; SET @ABCSupervisor='JOHNDOE' SET @XYZSupervisor='JANEDOE' --SET OwnerGroup values HERE: SET @ABCOwnerGroup='ALPHATEAM' SET @XYZOwnerGroup='OMEGATEAM' --UPDATES UPDATE dbo.workorder SET ownergroup='@'+SUBSTR(location,1,3)+'OwnerGroup' WHERE status&lt;&gt;'COMP' AND historyflag=0 AND istask=0 AND ownergroup IS NULL AND location IS NOT NULL UPDATE dbo.workorder SET supervisor='@'+SUBSTR(location,1,3)+'Supervisor' WHERE status&lt;&gt;'COMP' AND historyflag=0 AND istask=0 AND supervisor IS NULL AND location IS NOT NULL END GO </code></pre> <p>The only issues I see here are that I do not join of some sort on the "inserted" table so that it only affects those entries (and not the entire table every time). If I could get some help on that, it would be greatly appreciated!!</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USJuan Mellado
    1. USD.R.
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. COAre you sure this have to be a trigger?
      singulars
      1. POSQL Trigger - Maximo Workorders - Supervisor/Owner Initialization
      1. USIce
    2. CODo not store some default values inside the code! If you have a database: use it! it is build to store everything in let's say in a old-school manner "tables and records".
      singulars
      1. POSQL Trigger - Maximo Workorders - Supervisor/Owner Initialization
      1. USIce
    3. COif you are in trouble with tis stuff, here's a reading of interest, have a look: http://www.simple-talk.com/sql/t-sql-programming/procedural,-semi-procedural-and-declarative-programing-part-ii/
      singulars
      1. POSQL Trigger - Maximo Workorders - Supervisor/Owner Initialization
      1. USIce
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload