Header & Lines SQL
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: Header & Lines SQL

  1. #1
    Join Date
    Jul 2001
    Posts
    334

    Header & Lines SQL

    Hi,

    I am trying to create data file from SQL, I need file output in the below format (Header & Lines).

    10 ACCOUNTING (Header)
    CLARK (Line1)
    KING (Line2)
    MILLER (Line3)
    20 RESEARCH (Header)
    SMITH (Line1)
    JONES (Line2)
    SCOTT (Line3)
    ADAMS (Line4)
    FORD (Line5)

    Here is the sql query I have built, but not working properly.

    select d.deptno||' '||d.dname||chr(10)||e.ename
    from dept d,
    emp e
    where d.deptno=e.deptno
    and d.deptno in (10,20)
    order by d.deptno

    10 ACCOUNTING (Header need this)
    CLARK
    10 ACCOUNTING (Header don't want)
    KING
    10 ACCOUNTING (Header don't want)
    MILLER
    20 RESEARCH (Header need this)
    SMITH
    20 RESEARCH (Header don't want)
    JONES
    20 RESEARCH (Header don't want)
    SCOTT
    20 RESEARCH (Header don't want)
    ADAMS
    20 RESEARCH (Header don't want)
    FORD

    Thanks in advance.

  2. #2
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Use an analytical function to tell you which occurance of the deptno the row represents ...
    Code:
    select
       d.deptno,
       d.dname,
       e.ename,
       Row_Number() Over Partition By
          (d.deptno) dept_row
    from
       dept d,
       emp e
    where
       d.deptno=e.deptno and
       d.deptno in (10,20)
    order by d.deptno
    ... then use a decode (or case, if you prefer) to include the department in only the first row of that dept's records ...
    Code:
    d.deptno||' '||d.dname||chr(10)||e.ename
    select
       Decode(dept_row,
          1,deptno||' '||dname||chr(10))||
       ename
    from
       (
       select
          d.deptno,
          d.dname,
          e.ename,
          Row_Number() Over Partition By
             (d.deptno) dept_row
       from
          dept d,
          emp e
       where
          d.deptno=e.deptno and
          d.deptno in (10,20)
       order by d.deptno
       )
    Haven't tested it, but you get the idea
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  3. #3
    Join Date
    Jul 2001
    Posts
    334
    Hi Slimdave,

    Thanks for the help. The below portion of the query is really not clear to me. Can you specify little more what is this and how it works.


    Row_Number() Over Partition By
    (d.deptno) dept_row

    Thanks

  4. #4
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    That's an "analytic function" -- there's a section on them in the Oracle SQL Reference.

    The Partition section of the Over clause defines a "window" -- in this case the result set is divided into multiple windows each identified by a unique value of deptno. Within each of these windows the Row_Number() function numbers each individual row.

    So in your first set of example data the function gives you the line number in just the same way that you appended "Line1", "Line2", etc. to the various rows for each department.

    Have a look at the SQL Reference for more examples.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width