Tuesday, 14 October 2014

PARTITION BY Query in SQL

/* Formatted on 10/14/2014 11:10:48 AM (QP5 v5.115.810.9015) */
SELECT   DECODE (row_no, 1, FULL_NAME, NULL) FULL_NAME,
         DECODE (row_no, 1, EMPLOYEE_NUMBER, NULL) EMPLOYEE_NUMBER,
         DECODE (row_no, 1, JOB_TITLE, NULL) JOB_TITLE,
         DECODE (row_no, 1, USER_NAME, NULL) USER_NAME,
         DECODE (row_no, 1, APPL_ID, NULL) APPL_ID,
         DECODE (row_no, 1, APPLICATION_NAME, NULL) APPLICATION_NAME,
         PAGE_NAME
  FROM   (SELECT   a.*,
                   ROW_NUMBER ()
                      OVER (
                         PARTITION BY FULL_NAME,
                                      EMPLOYEE_NUMBER,
                                      JOB_TITLE,
                                      USER_NAME,
                                      APPL_ID,
                                      APPLICATION_NAME
                         ORDER BY FULL_NAME,
                                  EMPLOYEE_NUMBER,
                                  JOB_TITLE,
                                  USER_NAME,
                                  APPL_ID,
                                  APPLICATION_NAME
                      )
                      row_no
            FROM   (SELECT   DISTINCT
                             oghr.FULL_NAME,
                             oghr.EMPLOYEE_NUMBER,
                             --oghrass.JOB_TITLE,
                             (SELECT   DISTINCT lup.meaning
                                FROM   OGS_CM_LOOKUPS LUP,
                                       OGS_CM_LOOKUP_TYPES LUT
                               WHERE   LUP.Lookup_type_id =
                                          LUT.lookup_type_id
                                       AND lut.meaning LIKE 'Job Titles%'
                                       AND lup.lookup_code =
                                             oghrass.JOB_TITLE)
                                JOB_TITLE,
                             ogscuse.USER_NAME,
                             ogscappv.APPL_ID,
                             ogscappv.APPLICATION_NAME,
                             ogscappage.page_name
                      FROM   USER_APPS_V ogscappv,
                             USER_APP_PAGES_V ogscappage,
                             USERS_ALL ogscuse,
                             ASSIGNMENTS_ALL oghrass,
                             EMPLOYEES_ALL oghr
                     WHERE   ogscappv.USER_APPL_ID = ogscappage.USER_APPL_ID
                             AND ogscappv.APPL_ID = ogscappage.APPL_ID
                             AND ogscappv.USER_ID = ogscuse.USER_ID
                             AND ogscuse.PERSON_ID = oghrass.EMPLOYEE_ID
                             AND oghr.EMPLOYEE_ID = oghrass.EMPLOYEE_ID
                             AND ogscuse.PERSON_ID = oghr.EMPLOYEE_ID
                             AND ( (:PG_AUA_EMP_NAME IS NULL)
                                  OR (oghr.FULL_NAME LIKE :PG_AUA_EMP_NAME))
                             -- AND ogscappv.APPL_ID = :PG_AUA_APPL_NAME
                             AND ogscappv.APPL_ID LIKE :PG_AUA_APPL_NAME) a)

2 comments:

  1. wonderful information, I had come to know about your blog from my friend nandu , hyderabad,i have read atleast 7 posts of yours by now, and let me tell you, your website gives the best and the most interesting information. This is just the kind of information that i had been looking for, i'm already your rss reader now and i would regularly watch out for the new posts, once again hats off to you! Thanks a ton once again,
    Regards, obiee training in hyderabad

    ReplyDelete
  2. Regards
    Sridevi Koduru (Senior Oracle Apps Trainer Oracleappstechnical.com)
    LinkedIn profile - https://in.linkedin.com/in/sridevi-koduru-9b876a8b
    Please Contact for One to One Online Training on Oracle Apps Technical, Financials, SCM, Oracle Manufacturing, BI Publisher, OAF, ADF, SQL, PL/SQL, D2K at sridevikoduru@oracleappstechnical.com | +91 - 9581017828.

    ReplyDelete