OQL question. How to list ApplicationSolutions...

[Håkan Persson]

Hello

My question is: How do I list ApplicationSolutions without any Contacts connected?

In SQL the query would maybye look like "select name from applicationsolution where contacts is null" (or similar). How do I express that in OQL?

best regards Håkan Persson, Örebro, Sweden

[TheBigOne]

Hi,

I have a query to get Tickets without CI`s. Maybe it helps:
SELECT Ticket AS t JOIN lnkFunctionalCIToTicket AS l ON l.ticket_id=t.id

You should check links between contacts and Application Solution. If there is no link, you have your result.

[Håkan Persson]

OK, I managed to list all ApplicationSolutions WITH Contacts connnected.

SELECT ApplicationSolution AS as JOIN lnkContactToFunctionalCI AS lnk ON lnk.functionalci_id = as.id

I'd like to list AS WITHOUT Contacts connected.

In SQL: select * from applicationsolution where id not in (select functionalci_id from lnkContactToFunctionalCI)

Is that possible in OQL? To use 'not in' or simililar?

best regards Håkan, Sweden

[Jaime Valero]

Well, you may create an audit rule to avoid that problem with 'NOT' clausule.

1.- Generate an audit category, with definition set eg:
SELECT ApplicationSolution;

2.- For the new audit category, create an audit rule, eg:
SELECT Ticket AS t JOIN lnkFunctionalCIToTicket AS l ON l.ticket_id=t.id

3.-Mark valid objects= TRUE, that way the audit rule you will informed you about those ApplicationSolution that do NOT meet the rule. That means, the App solutions that do not have any related contact.

Kind regards

[Håkan Persson]

Perfect! Thanks a lot for a working solution.

Regards