ORA-01436: CONNECT BY loop in user data

            SELECT CASE

                     WHEN CONNECT_BY_ISLEAF = 1 THEN 0

                     WHEN LEVEL = 1 THEN 1

                     ELSE -1

                  END

                     AS status,

                  LEVEL,

                  menu_desc AS title,

                  DECODE (0, NULL, 'fa-bars', 'fa-file') AS icon,

                  child_node AS VALUE,

                  menu_desc AS tooltip,

                  APEX_UTIL.prepare_url (

                        'f?p='

                     || :app_id

                     || ':'

                     || page_no

                     || ':'

                     || :app_session

                     || ':::::')

                     AS link

             FROM (SELECT mennam AS menu_desc,

                          progcod AS parent_node,

                          modcod AS child_node,

                          progmid AS page_no

                     FROM menumst)

       START WITH child_node = 0

       CONNECT BY PRIOR parent_node = child_node

ORDER SIBLINGS BY parent_node;

I have faced ORA-01436: CONNECT BY loop in user data error

Various use of CASE statement

Q. Which query return different value?

1. SELECT CASE 0 WHEN 0 THEN 'A' ELSE 'B' END

FROM DUAL;

2. SELECT CASE WHEN 0 = 0 THEN 'A' ELSE 'B' END

FROM DUAL;

3. SELECT CASE NULL WHEN NULL THEN 'A' ELSE 'B' END

FROM DUAL;

4. SELECT CASE WHEN NULL IS NULL THEN 'A' ELSE 'B' END

FROM DUAL;

Class 1 Practice

Practice 1

1.            Connect database in SQL*Plus session using the user ID and password.

2.            SQL*Plus commands access the database (Select one).
                                a. True
                                b. False

3.            The following SELECT statement executes successfully:
SELECT last_name, job_id, salary AS Sal FROM    employees;
                                a. True
                                b. False

4.            The following SELECT statement executes successfully:
SELECT *
FROM   job_grades;
                                a. True
                                b. False

5.            There are four coding errors in this statement. Can you identify them?
SELECT  employee_id, last_name sal x 12              ANNUAL SALARY
FROM   employees;

6.            Show the structure of the DEPARTMENTS table. Select all data from the table.

7.            Show the structure of the EMPLOYEES table. Create a query to display the last name, job code, hire date, and employee number for each employee, with employee number appearing first. Save your SQL statement to a file named lab1_7.sql.

8.            Run your query in the file lab1_7.sql.

9.            Create a query to display unique job codes from the EMPLOYEES table.

If you have time, complete the following exercises:

10.          Copy the statement from lab1_7.sql into the SQL*Plus Edit window. Name the column headings Emp #, Employee, Job, and Hire Date, respectively. Run your query again.

11.          Display the last name concatenated with the job ID, separated by a comma and space, and name the column Employee and Title.

 

If you want an extra challenge, complete the following exercise:

12.          Create a query to display all the data from the EMPLOYEES table. Separate each column by a comma. Name the column THE_OUTPUT.