Sparx Systems Forum

Enterprise Architect => Automation Interface, Add-Ins and Tools => Topic started by: dgoetz on March 27, 2020, 10:14:12 pm

Title: SQL query for not equal Stereotype
Post by: dgoetz on March 27, 2020, 10:14:12 pm

I want to query for all attributes with a stereotype not equal to "abcd". I am using EA 13.5.

This query will find all attributes with stereotype "abcd":
  select *
  from t_attribute a
  where a.Stereotype like 'abcd'

The contrary search is obvious:
  select *
  from t_attribute a
  where a.Stereotype not like 'abcd'

But although there are attributes with an empty stereotype in the EA-project, the query returns no result.

Regards
Dieter

Title: Re: SQL query for not equal Stereotype
Post by: Paolo F Cantoni on March 27, 2020, 10:25:57 pm

Quote from: dgoetz on March 27, 2020, 10:14:12 pm

I want to query for all attributes with a stereotype not equal to "abcd". I am using EA 13.5.

This query will find all attributes with stereotype "abcd":
  select *
  from t_attribute a
  where a.Stereotype like 'abcd'

The contrary search is obvious:
  select *
  from t_attribute a
  where a.Stereotype not like 'abcd'

But although there are attributes with an empty stereotype in the EA-project, the query returns no result.

Regards
Dieter

Dieter,
The contrary search is NOT obvious:
  select *
  from t_attribute a
  where a.Stereotype not like 'abcd' or a.Stereotype is Null

As a Data Architect, I HATE Nulls...

Paolo

Title: Re: SQL query for not equal Stereotype
Post by: Uffe on March 27, 2020, 10:41:03 pm

What's that you say? Null not the same as empty?

Sir, I am shocked. Shocked!

 ;D

Title: Re: SQL query for not equal Stereotype
Post by: qwerty on March 27, 2020, 10:44:28 pm

Just try adding 

Code: [Select]

or a.Stereotype = ''
q.

Title: Re: SQL query for not equal Stereotype
Post by: Geert Bellekens on March 27, 2020, 11:03:26 pm

Quote from: qwerty on March 27, 2020, 10:44:28 pm

Just try adding 

Code: [Select]

or a.Stereotype = ''
q.

That's not the same thing and won't help in case of null

For large databases/complex queries it is often better to avoid "or" statements in the where clause because of performance issues.
That can be avoided by using something like

isnull(a.Stereotype,'') <> 'someStereotype' -- works for SQL Server

or

Coalesce(a.Stereotype,'') --might work for other databases

Each database has a bit it's own operator to replace null values with something else.

Geert

Title: Re: SQL query for not equal Stereotype
Post by: dgoetz on March 27, 2020, 11:26:09 pm

Thanks for the help. Null is working.