set search_path <<LOCAL>>;
SELECT distinct 
 CASE
  WHEN  package_name ='/com.castsoftware.mqe' and  COUNT(*) > 0  THEN 'Messaging driven Stack'
  WHEN package_name ='/com.castsoftware.android' or package_name ='/com.castsoftware.ios'   and  COUNT(*) > 0  THEN 'Mobile Stack'
  WHEN package_name ='/com.castsoftware.jaxrs' or package_name ='/com.castsoftware.dotnetweb' or package_name ='/com.castsoftware.jaxws' and  COUNT(*) > 0 THEN 'SOA(REST / SOAP /Webservice) Stack'
  ELSE 'N-Tier Stack'
 END AS TechnologyStack
FROM sys_package_version group by package_name

union 
SELECT distinct 
 CASE
  WHEN   lower(object_type_str) like 'wsdl%' or lower(object_type_str) like 'jax%'  and  COUNT(*) > 0   THEN 'SOA(REST / SOAP /Webservice) Stack'
  WHEN  object_type_str  in ('ASP.NET Get Operation','ASP.NET Post Operation','ASP.NET Put Operation','ASP.NET Delete Operation','ASP.NET Any Operation' ) 
   and  COUNT(*) > 0 THEN 'SOA(REST / SOAP /Webservice) Stack'
  WHEN  lower(object_type_str) like 'ios%' or lower(object_type_str) like '%android%' and  COUNT(*) > 0   THEN 'Mobile Stack'
  ELSE 'N-Tier Stack'
 END AS TechnologyStack
FROM cdt_objects group by object_type_str