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!

Help with group by clause

Discussion in 'SQL PL/SQL' started by james shallow, Nov 18, 2017.

  1. james shallow

    james shallow Active Member

    Messages:
    32
    Likes Received:
    1
    Trophy Points:
    110
    Location:
    uk
    Please, can you advise me on this query if it is correct? I have been asked to produce a report of vacation by staff for the year 2017 and group by line manager. The question is looking at this query, I have ordered by line manager which I think is showing staff under each manager but I am not sure If I need to break on the line manager and how to accomplish this. Please note that I inherited these table definition.
    Many thanks for your help.

    Code (SQL):
    SELECT grp.gid,
           grp.lgroup,
           grp.department,
           grp.line_manager,
           stf.line_manager2,
           hol.username,
           stf.known,
           stf.initials,
           hol.YEAR,
           hol.holiday,
           hol.forward,
           lea.fromdate,
           lea.todate,
           lea.reason,
           lea.description,
           lea.add_info,
           lea.ddate,
           lea.ndays,
    FROM vac_holiday hol
    INNER JOIN vac_staff_leave lea ON ( hol.username = lea.username)
    INNER JOIN vac_staff stf       ON (lea.username = stf.username)
    INNER JOIN vac_group grp       ON (stf.gid = grp.gid)
    WHERE YEAR = 2017
    AND to_date(lea.fromdate,'dd/mm/yyyy') >= to_date('01/01/2017', 'dd/mm/yyyy')
    AND to_date(lea.todate,'dd/mm/yyyy') <= to_date('31/12/2017', 'dd/mm/yyyy')
    ORDER BY grp.line_manager

    DESC vac_holiday

    name     NULL TYPE        
    -------- ---- ------------
    username      varchar2(16)
    YEAR                varchar2(4)
    holiday          varchar2(6)
    forward          varchar2(6)

    DESC vac_staff_leave
    name        NULL TYPE        
    ----------- ---- -------------
    username        varchar2(16)
    fromdate         varchar2(10)
    todate              varchar2(10)
    reason              varchar2(20)
    description      varchar2(100)
    add_info         varchar2(300)
    ddate               varchar2(10)
    ndays               varchar2(5)  
    approved         varchar2(1)  
    half_day         varchar2(2)

    DESC vac_group
    name         NULL TYPE        
    ------------ ---- ------------
    gid                        varchar2(16)
    department        varchar2(4)
    lgroup                  varchar2(64)
    line_manager     varchar2(16)

    DESC vac_staff
    name          NULL TYPE        
    ------------- ---- ------------
    username                varchar2(16)
    gid                            varchar2(16)
    line_manager2      varchar2(16)
    known                      varchar2(40)
    initials                     varchar2(6)  
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,542
    Likes Received:
    362
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    To begin with your query doesn't work because you have a comma at the end of the last column in your select statement and Oracle throws an error:

    *
    ERROR at line 19:
    ORA-00936: missing expression

    Remove that comma and you do get results. As far as where to break for the output I would be breaking on department, line_manager and line_manager2:

    break on department on line_manager on line_Manager2 skip 1

    The 'skip 1' puts a blank line between sets of results.

    Try those suggestions then come back if you don't get the output the way you want it.
     
    james shallow likes this.
  3. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    739
    Likes Received:
    145
    Trophy Points:
    830
    Location:
    Russian Federation
    Hi.

    In your scheme, the co-worker has only linear dependence (submission)?
    For example, Manager2 is also an employee and can be subordinate to the manager1, and the manager1 can also have a vacation.
    Do not you need to consider the hierarchy of employees?
     
    james shallow likes this.
  4. james shallow

    james shallow Active Member

    Messages:
    32
    Likes Received:
    1
    Trophy Points:
    110
    Location:
    uk
    Hi krasnoslobodtsev_si,

    Thanks for your advice, at this point, I just want to break by Line manager and wouldn't worry about Line manager 2. Looking at the query, what is the best way to achieve that?
     
  5. james shallow

    james shallow Active Member

    Messages:
    32
    Likes Received:
    1
    Trophy Points:
    110
    Location:
    uk
    Thanks Zargon, I have removed the comma, this was a hindsight during cut and paste. I just want to confirm that using skip will work on SQL developer< I thought this only works in SQL Plus. Thanks
     
  6. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,542
    Likes Received:
    362
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    That was not part of your original question. When you post post ALL of the information you have or want to know.

    Yes, it works in sql developerl.
     
    james shallow likes this.
  7. james shallow

    james shallow Active Member

    Messages:
    32
    Likes Received:
    1
    Trophy Points:
    110
    Location:
    uk
    thanks