MS Acess SQL Query vs EA SQL Query returns different results

[yaccoff]

Hi there,

Some time ago an SQL query was devised to parse an exported EA DB to Microsoft Access: The query took 30 minutes to run:

SELECT DISTINCT (SELECT to.Name FROM t_Object to WHERE tc.End_Object_ID = to.Object_ID), (SELECT to.Stereotype FROM t_Object to WHERE tc.End_Object_ID = to.Object_ID),
(SELECT to.Name FROM t_Object to WHERE tc.Start_Object_ID = to.Object_ID), (SELECT to.Stereotype FROM t_Object to WHERE tc.Start_Object_ID = to.Object_ID),
Nz(to.Alias, to.Name),  tp.Name, (SELECT t2.Name FROM t_package t2 WHERE t2.Package_ID = tp.Parent_ID),
(SELECT t3.Name FROM t_package t3 WHERE t3.Package_ID = (SELECT t2.Parent_ID FROM t_package t2 WHERE t2.Package_ID = tp.Parent_ID)),
(SELECT t4.Name FROM t_package t4 WHERE t4.Package_ID =  (SELECT t2.Parent_ID FROM t_package t2 WHERE t2.Package_ID = (SELECT t2.Parent_ID FROM t_package t2 WHERE t2.Package_ID = tp.Parent_ID))),
(SELECT to4.Name FROM t_Object to4 WHERE to4.Object_ID = to.ParentID)
FROM t_package tp, t_object to, t_connector tc
WHERE to.Package_ID = tp.Package_ID AND to.Object_Type = "Activity"  AND (to.Object_ID = tc.Start_Object_ID)

Recently with an explosion in contributions to the EA model, the model has got a lot bigger and the query now takes 2.5hrs!!!! It produces a 10 * 3073 output table, which we import to Excel.

What I want to do, is use the power of the EA QSL Query and produce the same results. Attempts so far:

select distinct po.ea_guid as CLASSGUID, po.Object_Type as CLASSTYPE, tEndObj.Name as [Deliverable], tEndObj.Stereotype as [StereoType], po.name as [Object Name]
, po.Alias As [Alias], tp.name as 'Package Name' ,tp2.name as 'Package level -1',tp3.name as 'Package level -2',tp4.name as 'Package level -3', to2.name as [Object Parent]
from ((((((( t_package tp
inner join t_object po on tp.Package_ID = po.Package_ID)
inner join t_object to2 on po.ParentID = to2.object_ID)
inner join t_connector tc on po.Object_ID = tc.Start_Object_ID)
left join t_package tp2 on tp2.Package_ID = tp.Parent_ID)
left join t_package tp3 on tp3.Package_ID = tp2.Parent_ID)
left join t_package tp4 on tp4.Package_ID = tp3.Parent_ID)
inner join t_object tEndObj on tEndObj.Object_ID = tc.End_Object_ID)
where
po.Object_Type in ('Activity')

The result of this query (take 3 seconds ish) and give the same columns (ish) I get what I need, but the rows count is short by about 13 rows. Its vital I get the same output in terms of rows.

Any ideas....

[qwerty]

You should ask a SQL guru (maybe on StackOverflow). I'm none, but the way you use JOINs absolutely improves performance. So that's what you experience here. However, WHY the results differ needs a lot of experience. Maybe Geert can see that?

q.

[yaccoff]

A fair response, given that I am not an SQL Query expert either. I am obviously fairly close to working the SQL Query and what I really need is somebody who can walk the data structures in the same way as the original SQL query

[qwerty]

There are not that many SQL gurus here (I'd guess). And since you seek the difference only you can ask this well on SO (though they sometimes tend to bash certain questions). Just give it a try.

q.

[Geert Bellekens]

It would take a real "Sherlock Holmes" effort to find why the two queries are producing different results.
And not a very fun effort at that.

Sorry I pass

But it is a fact that the new query is 100 x more readable then the old one.

Geert

[Paolo F Cantoni]

Hi there,

Some time ago an SQL query was devised to parse an exported EA DB to Microsoft Access: The query took 30 minutes to run:

[SNIP]

(my emphasis)
Exactly what does the highlighted phrase mean?

Paolo

[qwerty]

Sounds like they moved from another DB to EAP.

q.

[Geert Bellekens]

Your inner join with tEndObj will exclude any objects that don't have a parent object.
changing that to a left join might bring up the missing records.

Geert

  Damn, now I did it anyway

[yaccoff]

In response to Paolo's question, the instructions provided to us were to run up MS Access and Open the EAP file, which converts the  EAP to an .aacdb file, so my use of the term 'export' was misleading.

Geert (Sherlock) thx for your efforts, but the suggested change doesn't produce the missing items. What I intend to do next is compare the output columns for the original query and the new query and identify the EAP elements that it isn't finding and deduce why the select statement isn't finding these 13 items.

[yaccoff]

Apologies in advance, but my next question is related to the difference between the two SQL queries. Is there a way in an EA SQL query to test NULL as per the first Access Nz (ZZ.Alias) function and return something different? There appears to a isNull(xxxx) test, but is it possible to use it to output something other variable. I.e. ZZ.Name, when ZZ.Alias is null?

[Glassboy]

Apologies in advance, but my next question is related to the difference between the two SQL queries. Is there a way in an EA SQL query to test NULL as per the first Access Nz (ZZ.Alias) function and return something different? There appears to a isNull(xxxx) test, but is it possible to use it to output something other variable. I.e. ZZ.Name, when ZZ.Alias is null?

Paolo needs a trigger warning for any use of the word null :-)

[Paolo F Cantoni]

In response to Paolo's question, the instructions provided to us were to run up MS Access and Open the EAP file, which converts the  EAP to a .aacdb file, so my use of the term 'export' was misleading.

Geert (Sherlock) thx for your efforts, but the suggested change doesn't produce the missing items. What I intend to do next is compare the output columns for the original query and the new query and identify the EAP elements that it isn't finding and deduce why the select statement isn't finding these 13 items.

We use MS Access with Linked Tables so that the source EAP (or another repository) file doesn't need to change.  We DO use .accdb MS Access DBs but they still link to the repositories via Linked Tables.

If the intent was to transfer the repository from a .eap to a .accdb repository, then the Transfer Project functionality should have been used.

Paolo

[yaccoff]

I will giv en the export mechanism a try. Any ideas about the use if null in an EA SQL query?

[Geert Bellekens]

EA SQL == MS Access SQL.

Google has all the answers.

Geert

[yaccoff]

Thx Geert. So with a bit of googling, I now have a SQL query that produces exactly the same result (bar 1 annoying entry), and I think its an EA activity consistency issue.

Here's the latest query: I replaced the Nz with IFF(...)

select distinct po.ea_guid as CLASSGUID, po.Object_Type as CLASSTYPE, tEndObj.Name as [Deliverable], tEndObj.Stereotype as [StereoType], po.name as [Object Name]
, IIF(po.Alias Is Null, po.Name, po.Alias) As [Acticity Name], tp.name as 'Package Name' ,tp2.name as 'Package level -1',tp3.name as 'Package level -2',tp4.name as 'Package level -3', to2.name as [Role]
from ((((((( t_package tp
inner join t_object po on tp.Package_ID = po.Package_ID)
inner join t_object to2 on po.ParentID = to2.object_ID)
inner join t_connector tc on po.Object_ID = tc.Start_Object_ID)
left join t_package tp2 on tp2.Package_ID = tp.Parent_ID)
left join t_package tp3 on tp3.Package_ID = tp2.Parent_ID)
left join t_package tp4 on tp4.Package_ID = tp3.Parent_ID)
left join t_object tEndObj on tEndObj.Object_ID = tc.End_Object_ID)
where
po.Object_Type in ('Activity') and tEndObj.Stereotype in ('NATS_Deliverable') and tp4.name in ('Enabling Processes', 'Operating Processes', 'Management and Support Processes')

Now thanks to your efforts, I want to incorporate this SQL with the scripted aspects of EA and automate the Excel output.