This is part 2 of a 3-part series on how to query the SPF database.
In part 1, I gave an overview of the SPF database model, and showed how to write a simple query to return all objects of a specific type, starting from a given interface. This showed the DATAOBJ (object) and DATAOBJIF (interface) tables.
In this post, I show how to get two properties for those objects, using the DATAOBJPR (property) table.
In part 3, I show how to get a related object via the DATAREL (relationship) table.
We will start with the query from part 1.
Next we add a join to the property table as LEFT OUTER JOIN DATAOBJPR. In this query we get all available properties for the first few objects in the result set. The object columns will be duplicated, one for each property on the right side of the join.
/* Get specific properties of an object
Continue with the query from the prior section. In this example we will get the Major and Minor revision numbers for each Object.*/
/* Step 1. Join in the Property table.
-- We again use an alias on the Property table. I use a convention of first letter is the table
(p = Property, i = Interface, o = Object, r = Relationship)
then an underscore and a short name of the object being accessed in that table, so
p_major_rev = Property Major Revision number
-- We use a LEFT OUTER JOIN as sometimes a property row will be missing in the Property table, if the property has not been assigned for example. In this case an (INNER) JOIN would remove the Object row from the result set entirely. A LEFT OUTER JOIN will return a NULL value for the Property, and keep the Object in the result set.
-- Add our ROWNUM clause back in for speed during testing. Set it to 100 as there could be more than 10 properties on a single object, this will allow us to see all the available properties for the first few objects in the result set.
-- We also change the SELECT to include all rows from the Property table, to make it easy to pick the specific property we want in the next step.
*/
select i.interfacedefuid, o.objuid, o.objname, o.objdefuid, o.config,
p_major_rev.* /* include all rows from the property table */
from dataobjif i /* Use alias of "i" for the Interface table */
/* Join to the Object table to get the objects having this interface. */
join dataobj o
on i.objobid = o.obid /* The Interface table's OBJOBID column is the foreign key to the Object table's OBID primary key column. */
and o.terminationdate = '9999/12/31-23:59:59:999' /* include only current objects */
/* Join to the Property table to get the properties for these objects. For now we get them all, in the next step we will get the Major Revision number. */
left outer join dataobjpr p_major_rev
on o.obid = p_major_rev.objobid /* The Property table's OBJOBID column is the foreign key to the Object table's OBID primary key column. */
and p_major_rev.terminationdate = '9999/12/31-23:59:59:999' /* include only current objects */
/* Back to the main Interface table selection criteria */
where i.interfacedefuid = 'ISPFDocumentRevision' /* include only our desired interface */
and i.terminationdate = '9999/12/31-23:59:59:999' /* include only current data (ignore terminated records) */
and rownum <= 100 /* Just get a few records for speed during testing */
;
SPF Database Queries 2 – Step 1
Now we select which property we want, in this case we want the Major Revision property named SPFMajorRevision, and we restrict the join to just that one property by it’s name (PROPERTYDEFUID).
/* Step 2. Select just the Major Revsion number property.
-- Look in the resut set and find the PROPERTYDEFUID for the specific property we want. For Major Revision Number, we want the SPFMajorRevision property.
-- Add this criterion on the join to the Property table:
and p_major_rev.propertydefuid = 'SPFMajorRevision'
-- Change the SELECT clause to choose only the STRVALUE column from this join to the Property table instead of * . Use AS SPFMajorRevision to assign a more descriptive column name to this value.
*/
select i.interfacedefuid, o.objuid, o.objname, o.objdefuid, o.config,
p_major_rev.strvalue as SPFMajorRevision /* choose the Major Revsion number column from the Property table alias */
from dataobjif i /* Use alias of "i" for the Interface table */
/* Join to the Object table to get the objects having this interface. */
join dataobj o
on i.objobid = o.obid /* The Interface table's OBJOBID column is the foreign key to the Object table's OBID primary key column. */
and o.terminationdate = '9999/12/31-23:59:59:999' /* include only current objects */
/* Join to the Property table to get the SPFMajorRevision property for these objects. */
left outer join dataobjpr p_major_rev
on o.obid = p_major_rev.objobid /* The Property table's OBJOBID column is the foreign key to the Object table's OBID primary key column. */
and p_major_rev.terminationdate = '9999/12/31-23:59:59:999' /* include only current objects */
and p_major_rev.propertydefuid = 'SPFMajorRevision'
/* Back to the main Interface table selection criteria */
where i.interfacedefuid = 'ISPFDocumentRevision' /* include only our desired interface */
and i.terminationdate = '9999/12/31-23:59:59:999' /* include only current data (ignore terminated records) */
and rownum <= 100 /* Just get a few records for speed during testing */
;
Here you can see we have retrieved the Major Revision property as a column in the result set.
SPF Database Queries 2 – Step 2
Now we repeat the procedure for each property we want. Here we get the Minor Revision property named SPFMinorRevision.
/* Step 3. Do the same process to select the Minor Revsion number property.
-- Copy and paste the entire LEFT OUTER JOIN section for the Major revision, and change the following to make a new join to the Property table for the Minor revision property, with a different alias:
table alias: p_major_rev to p_minor_rev (several places)
propertydefuid from SPFMajorRevision to SPFMinorRevision
-- Add another column in the SELECT clause to get the Minor Revision number from the p_minor_rev alias
p_minor_rev.strvalue as SPFMinorRevision
and as before, name the column the same as the property.
*/
select i.interfacedefuid, o.objuid, o.objname, o.objdefuid, o.config,
p_major_rev.strvalue as SPFMajorRevision, /* choose the Major Revsion number column from the Property table alias */
p_minor_rev.strvalue as SPFMinorRevision /* choose the Minor Revsion number column from the Property table alias */
from dataobjif i /* Use alias of "i" for the Interface table */
/* Join to the Object table to get the objects having this interface. */
join dataobj o
on i.objobid = o.obid /* The Interface table's OBJOBID column is the foreign key to the Object table's OBID primary key column. */
and o.terminationdate = '9999/12/31-23:59:59:999' /* include only current objects */
/* Join to the Property table to get the SPFMajorRevision property for these objects. */
left outer join dataobjpr p_major_rev
on o.obid = p_major_rev.objobid /* The Property table's OBJOBID column is the foreign key to the Object table's OBID primary key column. */
and p_major_rev.terminationdate = '9999/12/31-23:59:59:999' /* include only current objects */
and p_major_rev.propertydefuid = 'SPFMajorRevision'
/* Join to the Property table to get the SPFMinorRevision property for these objects. */
left outer join dataobjpr p_minor_rev
on o.obid = p_minor_rev.objobid /* The Property table's OBJOBID column is the foreign key to the Object table's OBID primary key column. */
and p_minor_rev.terminationdate = '9999/12/31-23:59:59:999' /* include only current objects */
and p_minor_rev.propertydefuid = 'SPFMinorRevision'
/* Back to the main Interface table selection criteria */
where i.interfacedefuid = 'ISPFDocumentRevision' /* include only our desired interface */
and i.terminationdate = '9999/12/31-23:59:59:999' /* include only current data (ignore terminated records) */
and rownum <= 100 /* Just get a few records for speed during testing */
;
And now we can see out complete query returning both properties as separate columns in the result set.
SPF Database Queries 2 – Step 3