pre conditions in sql

[tm200014]

hello,

i am trying to get some data out of my EA model with sql queries.

for operations you can define "pre conditions". I cant seem to find the SQL table in which these pre conditions are listed.

can anyone point me in the right direction?

thanks in advance!

[Geert Bellekens]

Hi,

This EA search query will return operations that contain the search text in the postconditions.

Code: [Select]

select o.ea_guid as CLASSGUID,'Operation' as CLASSTYPE,o.name as Name, class.name as 'Class Name' ,package.name as 'Package Name',package_p1.name as 'Package level -1',package_p2.name as 'Package level -2',package_p3.name as 'Package level -3'
from t_operation as o
join t_operationposts as p on (o.operationID = p.operationID)
join t_object as class on (o.object_id = class.object_id)
join t_package as package on (class.package_id = package.package_id)
left join t_package as package_p1 on (package_p1.package_id = package.parent_id)
left join t_package as package_p2 on (package_p2.package_id = package_p1.parent_id)
left join t_package as package_p3 on (package_p3.package_id = package_p2.parent_id)
where p.Notes like '%<Search Term>%'
Change the '%' by '*' and add brackets around the joins if you are using a local eap file.

Geert

[Geert Bellekens]

t_operationpres is of course the table for the pre-conditions.

Geert

[tm200014]

ah!!!

thank you very much!

when i open the sql query editor in EA there is a list of all available tables. the t_operationpres is not in there. that tells me that this list is not complete.

is there a complete list of all available tables? i find the documentation from EA very poor on the sql part.

[Geert Bellekens]

i find the documentation from EA very poor on the sql part.

You Sir are nominated for "Understatement of the year"

No seriously, you can open any eap file with ms-access, or otherwise create your db of choice using the SQL creation scripts from the website.

Geert

[tm200014]

Alright. Thanks very much! I got that to work...

Although there is another thing which has been giving me a headache the whole day already

I want to list all operations, that do not have a tagged value named 'define' in them. I use the following code:

Code: [Select]

SELECT  DISTINCT t_operation.ea_guid AS CLASSGUID,
             t_operation.Name AS 'Name'
FROM    (t_operation LEFT JOIN t_operationtag ON (t_operation.OperationID = t_operationtag.ElementID))
WHERE   t_operationtag.Property <> 'define' OR t_operationtag.Property IS NULL

This works fine and shows all operations which have either no tagged value or any other tagged value other than 'define'.

My Problem now is, that as soon as the operation has more than one tagged value and one of the tagged values is 'define', then the operation gets displayed anyway because the other tagged values dont match the criteria.

So basically i want to check if there is a tagged value named [ch712]define' and if yes, the whole operation is not supposed to show up in my output at all.

Any ideas how i could do this?

Thanks

[Geert Bellekens]

Try something like this:

Code: [Select]

SELECT  DISTINCT o.ea_guid AS CLASSGUID, o.Name AS 'Name'
FROM    t_operation o
WHERE   not exists (select 1 from t_operationtag t
                            WHERE t.Property = 'define'
                            AND t.ElementID = o.OperationID)
Geert