Results 1 to 7 of 7
  1. #1
    Join Date
    Jul 2004
    Posts
    2

    Question Unanswered: Help with query(rownum)

    This is what I am trying to do, I have an employee and dependents for benefits. A spouse would = 99 and the other dependents for an employee would be 1, 2, 3.......
    Example of what I need:
    Employee1 Employee Suzy Smith
    Employee1 Spouse John Smith 99
    Employee1 Daughter Tia Smith 1
    Employee1 Son Tom Smith 2

    Employee2 Employee Tina Smith
    Employee2 Spouse Mike Smith 99
    Employee2 Daughter Lisa Smith 1
    Employee2 Son Bill Smith 2

    Example of what I get
    Employee1 Employee Suzy Smith
    Employee1 Spouse John Smith 99
    Employee1 Daughter Tia Smith 1
    Employee1 Son Tom Smith 2

    Employee2 Employee Tina Smith
    Employee2 Spouse Mike Smith 99
    Employee2 Daughter Lisa Smith 3
    Employee2 Son Bill Smith 4

    Code I have is select emplid, name, relationship, DECODE(relationship, 'D', rownum, 'S', rownum, 'C', rownum, 'SP', 99 )from
    (select emplid, name, relationship from sysadm.ps_dependent_benef
    order by emplid, relationship
    )

    The above works if I only select one employee but when I have more than one it does not restart the rownum count, is there a way to do this and if so what is an example of the code

  2. #2
    Join Date
    Jul 2003
    Posts
    2,296
    the code doesn't even represent your output.
    how about running your code, and posting the real output.
    it's hard to help you when we don't even see the real output from your code.

    i wouldn't use rownum. create your own count
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  3. #3
    Join Date
    Jul 2003
    Posts
    2,296
    also, what if the employee has 5 children?
    are daughters supposed to display first or the order for the children
    does not matter?
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  4. #4
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    you can try something like:

    Code:
    select emplid,name,DECODE(relationship,'D',rownum-(rownum-1),'S',rownum-(rownum-2),'C',rownum-(rownum-3),'SP',99) relationship
    from sysadm.ps_dependent_benef
    /

  5. #5
    Join Date
    Jul 2004
    Posts
    2

    Real Output

    I need to get the following:

    111111 Smith,Ralph SP 99
    111113 Jones,Kimberly D 1
    111113 Jones,Leah D 2
    111113 Jones,Jared S 3
    111113 Jones,Jesse S 4
    111113 Jones,Jonathan S 5
    111113 Jones,Seth S 6
    111113 Jones,Barbara SP 99
    111114 Weis,Helen SP 99
    111116 Bally,Kevin S 1
    111116 Bally,James SP 99

    The output is below:
    111111 Smith,Ralph SP 99
    111113 Jones,Kimberly D 2
    111113 Jones,Leah D 3
    111113 Jones,Jared S 4
    111113 Jones,Jesse S 5
    111113 Jones,Jonathan S 6
    111113 Jones,Seth S 7
    111113 Jones,Barbara SP 99
    111114 Weis,Helen SP 99
    111116 Bally,Kevin S 10
    111116 Bally,James SP 99

    My current query is select emplid, name, relationship, DECODE(relationship, 'D', rownum, 'S', rownum, 'C', rownum, 'SP', 99 )
    from (select emplid, name, relationship from sysadm.ps_dependent_benef
    group by emplid, name, relationship, DECODE(relationship, 'D', rownum, 'S', rownum, 'C', rownum, 'SP', 99 ) order by emplid, relationship)

  6. #6
    Join Date
    Jul 2003
    Posts
    2,296
    quick question as I am trying to help here:
    why are you grouping in the inner query?
    do you really mean to order by or are there duplicates?
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  7. #7
    Join Date
    Jul 2003
    Posts
    2,296
    This should do it.
    I didn't have any hints as to how an employee is identified so the
    below code only lists spouse (99) and then orders the children.

    PHP Code:
    select     
        emplid

        
    name
        
    relationship
        
    decode(the_order0,99the_orderthe_order
    from 
    (    
            
    select   
                emplid

                
    name
                
    relationship
                
    count(*) over (partition by emplid order by relationship desc)-1 the_order
            from 
                sysadm
    .ps_dependent_benef
            where 
                relationship IN 
    ('D''S''SP')
         ) 
    - The_Duck
    you can lead someone to something but they will never learn anything ...

Posting Permissions

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