1. SELECT DISTINCT(TO_CHAR(babs.appid)) AS id,
2.   babs.name                          AS name,
3.   CAST(NULL AS NUMERIC)              AS parentid,
4.   NULL                               AS parentname,
5.   'bizservice'                       AS type
6. FROM bill_aggr_biz_service babs
7. WHERE lower(name) LIKE '%ma%'
8. UNION ALL
9. SELECT DISTINCT(TO_CHAR(cs.cloudserviceid)) AS id,
10.   cs.name                                   AS name,
11.   CAST(NULL AS NUMERIC)                     AS parentid,
12.   NULL                                      AS parentname,
13.   'cloudservice'                            AS type
14. FROM cloud_service cs
15. INNER JOIN bill_aggr_cloud_service bacs
16. ON bacs.cloudserviceid = cs.cloudserviceid
17. WHERE lower(cs.name) LIKE '%ma%'
18. UNION ALL
19. SELECT DISTINCT(TO_CHAR(t.tagid)) AS id,
20.   t.tag                           AS name,
21.   t.tagtypeid                     AS parentid,
22.   tt.name                         AS parentname,
23.   'tag'                           AS type
24. FROM tag t
25. INNER JOIN tag_type tt
26. ON t.tagtypeid = tt.tagtypeid
27. WHERE lower(tag) LIKE 'm%'
28. AND (lower(t.description) NOT LIKE '%imported%'
29. OR t.description IS NULL)
30. AND (lower(tt.description) NOT LIKE '%imported%'
31. OR tt.description IS NULL)
32. UNION ALL
33. SELECT DISTINCT(accountid) AS id,
34.   accountname              AS name,
35.   CAST(NULL AS NUMERIC)    AS parentid,
36.   NULL                     AS parentname,
37.   'account'                AS type
38. FROM bill_aggr_resource
39. WHERE lower(accountname) LIKE '%ma%'
40. UNION ALL
41. SELECT TO_CHAR(NULL)    AS id,
42.   t.region              AS name,
43.   CAST(NULL AS NUMERIC) AS parentid,
44.   NULL                  AS parentname,
45.   'region'              AS type
46. FROM
47.   (SELECT DISTINCT region
48.   FROM bill_aggr_resource
49.   WHERE lower(region) LIKE '%ma%'
50.   ) t;