Extracting Conveyed Items using SQL query

[chrispayze]

Hi all,

I'm trying to create a list of connectors (Information Flows of stereotype 'IER'), with their start and finish element names, and their conveyed Information Item element names. I've almost got it, by Left Joining t_object and t_connector multiple times, and using t_xref as the link table between the connector and the info item element. However, it stores the ea_guid of the info item element within a 'memo' field type, so it won't let me do my final Left Join. I'm banging my head against a wall trying to think of a work-around. Has anyone got any ideas? Code below:

SELECT t_connector.ea_guid AS CLASSGUID, t_connector.Connector_Type AS CLASSTYPE, t_connector.Connector_ID AS ID, t_connector.Name,  TO1.Name AS Source, TO2.Name AS Destination, XCON.Description AS [xref conveyed item guid], TO3.Name AS [Conveyed Item]
      FROM ((((t_connector
            LEFT JOIN t_object AS TO1
                  ON t_connector.Start_Object_ID=TO1.Object_ID)
            LEFT JOIN t_object AS TO2
                  ON t_connector.End_Object_ID=TO2.Object_ID)
            LEFT JOIN t_xref AS XCON
                  ON t_connector.ea_guid=XCON.Client)
            LEFT JOIN t_object AS TO3
                  ON XCON.Description=TO3.ea_guid)

WHERE t_connector.Stereotype='IER'  
AND XCON.Behavior='conveyed'


Any help gratefully appreciated.
Chris

[chrispayze]

It's amazing how writing something down can help you think better, plus a bit of searching on general SQL fora. I didn't really need to join all the tables, as I was just extracting data from them, so I reduced by LEFT JOINs to 'Where' statements. You can use memo fields in 'Where' statements. The downside of this is that it won't work if there is more than one information item conveyed (when EA puts both guids in the same Description field, separated by commas).


SELECT t_connector.ea_guid AS CLASSGUID, t_connector.Connector_Type AS CLASSTYPE, t_connector.Connector_ID AS [IER ID], t_connector.Name,  TO1.Name AS Source, TO2.Name AS Destination, TO3.Name AS [Info Item Conveyed], t1.VALUE AS [Data Size], t2.VALUE AS Unit, t3.VALUE AS Frequency, t4.VALUE AS [Protective Marking], t5.VALUE AS Timeliness, t6.VALUE AS [Applicable Standards], t7.VALUE AS Provenance, t8.VALUE AS Validation

FROM t_connector, t_object AS TO2, t_object AS TO1,  t_connectortag AS t1, t_connectortag AS t2,  t_connectortag AS t3, t_connectortag AS t4,  t_connectortag AS t5,  t_connectortag AS t6,  t_connectortag AS t7, t_connectortag AS t8, t_xref AS XCON, t_object AS TO3

WHERE t_connector.Stereotype='IER'  
AND t1.Property='Data Size'
AND t2.Property='Unit'
AND t3.Property='Frequency'
AND t4.Property='Protective Marking'
AND t5.Property='Timeliness'
AND t6.Property='Applicable Standards'
AND t7.Property='Provenance'
AND t8.Property='Validation'
AND t_connector.End_Object_ID=TO2.Object_ID
AND t_connector.Start_Object_ID=TO1.Object_ID
AND t_connector.Connector_ID=t1.ElementID
AND t_connector.Connector_ID=t2.ElementID
AND t_connector.Connector_ID=t3.ElementID
AND t_connector.Connector_ID=t4.ElementID
AND t_connector.Connector_ID=t5.ElementID
AND t_connector.Connector_ID=t6.ElementID
AND t_connector.Connector_ID=t7.ElementID
AND t_connector.Connector_ID=t8.ElementID
AND XCON.Behavior='conveyed'
AND XCON.Description=TO3.ea_guid
AND t_connector.ea_guid=XCON.Client

Does anyone know a cute way of searching within a memo field for a string? The 'LIKE' operator doesn't seem to work in this way, even when using wildcards infront and/or behind it. The section in question is:

AND XCON.Description LIKE TO3.ea_guid

I've tried '%'+FIELDNAME+'%', but it doesn't seem to work (except for those with only one item conveyed, just like the original statement.

Rgds
Chris

[Geert Bellekens]

Chris,

You haven't said which database you are using, but I think in most cases you can cast the fields to a varchar or something like that.
In that case you can do a like on the field.

Geert

[Geert Bellekens]

On SQL Server this query works to get all conveyed items from the connectors.

Code: [Select]

select * from t_connector c
join t_xref x on x.Client = c.ea_guid
                         and x.Behavior = 'conveyed'
join t_object o on x.Description like '%' + o.ea_guid + '%'
Geert

[Geert Bellekens]

Hmm, looking at the number of '(((' in your query it looks like you are on MS-Access (.eap).

In that case the wildcard character is '*' and not '%'

Geert

[chrispayze]

Finally found the time to try this! Thanks for the suggestion. My issue is that EA won't let me conduct a JOIN on a memo field, which t_xref.Description is. Hence I moved to using 'Where' statements instead of joining the tables.

You are correct that I'm just creating my searches within EA, using a .eap file (MS Access).

The wildcard within the final 'LIKE' statement doesn't seem to work whether I use '*' or '%' if there is more than one info item conveyed on a connector. The results for that connector just don't appear in the search results in this case. It works fine if only one info item is conveyed on the connector.

[TRad]

I have the same issue as described here--I think it was never completely answered.  I am trying to basically the same query as Chris--and I have tried to use CAST as suggested by Geert.  I am not sure where else to load my database to practice the queries other than in MS Access, so that is what I am using.  I kept getting an error [Syntax error (missing operator)] when using CAST, but Cvar worked: 

SELECT t_connector.Name, t_object.Name, t_object_1.Name, t_object_2.Name
FROM (((t_connector INNER JOIN t_object ON t_connector.Start_Object_ID = t_object.Object_ID)
INNER JOIN t_object AS t_object_1 ON t_connector.End_Object_ID = t_object_1.Object_ID)
INNER JOIN t_xref ON t_connector.ea_guid = t_xref.Client)
INNER JOIN t_object AS t_object_2 ON Cvar(t_xref.Description) = t_object_2.ea_guid
WHERE (((t_connector.Stereotype)="ResourceExchange") AND ((t_xref.Behavior)="conveyed"));

Now, to see what works in Sparx SQL queries.