Author Topic: need help with an SQL query  (Read 502 times)

ngong

  • EA User
  • **
  • Posts: 269
  • Karma: +1/-2
    • View Profile
need help with an SQL query
« on: April 28, 2023, 01:14:52 am »
I am looking for a query that finds all elements having a certain string in their stereotype name - EA 16.1, SQLite database.

Quote
select ts.Stereotype stereotype, to2.Name, to2.Object_Type "type" from t_stereotypes ts
join t_xref tx on tx.Description like '%' || ts.ea_guid || '%'
join t_object to2 on tx.Client = to2.ea_guid
where ts.Stereotype like '%<Search Term>%';

works, if I substitute <Search Term> with a stereotype name or a part of it.
When leaving the <Search Term> in, the SQL query does find anything.

Where is my problem?
Rolf

qwerty

  • EA Guru
  • *****
  • Posts: 13428
  • Karma: +390/-299
  • I'm no guru at all
    • View Profile
Re: need help with an SQL query
« Reply #1 on: April 28, 2023, 04:49:22 am »
How/where are you running the query?

q.

Geert Bellekens

  • EA Guru
  • *****
  • Posts: 12783
  • Karma: +532/-33
  • Make EA work for YOU!
    • View Profile
    • Enterprise Architect Consultant and Value Added Reseller
Re: need help with an SQL query
« Reply #2 on: April 28, 2023, 01:56:07 pm »
This is what I use:

Code: [Select]
select c.ea_guid as CLASSGUID,c.object_type as CLASSTYPE,c.name as Name,c.ModifiedDate, c.stereotype as Stereotype
,xref.Description as AllStereotypes
,package.name as PackageName ,package_p1.name as PackageLevel1,package_p2.name as PackageLevel2
,package_p3.name as PackageLevel3
from (((((t_object c
inner join t_package as package on c.package_id = package.package_id)
left join t_xref as xref on xref.Client = c.ea_guid
    and xref.Name = 'StereoTypes' )
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
c.Stereotype like '#WC#<Search Term>#WC#'
or isnull(xref.Description, '') like '#WC#<Search Term>#WC#'

Geert