SQL for Diagram List extended with TaggedValues

[Geert Bellekens]

I just tried it on a .eap file  in the SQL Scratch pad, and I have lots of results, and not a single one of these tagged values.

Even in a model view it works: https://imgur.com/5EsrZWl

Not sure what you are doing differently.

Geert

[Paolo F Cantoni]

Geert, the problem is if you have elements that only have 3 and not all 4 tags present (not with null value but missing!). They will not be in the result set.

q.

I agree with qwerty.  It's the bl**dy NULL again! 

Paolo

[Daiim]

Geert, the problem is if you have elements that only have 3 and not all 4 tags present (not with null value but missing!). They will not be in the result set.

q.

Exactly. Thank you for clearify that. Has anyone an idea?

[Geert Bellekens]

Geert, the problem is if you have elements that only have 3 and not all 4 tags present (not with null value but missing!). They will not be in the result set.

q.

Exactly. Thank you for clearify that. Has anyone an idea?

Again, the query I posted returns results for All non abstract actors, regardless of the presence of the tagged values.
That is what the LEFT join is for.
I even posted an image of the model view I created with that query. I'm not sure what more proof you want.

You must be doing something differently that you are not telling us.

Geert

[Daiim]

Strange times. I couldn't copy&paste Geerts solution, but starting from scratch step by step finally works and verified it as fully functional - as Geert already insisted. The System is EA Unified Edition, 15.2.1555 on local eap).
I did the same in EA Corporate Edition 14.1.1427 using an eap-file, but it does NOT work correctly. So I wrote to the support.

If someone wants to try, you can find the example here: https://ufile.io/f/vehdk.
@Geert, qwerty and all the other guys - thank you very much for your patience and help!

Daiim

[Geert Bellekens]

I'm glad you got it working.

I had some strange issues after copy/pasting the query as well.
Thought I did something wrong there, but since you had it as well, I'm guessing something weird happened between uploading/downloading the code from and to the forum.

Geert

[Daiim]

The SPARX support answered to my mail and gave the following solution that solved the problem:

See if JET 4 is enabled in 14.1 (Ribbon: Start -> View -> Preferences -> General -> Use JET 4.0 - requires restart).
EA itself is not responsible for executing those queries, it relies on the database to do that.

I wished that EA could detect problems in the SQL query engine and does not silently drop that information by providing a random behavior/result of JOIN evaluation. That would have saved me hours of work. 

Hope this will help others to do so.