Link Tag Query to SQL Query

[Former Member]

I have tag values that are steps or alarm codes in a process and I want to create a cross reference SQL table for these values. Using BLS, how do you link these tables to have an Xacute query that will return the cross referenced values from the tag?

I appreciate any help that can be provided.

Thanks

Larry

[Former Member · Accepted solution]

Hi, Larry.

Help us understand a bit more detail about your application.

In general, you'll need some "context" on which to link the tables...tag name, tagname + timestamp, and so on. You can then use techniques such as the Joiner action or a looping/lookup approach to link them together. Depending on the size of the dataset, the Joiner might be the cleanest approach.

- Rick

[Former Member · Accepted solution]

Rick,

Thank you for your reply. In general, my SQL table holds the following 3 columns; TagName, TagValue, TextMessage. My report will be reading tags from the InSQL data historian database and I wish to lookup the TextMessage associated with the value (TagValue) of a particular TagName.

Based on your comments, I think you are correct that the Joiner function may work best, but am not totally familiar with its use. Could you point me in the direction of the sequence block necessary? As of now I have the following sequence occuring:

Sequence 1 - Tag Query that queries the tags for reference

Sequence 2 - Repeater that runs through each of the rows returned above

Sequence 3 - Tracer to validate the that the other 2 sequences are working

That is as far as I got so far.

I appreciate your help!

Larry

[Former Member · Accepted solution]

Rick,

One other note I missed in my last e-mail, the Tag Query does not return the TagName as a value in a column but as the column header for the value of the tag. How is it possible to get this as the value of its own column to be used as a link in the Joiner action.

Current query returns

DateTime XV43003 -

---

(header row)

03/22/2007 03:30:00 0

03/22/2007 03:31:00 2

03/22/2007 03:32:00 8

Preferred method

DateTime TagName Value -

---

(header row)

03/22/2007 03:30:00 XV43003 0

03/22/2007 03:31:00 XV43003 2

03/22/2007 03:32:00 XV43003 8

This would give me both the tagname and value to join to the sql table.

Thanks for your help!

Larry

[Former Member · Accepted solution]

Larry, what does your repeater do?

You could probably use it to build the DateTime, TagName, Value rows and insert them into an illuminator document.

For example: Create a document with the columns you want at the start of your transaction using a document action block, each time you repeat through the results of your tag query, append the DateTime and Value from the repeater and the TagName from a Transaction Property via a row action block.

When the repeater completes you will have a new document which you should then be able to join to your message data.

Regards,

[Former Member · Accepted solution]

Andrew,

Thanks for your help. Your suggestions have moved me into the right direction. I now have another issue. I am attempting to make the Tag Query generic so that I can pass in the Tag that I wish the query to access. The value column has a column header that is the same as the tag name that is queried, which does not allow me to link to the SQL table. How can I change the header column to a generic "TagValue" name so that I can set up the transaction to allows link the TagValue column to the SQL lookup table. Otherwise, I have to create a separate transaction for each of the tags I plan to look up.

I appreciate you help!

Larry

[Former Member · Accepted solution]

Hi, Larry.

You can use the "ColumnAlias" action to do this. The source and destination names can be provided as links.

- Rick

[Former Member · Accepted solution]

Rick,

Thanks for your suggestion. The "ColumnAlias" action fixed my problem. I now have an Xacute query that links Tag values to a standard SQL data table to retrieve the messages associated with the tag. There does appear to be one bug though.

When I execute the tag query with the date range, I get the correct data. Also when tracing the Xacute query with the same date range the trace data looks correct. When running the Xacute query inside xMII (Illuminator) the first record shows a different time. In the other 2 methods, the time is the time associated with the Start Date parameter. On the Xacute method, the time appears to be the time of the last record. The data is sorted correctly, just the wrong data appears in the first record and all other records are correct.

Can you test this out and let me know if this is a bug or not? I also tested with just a simple Xacute query that queries the tag values and outputs them to the outside world (removed linking to SQL table).

Thanks,

Larry

[Former Member · Accepted solution]

In your last case (simple test), did you also have a ColumnAlias in there or just a simple tag query -> output transaction?

[Former Member · Accepted solution]

Yes the ColumnAlias was part of the Xacute query. I have since removed but still get the same results.

Thanks,

Larry

[Former Member · Accepted solution]

Weird. I can't duplicate it here (I'm using the simulator tag connector). Works exactly as expected.