1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

see this view is there any thing i am missing

Discussion in 'SQL PL/SQL' started by sandip.senmajumder, Feb 4, 2011.

  1. sandip.senmajumder

    sandip.senmajumder Active Member

    Messages:
    27
    Likes Received:
    0
    Trophy Points:
    80
    Code (SQL):
    CREATE OR REPLACE FORCE VIEW kiits.v_Prgm_Qualifying_Exam_List
    AS
       SELECT ProgrammeId ,
              SUBSTR(ExList, 2, LENGTH(ExList) - 1) QualifyExamList
         FROM ( SELECT C1.ProgrammeId ,
                       ( SELECT ',' || LTRIM(RTRIM(CAST(QualifyingExamId AS VARCHAR2)))
                         FROM t_Programme_Qualifying_Exams C2
                          WHERE C2.ProgrammeId = C1.ProgrammeId
                           ORDER BY C2.ProgrammeId,
                                    C2.QualifyingExamId DESC ) AS ExList
                FROM t_Programme_Qualifying_Exams C1
                  GROUP BY C1.ProgrammeId ) AS  A
     
  2. Sadik

    Sadik Community Moderator Forum Guru

    Messages:
    1,906
    Likes Received:
    252
    Trophy Points:
    1,455
    And what error are you getting? Why is this code the way it is? why is the earth round...?
     
  3. sandip.senmajumder

    sandip.senmajumder Active Member

    Messages:
    27
    Likes Received:
    0
    Trophy Points:
    80
    my result would be like...
    if my table content this value
    1 4 11001
    2 4 11002
    3 4 11003
    24 46 11001
    25 46 11002
    26 41 11001
    27 41 11002
    28 41 11003
    29 41 11004
    30 41 11005
    31 41 11006
    34 45 11001
    35 45 11002
    39 20 11001
    40 20 11002
    41 19 11001
    42 19 11002
    43 8 11001
    44 8 11002
    63 3 11001
    64 3 11002
    65 3 11003

    my result would be like..

    3 11003,11002,11001
    4 11003,11002,11001
    8 11002,11001
    19 11002,11001
    20 11002,11001
    41 11006,11005,11004,11003,11002,11001
    45 11002,11001
    46 11002,11001
     
  4. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,346
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    But what IS the result you're seeing? Again, what error, if any, is displayed? Why did you ask this question if you see nothing is wrong in the output?
     
  5. sandip.senmajumder

    sandip.senmajumder Active Member

    Messages:
    27
    Likes Received:
    0
    Trophy Points:
    80
    this is the output i am expecting...........well i have use this view to get that output .......have a look.....

    CREATE OR REPLACE VIEW v_Prgm_Qualifying_Exam_List
    as
    select distinct ProgrammeId, rtrim(xmlagg(xmlelement(e,QualifyingExamid,',').extract('//text()')),',') QualifyExamList
    from t_Programme_Qualifying_Exams
    group by ProgrammeId;


    will any change make this query more perfect......??????
     
  6. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    There some other ways of doing the same.

    Code (SQL):

    SQL> SELECT * FROM EMP;

         EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
    ---------- ---------- --------- ---------- --------- ---------- ---------- ----------
          7369 SMITH      CLERK           7902 17-DEC-80        800                    20
          7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
          7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
          7566 JONES      MANAGER         7839 02-APR-81       2975                    20
          7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30
          7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
          7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
          7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20
          7839 KING       PRESIDENT            17-NOV-81       5000                    10
          7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30
          7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20
          7900 JAMES      CLERK           7698 03-DEC-81        950                    30
          7902 FORD       ANALYST         7566 03-DEC-81       3000                    20
          7934 MILLER     CLERK           7782 23-JAN-82       1300                    10

    14 ROWS selected.

    SQL> SELECT EMPNO,ENAME,DEPTNO FROM EMP ORDER BY DEPTNO,ENAME;

         EMPNO ENAME          DEPTNO
    ---------- ---------- ----------
          7782 CLARK              10
          7839 KING               10
          7934 MILLER             10
          7876 ADAMS              20
          7902 FORD               20
          7566 JONES              20
          7788 SCOTT              20
          7369 SMITH              20
          7499 ALLEN              30
          7698 BLAKE              30
          7900 JAMES              30
          7654 MARTIN             30
          7844 TURNER             30
          7521 WARD               30

    14 ROWS selected.

    SQL> SELECT  DEPTNO ,
      2          MAX( SUBSTR(SYS_CONNECT_BY_PATH(ENAME, ','),2)) ELIST
      3  FROM (SELECT ENAME,
      4               DEPTNO,
      5               LAG(ENAME,1) OVER (PARTITION BY DEPTNO ORDER BY ENAME DESC) LGNAME
      6        FROM scott.EMP)
      7  START WITH LGNAME IS NULL
      8  CONNECT BY  PRIOR ENAME = LGNAME
      9  GROUP BY  DEPTNO
     10  ORDER BY DEPTNO ;

        DEPTNO ELIST
    ---------- --------------------------------------------------
            10 MILLER,KING,CLARK
            20 SMITH,SCOTT,JONES,FORD,ADAMS
            30 WARD,TURNER,MARTIN,JAMES,BLAKE,ALLEN

    SQL> SELECT DEPTNO, RTRIM(XMLAGG(X),',')ELIST
    FROM (SELECT  DEPTNO ,ENAME  ,
                  XMLELEMENT("E",ENAME,',').EXTRACT('//text()') X
          FROM EMP
          ORDER BY DEPTNO, ENAME DESC )
    GROUP BY DEPTNO;   2    3    4    5    6

        DEPTNO ELIST
    ---------- --------------------------------------------------
            10 MILLER,CLARK,KING
            20 SMITH,ADAMS,FORD,JONES,SCOTT
            30 WARD,ALLEN,BLAKE,JAMES,MARTIN,TURNER

    SQL>
     
    Also you can create a function and call that function to get the list directly from SQL.

    But I think SYS_CONNECT_BY_PATH will be more accurate method. even less hectic.
     
  7. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    You can use Order by clause of XMLAGG to make the string on order as shown below.

    Code (SQL):

    SQL> SELECT DEPTNO,
                 RTRIM(XMLAGG(XMLELEMENT("E",ENAME||',').EXTRACT('//text()')ORDER BY ENAME DESC ).getstringval(),',')  ELIST
    FROM scott.EMP
    GROUP BY DEPTNO
    ORDER BY DEPTNO  2    3    4    5  ;

        DEPTNO ELIST
    ---------- --------------------------------------------------
            10 MILLER,KING,CLARK
            20 SMITH,SCOTT,JONES,FORD,ADAMS
            30 WARD,TURNER,MARTIN,JAMES,BLAKE,ALLEN

    SQL>
     
     
  8. sandip.senmajumder

    sandip.senmajumder Active Member

    Messages:
    27
    Likes Received:
    0
    Trophy Points:
    80
    Hi Raj,
    You said SYS_CONNECT_BY_PATH will be more accurate method even less hectic.
    may i know why?.......
     
  9. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    Both might be accurate methode. But as far as performance is concerned (especially with huge database) , my vote goes to SYS_CONNECT_BY_PATH.

    Actually there are some other method to achieve the same.
     
  10. sandip.senmajumder

    sandip.senmajumder Active Member

    Messages:
    27
    Likes Received:
    0
    Trophy Points:
    80
    can you be more specific so that i can understand...........the function
     
  11. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    It is simple,

    from performance point of view, I prefer SYS_CONNECT_BY_PATH over XMLAGG function. It is up to you to decide which option you need.

    There are some other options also available like MX(DECODE), MODEL etc for getting the same result.
     
  12. sandip.senmajumder

    sandip.senmajumder Active Member

    Messages:
    27
    Likes Received:
    0
    Trophy Points:
    80
    If i am not wrong XMLAGG function has some limitation.........retrieving data.....


    i want to know about this SYS_CONNECT_BY_PATH function..........if you want to explain......thanks
     
  13. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    There is no limitation for XMLAGG while retrieving data. Only concern is the performance issue.

    I am not going to explain SYS_CONNECT_BY_PATH. Google is the best friend for you.

    "SYS_CONNECT_BY_PATH is valid only in hierarchical queries. It returns the path of a column value from root to node, with column values separated by char for each row returned by CONNECT BY condition."

    Just google and find it. you will find tons of examples.
     
  14. sandip.senmajumder

    sandip.senmajumder Active Member

    Messages:
    27
    Likes Received:
    0
    Trophy Points:
    80
    thanks Raj........i will come back to you again