Please help - Complex SQL failing!

[chrispayze]

Hi all,

I'm new to the board, and relatively new to EA, so please go easy on me! I'm trying to write a slightly complex SQL search to produce an Information Exchange table as a csv export, which has the following columns:
    Connector Name
    Source Object Name
    Destination Object Name
    Connector tagged value1
    Connector tagged value2
    etc.

I've identified the source tables as t_object, t_connector and t_connectortag. I've got the following statements working:

To match up connector start object ID to object name:
SELECT t_connector.Name, TO1.Name AS Source, t_connector.End_Object_ID
FROM t_connector
LEFT JOIN t_object AS TO1
ON t_connector.Start_Object_ID=TO1.Object_ID

I also restrict the returned connectors by stereotype using a WHERE statement, which seems to work well, but I've left it out so as to not complicate the issue here.

And the same for matching end object ID to object name:
SELECT t_connector.Name, TO2.Name AS Destination, t_connector.Start_Object_ID
FROM t_connector
LEFT JOIN t_object AS TO2
ON t_connector.End_Object_ID=TO1.Object_ID

1st problem: I can't get these two statements to combine, by nesting joins or similar, it just gives me a 'Syntax error (missing operator) in query expression' when I run the following:
SELECT t_connector.Name, TO1.Name AS Source, TO2.Name AS Destination
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

2nd problem: I'm not really sure where to start in getting the Tagged Value property to a column name and the Value as the data in the column, matched up to the Connector ID. Any ideas?

The tagged values are consistent across the stereotype, as I've defined the stereotype in a profile.


Any help greatly appreciated, and I hope I've described the problem adequately.

Rgds
Chris

[chrispayze]

I've got a bit further with the tags, as follows:

SELECT t_connector.Connector_ID, t_connector.Name, t_connectortag.Property, t_connectortag.Value
FROM t_connector
LEFT JOIN t_connectortag
ON t_connectortag.ElementID=t_connector.Connector_ID

This gives a list of every tag for every connector (although I do again filter thsi using a Where statement to narrow it down to a single stereotype).

If I could combine the three SELECT statements, I'd be nearly there!

[chrispayze]

I've solved problem 1! It was all a matter of having brackets separating the JOIN statements. I've now got a list of the connectors with tagged values, but obviously the connectors are repeated for each tagged value. What I really want is to show the tagged values as columns (tag name as the column heading, value as the data in the column), so that the connectors only show once each.

This is what I've got so far:

SELECT t_connector.ea_guid AS CLASSGUID, t_connector.Connector_Type AS CLASSTYPE, t_connector.Connector_ID, t_connector.Name, t_connector.Stereotype , t_connector.Connector_Type, TO1.Name AS Source, TO2.Name AS Destination, t_connectortag.Property, t_connectortag.VALUE
      FROM (((t_connector
            LEFT JOIN t_object AS TO2
                  ON t_connector.End_Object_ID=TO2.Object_ID )
            LEFT JOIN t_object AS TO1
                  ON t_connector.Start_Object_ID=TO1.Object_ID)
            LEFT JOIN t_connectortag
                  ON t_connector.Connector_ID=t_connectortag.ElementID)
WHERE t_connector.Stereotype='IER'

[qwerty]

You should also tell us which RDBMS you're using.

q.

[Geert Bellekens]

Chris,

You can join the t_connectortag multiple times, and include the name of the tag in the join as

Code: [Select]

SELECT t_connector.ea_guid AS CLASSGUID, t_connector.Connector_Type AS CLASSTYPE, t_connector.Connector_ID, t_connector.Name, t_connector.Stereotype , t_connector.Connector_Type, TO1.Name AS Source, TO2.Name AS Destination,
t1.VALUE as T1,t2.VALUE as T2
     FROM ((((t_connector
           LEFT JOIN t_object AS TO2
                 ON t_connector.End_Object_ID=TO2.Object_ID )
           LEFT JOIN t_object AS TO1
                 ON t_connector.Start_Object_ID=TO1.Object_ID)
           LEFT JOIN t_connectortag t1
                 ON t_connector.Connector_ID=t1.ElementID and t1.Property = 'T1')
           LEFT JOIN t_connectortag t2
                 ON t_connector.Connector_ID=t2.ElementID and t2.Property = 'T2')
WHERE t_connector.Stereotype='IER'
Geert

[chrispayze]

Thanks for the help everyone, it's working almost as I want it now. I still haven't solved my 2nd problem though. A better description below (apols if names are slightly wrong, I'm doing it from memory):

My Leftjoin statements get me to this (abridged!):

Name   Start_Object    End_Object  
IER1        S1                  Si1
IER2        S2                  Si2
IER3        S3                  Si3

t_connectortag colums of interest:

Name    TagName    TagValue
IER1        Tag1             xxx
IER1        Tag2             yyy
IER1        Tag3             zzz
IER2        Tag1             aaa
IER2        Tag2             bbb
IER2        Tag3             ccc
IER3        Tag1             ppp
IER3        Tag2             qqq
IER3        Tag3              rrr


The following is fairly simple to achieve, but not quite what I want, because of all the repetition:

Name    TagName    TagValue    Start_Object    End_Object
IER1        Tag1             xxx          S1                  Si1
IER1        Tag2             yyy          S1                  Si1
IER1        Tag3             zzz           S1                  Si1
IER2        Tag1             aaa          S2                  Si2
IER2        Tag2             bbb          S2                  Si2
IER2        Tag3             ccc           S2                  Si2
IER3        Tag1             ppp          S3                  Si3
IER3        Tag2             qqq          S3                  Si3
IER3        Tag3              rrr           S3                  Si3


What I really want is to extract the discrete values of TagName and create columns, then put the corresponding TagValue values in the appropriate column against the appropriate Name:

Name    Source       Sink    Tag1    Tag2    Tag3
IER1        S1            Si1     xxx      yyy      zzz
IER2        S2            Si2     aaa      bbb      ccc
IER3        S3            Si3     ppp      qqq       rrr


Any ideas?

[Geert Bellekens]

See my previous reply, that should work as you intend.

Geert

[chrispayze]

Just tried it again, I just keep getting the error: DAO. QueryDef [3296]  Join expression not supported.

Should there be an'AS' between to connector tag and t2? Not that it seems to make a difference.

Rgds
Chris

[qwerty]

As I said: tell us your RDBMS. SQL is not equal to SQL

q.

[chrispayze]

qwerty, what's a RDBMS? I'm just using the SQL editor in the 'project find' window within EA10.

[qwerty]

Relational DataBase Management System. As you use the internal SQL editor it still unclear. Are you using EAP files? In that case you have MS Access SQL syntax which likely differs from that presented by Geert (using AFAIK MS SQL Server).

q.

[chrispayze]

Aha, that makes sense. Yes, I'm using a normal EAP file, created within EA. What should the syntax change be to allow geert's suggestion to work?

Thanks for taking the time to input to this.

[qwerty]

Hmm. This not really an SQL forum. You should consider Google to learn the differences between Mickeysofts two SQLs. (Or may some user has the answer but likely earliest Monday.)

q.

[Geert Bellekens]

Hmm, apparently MS-Access doesn't like multiple expressions in a join statement.
Solved by moving them into the where clause like this:

Code: [Select]

SELECT t_connector.ea_guid AS CLASSGUID, t_connector.Connector_Type AS CLASSTYPE, t_connector.Connector_ID, t_connector.Name, t_connector.Stereotype , t_connector.Connector_Type, TO1.Name AS Source, TO2.Name AS Destination,
t1.VALUE as T1,t2.VALUE as T2
    FROM ((((t_connector
           LEFT JOIN t_object AS TO2
                 ON t_connector.End_Object_ID=TO2.Object_ID )
           LEFT JOIN t_object AS TO1
                 ON t_connector.Start_Object_ID=TO1.Object_ID)
           LEFT JOIN t_connectortag t1
                 ON t_connector.Connector_ID=t1.ElementID)
           LEFT JOIN t_connectortag t2
                 ON t_connector.Connector_ID=t2.ElementID )
WHERE t_connector.Stereotype='IER'
 and t1.Property = 'T1'
 and t2.Property = 'T2'
Geert

[chrispayze]

Great, that seems to be working a treat. Thanks very much. Especially once I'd woken up this morning and realised I should be replacing the T1, T2 etc with my actual tag names! Talk about a 'dim moment'!  Just shows how time away and a fresh look can help.

For presentational purposes, some of my column headings have two words, separated by a space, rather than an underscore. To get the statement to read it as a title, I put apostrophes before and after the two words, but they then show up in the column heading. The single-worded headings don't have this problem. Is there a notation/syntax that allows me to identify two words as a column heading but not then transfer the identification characters to the heading?

Last problem to solve, then I promise I'll leave you all alone!

Chris