Solved

Convert Hierarchical query to ANSI Sql

Posted on 2013-01-22
6
406 Views
Last Modified: 2014-05-12
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;
0
Comment
Question by:rockrem
6 Comments
 
LVL 36

Expert Comment

by:Geert Gruwez
Comment Utility
having sample data always help^s

what database are you targetting ?
i guess it's from oracle to something else ?
0
 

Author Comment

by:rockrem
Comment Utility
Postgres
0
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
Comment Utility
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

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Creating and Managing Databases with phpMyAdmin in cPanel.
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
Steps to create a PostgreSQL RDS instance in the Amazon cloud. We will cover some of the default settings and show how to connect to the instance once it is up and running.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now