Convert Hierarchical query to ANSI Sql

I need to convert this connect by statement to ANSI Sql/ SQL-92 ANSI standard

CREATE OR REPLACE VIEW "IN21_BIRD"."V_UGROUP_GLOBAL_HIERARCHY" ("UGROUP_NAME_INDENTED", "UIC", "HIER_LEVEL", "IS_LEAF", "PATH", "UGROUP_PK", "REGION_PK", "UGROUP_NAME") AS
SELECT     LPAD (' ', LEVEL - 1) || ugroup_name AS ugroup_name_indented,
            uic, LEVEL AS hier_level, CONNECT_BY_ISLEAF AS is_leaf,
            SYS_CONNECT_BY_PATH (ugroup_name, '\') AS PATH, ugroup_pk,
            region_fk AS region_pk, ugroup_name
       FROM ugroup_global
 START WITH ugroup_fk IS NULL
 CONNECT BY PRIOR ugroup_pk = ugroup_fk

CREATE TABLE ADMIN.UGROUP_GLOBAL
(
  UGROUP_PK SMALLINT NOT NULL,
  UGROUP_NAME CHARACTER VARYING(35) NOT NULL,
  UGROUP_FK SMALLINT,
  UIC CHARACTER VARYING(6),
  REGION_FK BYTEINT NOT NULL,
  IS_HOME CHARACTER(1) NOT NULL,
  VALID_START DATE,
  VALID_END DATE
)
DISTRIBUTE ON RANDOM;
rockremAsked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
that would be this kind of syntax:
http://www.postgresql.org/docs/8.4/static/queries-with.html
the CTE expressions with recursive working
0
 
Geert GOracle dbaCommented:
having sample data always help^s

what database are you targetting ?
i guess it's from oracle to something else ?
0
 
rockremAuthor Commented:
Postgres
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.