Difficulty with writing a Query involving Deeply - challenging one

[SKumar]

Hello All

I have been trying to write this query for a long time but dont see a way of doing this using Deeply: I have simplified this a lot so to be able express my issue.

We have added an attribute called Asset category to Application (meta calss Application). This can hold 'System', 'Application' and 'Platform'.

I have some Applications that have the attribute marked as 'SYSTEM'. I have some Applications that are owned by these systems (meta class Application as above) using the Component link. These applications under the system(meta class Application) have the attribute marked as 'Application'.

I have some applications that are not owned by any system and their custom attribute is marked as 'Application'.

Now I am trying to find out all applications that are not Systems as well as are independent (meaning not Owned by systems).

I tried this but its not producing correct results:

Select [Application] Into @appl1 Where  [Asset Category] Not = "System"  
Select [Application] Into @appl2 Where  [Asset category] = "System"
Select [Application] From @appl1 Where [Aggregation of] Deeply Not in @appl2 as well as

Select [Application] From @appl1 Where [Aggregation of] (Deeply Not in @appl2)

It produces only Applications (and not systems) but is unable to recurse properly.

I am missing the link - I am not able to use the Deeply operation properly.

I would appreciate if anyone can help me with this. Actually, please enlighten me here as I am not able to grasp ERQL properly inspite of years of SQL expertise, where Hierarchial Queries (using keywords like Prior to) could be availed.

[SKumar]

C

That was deep - thanks a lot! I will try 2 digest this 🙂

Thanks again!

-S

[cpucci · Accepted solution]

Try this query:

SELECT [Application] WHERE [Aggregation Of].[Asset Category] DEEPLY NOT = "System"

this query will give you the list of application that are under and application that is not of type System (at any level).

If you want to get also applications at the first level (no aggragation of) then use this other query:

SELECT [Application] WHERE ([Aggregation Of].[Asset Category] DEEPLY NOT = "System") Or ([Aggregation Of] null And ([Asset Category] NOT = "System" Or [Asset Category] nul))