The birth of a query

So, my boss said, way back in the day. I want to find out the locations where we have compressor bottles, but no compressors, and the vise versa, compressors without bottles. She didn't mean in the real world, she meant in a database.

You need to know this was during the data migration process to Maximo. It used to be that compressors were in a different database than compressor bottles.

So I started with a quick and dirty bit of SQL, and had a list that got some tweaking in xl. That was one week, and the next week all the people that were interested in that were let go.

Time went on, as did various data integrity processes, and eventually it came up again, with a twist. My current boss wanted to know the same thing, but also if the vessel regulator thought we owned the vessels or not, and did the locations and operational statuses agree. Plus, while you're at it, the location hierarchy up to the top, so we can send out the lists to the right place for correction.

So here's a trivially simple bit of SQL.

select *
from ABSA20151209
where ANUMFMT in
(
)
;

That gives me a list of all the vessels in the regulator database where the A number is in a list I provide, and put inside the brackets. There is another much longer, more complicated chunk of SQL that gets the lists of bottles for that A number, and compressors, and compares them. With a bit of shuffling around I can know lots of the individual pieces of the data, and could assemble them into an xl. Except that it's really easy to miss something, and I wanted to build the query to do it all for me.

I was going to dive in and join the two lists when I realized that there are a few vessels in our database that are not listed in the regulator's database as owned by us, and some they think we own, but are not in our database. Sometimes order is really important. Such things need to be accounted for or you'll drive yourself crazy. Plus I know I'm getting a new regulator list in a few days, so I need to make it easy to drop it into the query.

I built a little diagram to make sure I knew what I was getting, and to keep me on track so I didn't build something else.

Then the creative part starts. There's several ways to do this. I start writing simple SQL and make sure each bit works before adding other bits which have also been tested. So. Get the list of compressors that are not air compressors, get the hierarchy. Get the list of compressor bottles, and add in hierarchy. Left join them to get the list of locations where there are bottles but no compressors. Left join that to the regulator list. Along the way tweak the code for the subtleties of data. Extract the A number from another table so I can link to the regulator. Correctly handle where one table has null values. Every step along the way make sure the right number of assets are showing up. Then the work begins, looking at the output xl to see where work exists to fix stuff.

You are just dying to read the result of all this creativity, I know it. I just KNOW it! Though I do have to admit the colour coding in my editor makes things a little easier to read. And yes, for the two SQL geeks that I know read my blog, I know I did the joins two different ways, and that first ones you see are not ANSI SQL-92 compliant. Getting the location hierarchy going is tricky, and it was easiest to see this way, and once I had it, I've never bothered to go back and re-write it. But this is part of what I do to get paid.

WITH X AS (
--This whole query is to compare compressors to compressor bottles by LSD and then compare to ABSA.
SELECT
   L5.PARENT AS DISTRICT,
   --This query generates a list of locations in hierarchy together with compressors.
   L4.PARENT AS SUPI,
   L3.PARENT AS BUDGET,
   L2.PARENT AS PROP,
   L1.PARENT AS FIELD,
   A.LOCATION AS LOCATION, A.STATUS AS LOC_STATUS, SUBSTR(A.LOCATION, -16) AS LSD,
   S.ASSETNUM, S.STATUS AS ASSET_STATUS, S.DESCRIPTION, S.MANUFACTURER,
   C.NAME, S.PLUSCMODELNUM
FROM
   MAXIMO.ASSET@keitcart_omaxmop1_dblink S,
   MAXIMO.COMPANIES@keitcart_omaxmop1_dblink C,
   MAXIMO.LOCATIONS@keitcart_omaxmop1_dblink A,
   MAXIMO.LOCHIERARCHY@keitcart_omaxmop1_dblink L1,
   MAXIMO.LOCHIERARCHY@keitcart_omaxmop1_dblink L2,
   MAXIMO.LOCHIERARCHY@keitcart_omaxmop1_dblink L3,
   MAXIMO.LOCHIERARCHY@keitcart_omaxmop1_dblink L4,
   MAXIMO.LOCHIERARCHY@keitcart_omaxmop1_dblink L5
WHERE
   S.LOCATION = A.LOCATION
   AND S.MANUFACTURER = C.COMPANY
   AND A.LOCATION = L1.LOCATION
   AND L1.PARENT = L2.LOCATION
   AND L2.PARENT = L3.LOCATION
   AND L3.PARENT = L4.LOCATION
   AND L4.PARENT = L5.LOCATION
   AND L4.PARENT <>'C-RETIRED'
   AND S.DESCRIPTION LIKE 'Compressor%'
   AND S.DESCRIPTION NOT LIKE '%AIR%'
   AND S.DESCRIPTION NOT LIKE '%Air%'
   AND S.DESCRIPTION NOT LIKE '% air%'
   AND S.DESCRIPTION NOT LIKE '%Pumpjack%'
   AND S.DESCRIPTION NOT LIKE '%VRU%'
   AND S.STATUS NOT IN ('SOLD', 'SOLDCL', 'INVENTORY', 'SCRAPPED','SURPLUS FOR PARTS','DATA RECYCLE')
ORDER BY
   L5.PARENT, L4.PARENT, L3.PARENT,L2.PARENT,L1.PARENT, SUBSTR(A.LOCATION, -16), A.LOCATION),
Y AS (        
SELECT
   L5.PARENT AS DISTRICT,
   --This query generates a list of locations in hierarchy together with compressor bottles.
   L4.PARENT AS SUPI,
   L3.PARENT AS BUDGET,
   L2.PARENT AS PROP,
   L1.PARENT AS FIELD,
   A.LOCATION AS LOCATION, A.STATUS AS LOC_STATUS, SUBSTR(A.LOCATION, -16) AS LSD,
   S.ASSETNUM, S.STATUS AS ASSET_STATUS, S.DESCRIPTION, P.ALNVALUE AS ANUM, S.PLUSCMODELNUM
FROM
   MAXIMO.ASSETSPEC@keitcart_omaxmop1_dblink P,
   MAXIMO.ASSET@keitcart_omaxmop1_dblink S,
   MAXIMO.LOCATIONS@keitcart_omaxmop1_dblink A,
   MAXIMO.LOCHIERARCHY@keitcart_omaxmop1_dblink L1,
   MAXIMO.LOCHIERARCHY@keitcart_omaxmop1_dblink L2,
   MAXIMO.LOCHIERARCHY@keitcart_omaxmop1_dblink L3,
   MAXIMO.LOCHIERARCHY@keitcart_omaxmop1_dblink L4,
   MAXIMO.LOCHIERARCHY@keitcart_omaxmop1_dblink L5
WHERE
   S.LOCATION = A.LOCATION
   AND A.LOCATION = L1.LOCATION
   AND L1.PARENT = L2.LOCATION
   AND L2.PARENT = L3.LOCATION
   AND L3.PARENT = L4.LOCATION
   AND L4.PARENT = L5.LOCATION
   AND L4.PARENT <>'C-RETIRED'
   AND (S.DESCRIPTION LIKE '%Dampener%' OR S.DESCRIPTION LIKE '%SUCTION SCRUBBER%')
   AND S.STATUS NOT IN ('SOLD', 'SOLDCL', 'INVENTORY', 'SCRAPPED','SURPLUS FOR PARTS','DATA RECYCLE')
   AND S.ASSETNUM = P.ASSETNUM
   AND P.ASSETATTRID = 'PWABJURNO'
ORDER BY
   L5.PARENT, L4.PARENT, L3.PARENT, L2.PARENT, L1.PARENT, SUBSTR(A.LOCATION, -16), A.LOCATION),
Z AS (
SELECT
   Y.*
--this shows vessels without compressors
FROM Y
LEFT JOIN X ON Y.LSD = X.LSD
WHERE X.LOCATION IS NULL
ORDER BY Y.SUPI, Y.BUDGET, Y.PROP, Y.FIELD, SUBSTR(Y.LSD, -14), Y.LOCATION)
SELECT Z.*, Q.DESCRIPTION, Q.INSERV, Q.LOCATION
FROM Z
LEFT JOIN ABSA20151209 Q ON Z.ANUM = Q.ANUMFMT
;