SQL query for connections with name="External Reference"

[ngong]

Dear SQL specialist,
I would appreciate some help on creating a query. I am missing some very first steps.
I did SQL some decades ago, read some examples from Geert, scanned "enterprise-architect-object-model.pdf". I can do a query like

select c.Connector_ID as id, c.name from t_connector c where c.name="External Reference"

and get two columns. What I need is, for each row the name and type of ClientEnd and SupplierEnd.
A benefit would be the containing package name for each.
 
But if I add anything to my query I always get "Runtim error 3061. Too few parameters. Expected 1."
(e.g. the pdf says t_connector has a column Alias and ClientID, but c.Alias or c.ClientID leads to the error)

What did I miss?

[Geert Bellekens]

Rolf,

I'd suggest you get a proper SQL tool to write your queries.
I'm using AnySQL Maestro when writing queries for an .eap model.

Such tools have intellisense and much better error handling than EA.
Only when my query works in that tool I will copy/past it in EA.

Geert

PS. EA also has some autocomplete feature  for column names, but that only works when you type in the full table table e.g. "t_connector"

[Nizam]

Geerts suggestion is best to create useful queries, but Coincidentally i had something similar written for reporting purpose

Code: [Select]

select c.Name as Name, ce.Name as ClientName,  ce.Object_Type as ClientType, se.Name as SupplierName, se.Object_Type as SupplierType   
from (( t_connector c
left join t_object ce on ce.Object_ID = c.End_Object_ID )
left join t_object se on se.Object_ID = c.Start_Object_ID )
where c.Name ='<Name>'


[ngong]

wonderful, Nizam - works out of the box. Now I learned e.g. that column Start_Object_ID is foreign key to t_object.

I will try to convince IT to install AnySQL Maestro on the company laptop I am using.

However: where to read further? Not so much SQL, but EA relational schema. Start_Object_ID does not appear in enterprise-architect-object-model.pdf. I am obviosly reading the wrong document. Which of the documents in www.sparxsystems.com.au/resources/user-guides holds that information?

However, I installed AnySQL on my private computer and it seems that I get all most needed information just from connecting to an .eap file. Thank you Geert.

[Nizam]

Glad it helped. You can refer to https://leanpub.com/InsideEA from Thomas Kilian

[Geert Bellekens]

The object model is the model for the API, not for the database.

You can simply reverse an .eap file in EA to get the database structure and/or get Thomas's book.

Geert

[KP]

And don't forget that you can use intellisense. Unfortunately, when typing a SQL statement like this into EA's SQL builder

select c.Connector_ID as id, c.name from t_connector c where c.name="External Reference"

you don't get intellisense when you type c. but if you type t_connector. then you will see all the fields available in that table.

[Geert Bellekens]

you don't get intellisense when you type c. but if you type t_connector. then you will see all the fields available in that table.

Would be nice if that could be fixed. Nobody uses full table names when writing queries.
I actually never write or edit a query directly in EA because of the missing intellisense on aliases.

Geert