Retrieving latest version of tag value for report using SQL

[Richard Burke]

Hello,
I am using SQL to create a requirement table for the custom section of a report.

I have extracted the requirements from the t_object table successfully but now we would like to add some of the requirement attributes that are stored in tags. I can access those too using the t_objectproperties table and linking on OBJECT_ID.

The problem is that some requirements have multiple tags with the same name - I think this was caused by using Reqtify to import them. However, EA shows only one value for each type of tag unless I turn on the Objects, "Show Duplicate Tags" tick box in the Options.

I would like to be able to access the latest version of the tagged attribute. In other words, if a user changes a tagged value using EA, I would like to be able to retrieve that value.

I can confirm that the the latest version of the property is neither the one with the lowest nor the highest PropertyID.

EA must be retrieving the latest value, so how do I do it in SQL?

Thanks in advance for your help.
Best wishes,
Richard

[Paolo F Cantoni]

Hi Richard,

There is no fact in the t_objectproperties table to get you the timepoint of the tagged value.  The best you can do is to use the PropertyID value since it is a monotonically increasing number and therefore, the higher the number, the more recent the value.

HTH,
Paolo

[Geert Bellekens]

I'm not sure EA is retrieving the latest value. Could be that EA is simply retrieving "a" value, whichever is returned first by the database. 

Geert

[Paolo F Cantoni]

I'm not sure EA is retrieving the latest value. Could be that EA is simply retrieving "a" value, whichever is returned first by the database. 

Geert

To use a famous TV quote (from the BBC TV "house of Cards"): "You might well think that. I couldn't possible say that!"

+1

Paolo

[Glassboy]

<ob nitpick>"You might very well think that; I couldn't possibly comment"<ob/>

Or possibly "wouldn't say that".  Francis Urquhart hs excellent grammar, and wouldn't improperly use "could".

[Paolo F Cantoni]

<ob nitpick>"You might very well think that; I couldn't possibly comment"<ob/>

Or possibly "wouldn't say that".  Francis Urquhart hs excellent grammar, and wouldn't improperly use "could".

I see your nitpick and raise, Sir!
<ob nitpick²"> Urquhart is characterised by his habitual breaking of the fourth wall, his quoting of Shakespeare, and his usage of the catchphrase, "You might very well think that; I couldn't possibly comment", or a variation thereon, as a plausibly deniable way of agreeing with people and/or leaking information. https://en.wikipedia.org/wiki/Francis_Urquhart </ob>

[Richard Burke]

Thanks for the feedback.

Francis Urquhart aside (never watched it myself), it's definitely not picking up the latest value of PropertyID. I agree with Geert that it is picking up "a" value but the million dollar question is how it is choosing which one to display and update when the user edits the field.

I thought about running a script to delete the duplicates (at the moment the values are all the same) but I am worried that EA might not pick up any of the values or might create yet another duplicate. I will give this a go and let you know.

Thanks,
Richard

[Richard Burke]

I deleted all the linked multiple entries from the t_objectproperties table for one object and confirmed that EA correctly displays the remaining single value.

I also edited the value in EA and confirmed that the database updates the single entry.

All I need to do now is come up with an SQL script that deletes duplicate entries.

It would still be nice to know how EA is deciding which entry to use.

Best wishes,
Richard

[Glassboy]

<ob nitpick>"You might very well think that; I couldn't possibly comment"<ob/>

Or possibly "wouldn't say that".  Francis Urquhart hs excellent grammar, and wouldn't improperly use "could".

I see your nitpick and raise, Sir!
<ob nitpick²"> Urquhart is characterised by his habitual breaking of the fourth wall, his quoting of Shakespeare, and his usage of the catchphrase, "You might very well think that; I couldn't possibly comment", or a variation thereon, as a plausibly deniable way of agreeing with people and/or leaking information. https://en.wikipedia.org/wiki/Francis_Urquhart </ob>

You miss my point.  Unless I'm mistaken (it has been a year or so since I watched it) he never said "I couldn't say that".  He also wouldn't as he knows he is physically capable of the act of saying - he wouldn't or he shouldn't but he most definitely can.  The tag line also hinges on "comment"; another politician would say "no comment".  FU says something that you might well think is "no comment", but you might well think is the total opposite.

[Glassboy]

It would still be nice to know how EA is deciding which entry to use.

Richard, that's probably part of the whimsey feature set :-)

[Eve]

There's an option in the tagged values window to display duplicates. If it's off, the one displayed depends on the order they are returned from the database.

[Richard Burke]

Just to close this one down, the general consensus is that if display duplicates is turned off, EA is displaying the first tag value read from the database. This is neither the highest nor the lowest PropertyID so that makes it difficult to work out using SQL.

In the end, I took the approach of deleting duplicates but no doubt when I resynchronise with Reqtify I will get another set of duplicates. As suggested in one of the previous posts on this thread, the best bet is probably to display all tags and manually delete the unwanted/ out of date ones.

Richard

[Geert Bellekens]

Any chance you can tackle the problem at the source, and avoid the duplicates to be created in the first place?

How do you synchronize? Via CSV import/export, or a custom interface ?

Geert

[Richard Burke]

Hi Geert,

I am using a tool called Reqtify (which is what Rational Rhapsody Gateway is built on). Unfortunately I have no control over how it does the import.

It's a bit annoying but my SQL script successfully deletes duplicates.

If anybody wants the SQL scripts I have included them below. They are only tested with MySQL so may not work with other databases. Substitute the systemsengineering bit with whatever your database is called (I am using a tool called FlySpeed and there are multiple databases on the server).

This one shows the duplicate tags:

Code: [Select]

Select Distinct
  t1.*
From
  systemsengineering.t_objectproperties t1 Inner Join
  systemsengineering.t_objectproperties t2
    On t1.PropertyID < t2.PropertyID And t1.Value = t2.Value And
    t1.Property = t2.Property And t1.Object_ID = t2.Object_ID
Order By
  t1.Property,
  t2.Value

This one deletes them:

Code: [Select]

Delete t1
From
  systemsengineering.t_objectproperties t1 Inner Join
  systemsengineering.t_objectproperties t2
    On t1.PropertyID < t2.PropertyID And t1.Value = t2.Value And
    t1.Property = t2.Property And t1.Object_ID = t2.Object_ID
Regards,
Richard

[Geert Bellekens]

I am using a tool called Reqtify (which is what Rational Rhapsody Gateway is built on). Unfortunately I have no control over how it does the import.

Ok, too bad. I guess your workaround is the best possible solution given the circumstances.
Thanks for sharing your solution.

Geert