SQL Dialect Detector v1.0.1

Enter your SQL query here:

SELECT
  *,
  count(*) OVER () AS fullCount
FROM
  (
    WITH
      sic_tbl AS (
        SELECT
          epi.party,
          epi.ext_party_id
        FROM
          ext_party_identity epi
          INNER JOIN external_identity exi ON epi.ID = exi.ID
        WHERE
          exi.ext_source IN (
            SELECT
              pr.ID
            FROM
              party_role pr
              INNER JOIN party P ON P.ID = pr.org
              INNER JOIN party_role_type prt ON pr.party_role_type = prt.ID
            WHERE
              P.NAME = 'Single Invoice CO'
              AND prt.role_type = 'ROLE_SCN'
          )
      ),
      role_tbl AS (
        SELECT
          party_role.org,
          array_to_string(
            array_agg(
              CASE
                WHEN party_role_type.role_type = 'ROLE_FUNDER'
                AND party_role.party_role_sub_type = 'ASSET_MANAGER' THEN 'Asset Manager' || ' ' || party_role.id::text
                ELSE role_name || ' ' || party_role.id::text
              END
              ORDER BY
                role_name
            ),
            ', '
          ) as roles
        FROM
          party_role
          INNER JOIN party_role_type ON party_role.party_role_type = party_role_type.id
        WHERE
          party_role_type.role_type != 'ROLE_MEMBER'
        GROUP BY
          party_role.org
      ),
      ext_role_tbl AS (
        SELECT
          pr.org,
          array_to_string(array_agg(epri.ext_party_role_id), ', ') as ext_party_roles
        FROM
          ext_party_role_identity epri
          INNER JOIN party_role pr ON pr.id = epri.party_role
        GROUP BY
          pr.org
      ),
      parent_tbl AS (
        SELECT
          pr.org,
          jsonb_object_agg(parent_pr.org, org_name (parent_pr.id)) as parents,
          array_to_string(
            array_agg(
              org_name (parent_pr.id)
              ORDER BY
                parent_pr.org
            ),
            ','
          ) AS parent_names
        FROM
          party_relationship rel
          INNER JOIN party_role pr ON pr.id = rel.to_organization_role
          INNER JOIn party_role parent_pr ON parent_pr.id = rel.from_organization_role
        WHERE
          rel.relationship_type = 'SUB'
        GROUP BY
          pr.org
      ),
      parent_ext_role_tbl AS (
        SELECT
          pr.org,
          array_to_string(array_agg(epri.ext_party_role_id), ', ') as parent_ext_party_roles
        FROM
          ext_party_role_identity epri
          INNER JOIN party_relationship prel ON epri.party_role = prel.from_organization_role
          INNER JOIN party_role pr ON pr.id = prel.to_organization_role
        WHERE
          prel.relationship_type = 'SUB'
        GROUP BY
          pr.org
      )
    SELECT DISTINCT
      ON (party.id) party.id,
      COALESCE(sic_tbl.ext_party_id, 'N/A') AS sicId,
      party.NAME AS orgName,
      org_main_country (party_role.ID) AS country,
      role_tbl.roles,
      ext_role_tbl.ext_party_roles AS extPartyRoles,
      COALESCE(i1.sic_code || ' : ' || i1.name, i2.sic_code || ' : ' || i2.name, 'N/A') AS naic,
      COALESCE(g_sub_industry.code || ' : ' || g_sub_industry.name, g_industry.code || ' : ' || g_industry.name, g_industry_group.code || ' : ' || g_industry_group.name, g_sector.code || ' : ' || g_sector.name) AS gics,
      COALESCE(NULLIF(get_sp_long_term_rating_value (ogi.sp_long_term), ''), NULLIF(get_sp_short_term_rating_value (ogi.sp_short_term), ''), 'N/A') as spRating,
      COALESCE(NULLIF(get_moody_long_term_rating_value (ogi.moody_long_term), ''), NULLIF(get_moody_short_term_rating_value (ogi.moody_short_term), ''), 'N/A') as moodyRating,
      parent_tbl.parents,
      parent_tbl.parent_names AS parentNames,
      parent_ext_role_tbl.parent_ext_party_roles AS parentExtPartyRoles,
      CASE
        WHEN EXISTS (
          SELECT
            1
          FROM
            party_role orgRole
            INNER JOIN user_credential uc ON uc.login_as_org_role = orgRole.id
          WHERE
            orgRole.org = party.id
        ) THEN true
        ELSE false
      END AS registeredMember
    FROM
      party
      INNER JOIN party_role ON party_role.org = party.ID
      LEFT JOIN org_general_info ogi ON ogi.org = party.id
      LEFT JOIN sic_tbl ON sic_tbl.party = party.id
      LEFT JOIN role_tbl ON role_tbl.org = party.id
      LEFT JOIN ext_role_tbl ON ext_role_tbl.org = party.id
      LEFT JOIN parent_tbl ON parent_tbl.org = party.id
      LEFT JOIN parent_ext_role_tbl ON parent_ext_role_tbl.org = party.id
      LEFT JOIN industry i1 ON ogi.naic_industry = i1.sic_code
      LEFT JOIN industry i2 ON ogi.naic_major_group = i2.sic_code
      LEFT JOIN gics_code g_sub_industry ON ogi.gics_sub_industry = g_sub_industry.code
      LEFT JOIN gics_code g_industry ON ogi.gics_industry = g_industry.code
      LEFT JOIN gics_code g_industry_group ON ogi.gics_industry_group = g_industry_group.code
      LEFT JOIN gics_code g_sector ON ogi.gics_sector = g.sector
  ) as o

Detected SQL Dialects: