Updating SQL queries for AMDS: what RAMM/AWM users need to know.

When the Asset Management Data Standard (AMDS) is implemented in RAMM/AWM, it changes the way you work with the assets in your database.

During the AMDS go-live week, the data from the classic asset tables is copied and transformed into a new set of AMDS conformant asset tables.

In that same data migration process, those classic asset tables (where the data has been copied from) are converted to being read-only. And depending on your role and/or how far along you are from completing your migration week, you may lose, or have already lost visibility of those classic tables altogether.

As the AMDS conformant tables are a brand new set of user defined tables in your database, this is a pretty fundamental change to how you view and work with your assets in RAMM/AWM. And if you use SQL in the SQL Management app to query asset tables, these changes will have an impact on you too.

Can you use SQL to query an AMDS asset table?

So you may be wondering if you can actually still use the SQL Management application in RAMM / AWM, to write and run SQL queries on the new AMDS tables.

Well, good news.

The short answer is 'yes'.

And the slightly longer answer is 'yes you can, if your RAMM / AWM user account has the permissions to let you run SQL queries on those new AMDS tables'.

Aside from being a user defined table (UDT), an AMDS asset table can be queried in much the same way as a classic standard RAMM table can be queried. So yes, it is totally possible to query an AMDS asset table in AWM / RAMM, using the same SQL Management application that you may have used to query your classic assets.

But what about if you have an existing SQL query that works on a classic table. And you now want to use that query to do the same thing on a new AMDS table. Can you do that?

Still good news, but this one requires an even slightly longer answer ... 'yes you can, if your RAMM / AWM user account has the permissions to let you run SQL queries on those new AMDS tables, and you make the necessary updates to your existing SQL query to adapt it to work with the new AMDS table'.

Let’s unpack what that means with some simple examples, and then we will look at how that applies to some more complex SQL queries.

Before we go further... do you need to brush up on your SQL?

Before we get started... if all this sounds interesting, but you are a bit uncertain about using SQL, or you need a bit of a refresher before jumping back in, then The Datastack has you covered.

Check out this link right now! This is a free SQL Basics for RAMM course that you can start today, with self-paced lessons sent straight to your inbox.

Now onto the rest of the article.

Starting out with a basic query

In this first example, we will run a simple SELECT * FROM tablename query.

This query tells the SQL Management application that we want to return all records and columns from the table that we ask for. And for demonstration purposes in this article, we will use the Footpath (Classic) table and the new Pathway (AMDS) table.

So, starting with the classic asset table first, we need to tell the SQL Management application to query the footpath table.

Here is what the query could look like (in a format for you to copy and paste to try out)...

-- Get all columns and records from the Footpath (Classic) table
SELECT *
FROM footpath
;

And here is what it would look like in RAMM / AWM:

This example shows a quick and easy way to get all the records in the Footpath (Classic) table.

Now, adapting this same query to the AMDS environment is quick and easy too. So instead of talking the language of footpath, now in the world of AMDS we are talking about Pathways.

The table name we need to use in our query for the Pathway (AMDS) asset table is ud_pathway. So to adapt our query, all we need to do is swap out the footpath table name, and put in ud_pathway instead.

Here is the code we need (in a format for you to copy and paste to try out);

-- Get all columns and records from the Pathway (AMDS) table
SELECT *
FROM ud_pathway
;

And here is what it would look like in RAMM / AWM:

So we now have now adapted our SQL query to be in AMDS compatible within RAMM / AWM. And this was achieved with a simple table name change.

Building on our SELECT query with the TOP keyword

For this second example, we are going to use the TOP keyword to expand on our code from above. Based on the number we specify when using the TOP keyword, it will limit the number of rows that get returned in the results.

So for demonstration purposes, lets go with 5 records from the footpath table. Here is what that query could look like.

-- Get five records from the Footpath (Classic) table
SELECT TOP (5) *
FROM footpath
;

And here is what it would look like in RAMM / AWM:

If you are following along in your own database, you may notice that different footpath assets can show up in the results each time you run this query. For the purposes of this article, we are not going to drill down into the details of why this happens. But that outcome in the query results is ok and expected. Keep an eye out for a future article here on the TalkData blog where we will explore this in more detail, and take a look at the different sorting and ordering options that you can use.

Now, the same query looks pretty similar when adapting it to AMDS, as we simply need to change the table name to ud_pathway.

-- Get five records from the Pathway (AMDS) table
SELECT TOP (5) *
FROM ud_pathway
;

And here is what it would look like in RAMM / AWM:

So using the TOP keyword limits the records that will be returned in the results. And this provides a different outcome to the first SELECT * example we looked at, where we returned everything from the table.

But again, it looks and sounds pretty similar when running the query for both the classic and AMDS tables. We just needed to change the table name to get the same equivalent functional outcome from the AMDS table.

Updating your SQL queries for AMDS can get a bit trickier

With our queries above, we can see that the AMDS tables functionally work the same in the RAMM / AWM SQL Management application.

We can run some queries to access the data in our tables, and it works in very much the same way between our classic assets and AMDS assets.

But (and you knew there was a but coming), depending on what you are doing in SQL Management, and how complex your queries are, updating them to work correctly in the AMDS data environment is not always going to be as simple as changing the table name.

Beyond adding a bunch of new AMDS conformant tables to your RAMM / AWM database, the AMDS rollout has introduced many other changes to how our assets are defined and managed. To put it another way, the columns in the Pathway (AMDS) table are not a direct 1:1 match to the columns in the classic Footpath table.

And we can show that below in our next example, using something as simple as an ID.

Changing more than just the table name to query an AMDS table

Sticking with our Footpath and Pathway theme, let's use the Footpath ID column as our target column in this example.

In terms of the classic footpath assets, the Footpath ID is a unique identifier within the footpath table, that is assigned to each footpath asset . This Footpath ID is a number like 1, 392, or 2088. And every footpath record in the table has a Footpath ID. And the Footpath ID for a given record is different to every other Footpath ID in the table.

So let's say we have a query to to return a list of every Footpath ID in our footpath classic table. It could look something like this...

-- Return a list of every Footpath ID from the Footpath table
SELECT footpath_id
FROM footpath
;

And here is what it would look like in RAMM / AWM:

Great, so this works nicely for our classic footpath assets. Running this query will give us a list of every Footpath ID that exists in the table.

But what about updating this to work for AMDS?

If we simply change the table name to ud_pathway to query the AMDS Pathway table, what is going to happen? Here is what it would look like in RAMM / AWM (plus an additional comment line to give an indication of the expected result) :

If you run this query on a typical Pathway (AMDS) table, you will quickly find out that you are going to get an error like this...

An Invalid column name error. Why has this happened?

The footpath_id column is not a thing in the AMDS standard, and does not exist in the Pathway (AMDS) table we are querying above. And the SQL Management application is telling us that it can't get the information we are asking for.

So if we wanted to adapt our existing query, we would need to use the AMDS friendly column name of system_id, which is effectively the functionally equivalent ID in the Pathway (AMDS) table.

Here is what that SQL code could look like.

-- Return a list of every Asset ID (system_id) in the Pathway AMDS table
SELECT system_id
FROM ud_pathway
;

And here is what it would look like in RAMM / AWM:

This is a very quick example of where a specific column that we were originally querying (footpath_id) now no longer exists in AMDS. So to adapt our query above, not only did we need to change the table name, but the column name being referenced in the query need to be updated too.

Depending on the complexity of your query, the SQL code for an AMDS table can end up looking quite a bit different a functionally equivalent query that you may have been using on a classic asset table.

A very subtle plug for when things get even trickier

In today's article we have seen that you can use SQL Management in RAMM/AWM to query an AMDS table. And this can look and work in much the same way as querying a classic asset table.

We have also seen that if you have existing queries that are designed for classic asset tables, these will need to be adapted to work in the world of AMDS once implemented in your RAMM / AWM database. Depending on the complexity of your queries, these changes can range from a relatively simple table name change, through to a potentially far more extensive set of necessary changes.

And here comes the very subtle plug… If you need a hand with updating your SQL to work with your AMDS data, The Datastack is here to help.

With extensive experience reviewing and upgrading existing SQL queries to align with the AMDS data environment, and developing new SQL solutions for your AMDS assets, The Datastack will make this entire process very easy and effective for you.

When you are ready to get started, reach out here for a chat.

---