SELECT DISTINCT(TO_CHAR(babs.appid)) AS id,
  babs.name                          AS name,
  CAST(NULL AS NUMERIC)              AS parentid,
  NULL                               AS parentname,
  'bizservice'                       AS type
FROM bill_aggr_biz_service babs
WHERE lower(name) LIKE '%ma%'
UNION ALL
SELECT DISTINCT(TO_CHAR(cs.cloudserviceid)) AS id,
  cs.name                                   AS name,
  CAST(NULL AS NUMERIC)                     AS parentid,
  NULL                                      AS parentname,
  'cloudservice'                            AS type
FROM cloud_service cs
INNER JOIN bill_aggr_cloud_service bacs
ON bacs.cloudserviceid = cs.cloudserviceid
WHERE lower(cs.name) LIKE '%ma%'
UNION ALL
SELECT DISTINCT(TO_CHAR(t.tagid)) AS id,
  t.tag                           AS name,
  t.tagtypeid                     AS parentid,
  tt.name                         AS parentname,
  'tag'                           AS type
FROM tag t
INNER JOIN tag_type tt
ON t.tagtypeid = tt.tagtypeid
WHERE lower(tag) LIKE 'm%'
AND (lower(t.description) NOT LIKE '%imported%'
OR t.description IS NULL)
AND (lower(tt.description) NOT LIKE '%imported%'
OR tt.description IS NULL)
UNION ALL
SELECT DISTINCT(accountid) AS id,
  accountname              AS name,
  CAST(NULL AS NUMERIC)    AS parentid,
  NULL                     AS parentname,
  'account'                AS type
FROM bill_aggr_resource
WHERE lower(accountname) LIKE '%ma%'
UNION ALL
SELECT TO_CHAR(NULL)    AS id,
  t.region              AS name,
  CAST(NULL AS NUMERIC) AS parentid,
  NULL                  AS parentname,
  'region'              AS type
FROM
  (SELECT DISTINCT region
  FROM bill_aggr_resource
  WHERE lower(region) LIKE '%ma%'
  ) t;