SQL search for conveyed items and source/target names

[Ramirez-UK]

I've been working with conveyed information and trying bits of script found on the forums.  I've managed to get the following SQL to list all items conveyed, and can return the Start_Object_ID and End_Object_ID:

select Connector_ID, Direction, Connector_Type, t_object.Name, Start_Object_ID, End_Object_ID from t_connector, t_xref, t_object where t_xref.Client = t_connector.ea_guid and t_xref.description LIKE '*'+t_object.ea_guid+'*'

What I'd really like is to return the names of the start and end object elements, not just the IDs.  Is there a bit of script that can do this?

I'm afraid I'm not a SQL expert and even less of a guru when it comes to finding info buried in Sparx's database!

[qwerty]

Without having tested, the following should give you the result you need:

Code: [Select]

select Connector_ID, Direction, Connector_Type, t_object.Name, so.name, eo.name
from t_connector, t_xref, t_object, t_object so, t_object eo
where t_xref.Client = t_connector.ea_guid and t_xref.description LIKE '*'+t_object.ea_guid+'*' and so.object_id = Start_Object_ID and eo.object_id = End_Object_ID
q.

[Ramirez-UK]

Many thanks, that gives exactly the result I need and it's quick and simple too!

[Ramirez-UK]

OK, an update on this topic - I've been busy building up some models using conveyed items, but storing this all in a local eap file.  I've then transferred this into my main SQL Server repository via an XMI import. Having done this, the custom search I created above now no longer works.  As far as I can see the search isn't timing out - it's taking longer but seems to complete - but comes up with no results at all.

I don't understand how a simple search can work OK in a local file and not on a shared repository.  To be clear, it's exactly the same search, held under 'My Searches'.

Any ideas?

[qwerty]

Instead of * you need a %.

q.

[KP]

Instead of * you need a %.

q.

Or use '#WC#' which will substitute the appropriate wildcard character for the database you're running the search on.

[Ramirez-UK]

Thanks both.  I hadn't realised that the search syntax was slightly different depending on whether you were in an eap file or a shared server.

The wildcards now work but I've run into another issue.   To confirm the search query I'm using is:

Code: [Select]

select Connector_ID, Direction, Connector_Type, t_object.Name, so.name, eo.name
from t_connector, t_xref, t_object, t_object so, t_object eo
where t_xref.Client = t_connector.ea_guid and t_xref.description LIKE '#WC#'+t_object.ea_guid+'#WC#' and so.object_id = Start_Object_ID and eo.object_id = End_Object_ID and t_object.name LIKE '<Search Term>'
In the eap file the search returns the following columns:

Connector_ID | Direction | Connector_Type | t_object.name | so.name | eo.name

and the search results come up fine, listing the information conveyed, the start element and end element.  On the server, the same query comes back as:

Connector_ID | Direction | Connector_Type | Name | name | name

The first and third name columns are populated with the end element; the start element and the name of the information conveyed doesn't show up at all.  Maybe there's another syntax issue I need to correct in the query?

[qwerty]

Also without testing: replace the first line like this (I think you get the idea):

Code: [Select]

select Connector_ID, Direction, Connector_Type, t_object.Name as "Obj-Name", so.name as "Start-Name", eo.name as "End-Name"
That should result in a unique display.

q.

[Ramirez-UK]

Thank you that's done the trick.