SQL to count diagram objects on all diagrams in all sub-packages...

[Mr Stuff]

I don't do SQL so this is hard

I would like to count all the diagram objects on all diagrams in all sub-packages of a specified root package.

I think it should be simple  - I tried building up from "... FROM t_package START WITH ea_guid = '{AF3FBF07-CEC4-49f4-8E84-D2CCB146B4BA}' " but then ran out of steam as CONNECT BY Parent_ID = Package_ID just gives me my root package.

Ignorance is not bliss... all assistance gratefully received!

Thx, Julian

[qwerty]

You will have a hard time with just SQL. However, the EA SQL processor has a useful macro. Try

Code: [Select]

SELECT * from t_package where package_id in (#Branch#)
in a new query (not the SQL Scratch Pad). Also note that "Branch" is case sensitive (sic!).

q.

[Mr Stuff]

Referring to the user guide http://www.sparxsystems.com/enterprise_architect_user_guide/13.0/model_navigation/creating_filters.html

I selected my chosen root package in the project browser and entered

Code: [Select]

SELECT * from t_package where package_id in (#Branch#) in the <search term> box and ran the query by clicking the "play" type button (with no tooltip)

Nothing happened. Repeated in front of witnesses, tried adding ";" at the end, and capitalising as

Code: [Select]

SELECT * FROM t_package WHERE Package_ID IN  (#Branch#)
Still nothing

And even if I get that part working from a follow-up answer, what do I do next? 

Sorry for being such a plodder...

Thanks

Julian

[Mr Stuff]

@qwerty said "not in the scratchpad" - in fact that's where it does work

Next steps?

[qwerty]

And also not in the "Search field". You have to create a custom SQL search (it's also in the help page you referenced).

q.

[Mr Stuff]

Hmmm.... This actually seems to work, on the basis that it went up by 1 when I added an object to one diagram

Code: [Select]

SELECT COUNT(*)
FROM t_diagramobjects
INNER JOIN t_diagram ON t_diagramobjects.Diagram_ID = t_diagram.Diagram_ID
WHERE t_diagram.Package_ID IN (SELECT Package_ID from t_package where Package_ID in (#Branch#))
So, final question is: how do I reference (#Branch#) from e.g. the GUID of a specific package so I don't  have to select it in the browser?

Julian

[qwerty]

@qwerty said "not in the scratchpad" - in fact that's where it does work

Next steps?

Gosh. They made it work there too

q.

[qwerty]

Hmmm.... This actually seems to work, on the basis that it went up by 1 when I added an object to one diagram

Code: [Select]

SELECT COUNT(*)
FROM t_diagramobjects
INNER JOIN t_diagram ON t_diagramobjects.Diagram_ID = t_diagram.Diagram_ID
WHERE t_diagram.Package_ID IN (SELECT Package_ID from t_package where Package_ID in (#Branch#))
So, final question is: how do I reference (#Branch#) from e.g. the GUID of a specific package so I don't  have to select it in the browser?

Julian

That's not possible. The #Branch# only works for a selected package.

q.

[Mr Stuff]

OK, I've wrapped the query in a script to use Repository.SQLQuery

But I think EA is objecting to (#Branch#) [actually failing on the #] in the sql text...

Can (#Branch#) be used in a direct query to Repository.SQLQuery or only from the front end?

Thx, Julian

[qwerty]

I guess not. I have not tried. It didn't work in the past like it didn't in the scratch pad. So probably worth a feature request. Maybe it's already implemented 

q.

[Mr Stuff]

Now when using SQL searches you can use the macro #Branch# to get all package ID's of the currently selected package and all nested packages. Because that macro can't be used in scripting I'm making the package ID string myself.

I found the answer in Geert's post above; I'll try to follow his approach...

Julian

[qwerty]

I just tested it with the above query and it threw an error "#Branch# ... blabla ... syntax error". As expected...

q.

[Mr Stuff]

I've developed Geert's code to provide a string of Package_ID's suitable for SQL and I have the query string...

Code: [Select]

SELECT COUNT(Object_ID) FROM t_diagramobjects INNER JOIN t_diagram ON t_diagramobjects.Diagram_ID = t_diagram.Diagram_ID WHERE t_diagram.Package_ID IN ('3982','4482','7922','4483','6882','6722','7062','7103')
However, whilst this works from the SQL scratchpad, returning the same answer as obtained with (#Branch#) in the IN clause, if I actually use that string with repository.SQLQuery I get (via Session.output

Code: [Select]

<?xml version="1.0"?>
<EADATA version="1.0" exporter="Enterprise Architect">
<Dataset_0><Data><Row/></Data></Dataset_0></EADATA>

and EA complains...

Error:
Code = 0x0
Source = Line : 0; Char : 0
Error description = (null)

NB Removing the tick marks around the numbers in the IN clause gives the same results

Anyone have any idea why this is not working?

J

[qwerty]

The SELECT needs a GROUP statement and Package_ID is INT, not string:

Code: [Select]

SELECT COUNT(*) , OBject_ID FROM t_diagramobjects INNER JOIN t_diagram ON t_diagramobjects.Diagram_ID = t_diagram.Diagram_ID
WHERE t_diagram.Package_ID IN (3982,4482,54)
GROUP BY Object_ID

q.

[Mr Stuff]

Thanks but I don't understand - a bit too terse for me.

a) You said Package_ID is Int, implying drop the tick marks, but I as I said I tried both with and without tick marks and it made no difference, so how/why does the string/int issue affect the repository.SQLQuery but not the scratchpad?

b) You said I need a GROUP BY...
    i) I only want a single number, the count, not the Count and Object_ID that you added, in which case GROUP BY seems redundant How should adding Object_ID to the select help?
   ii) The query was perfectly OK exactly as quoted in the scatchpad so again, GROUP BY is redundant - at least in the scratchpad. If it is needed for repository.SQLQuery can you say why?

Hence the question: why is ok in the scratchPad but not via repository.SQLQuery?

What am I missing here?

J