This discussion is archived
1 2 3 Previous Next 30 Replies Latest reply: Dec 5, 2012 7:12 AM by 883189 Go to original post RSS
  • 15. Re: query help....three conditions
    user346369 Expert
    Currently Being Moderated
    select empno,
    case when (abest is not null and bgood is not null) then abest
            when (abest is not null and bgood is null) then abest
            when (abest is null and bgood is not null) then bgood
            else null
            end as "level"
            from (
    select i.empno, 
           i.empstatus as abest,
           ii.empstatus as bgood
    from emp i,
         emp ii
    where i.emptype = 'best'
      and ii.emptype='good'
      and i.empno=ii.empno);
    Does that really work???
    Here's what I get:
    create table emp
        (empno varchar2(5), emptype varchar2(7), empstatus varchar2(9) );
    Insert into emp values('1', 'best', 'Active');
    Insert into emp values('2', 'good', 'drop');
    Insert into emp values('3', 'best', 'Active');
    Insert into emp values('1', 'good', 'cancel');
    Insert into emp values('4', 'good', 'left');
    Insert into emp values('5',  null,   null);
    set feedback on
    select * from emp order by empno,emptype;
    
    EMPNO EMPTYPE EMPSTATUS
    ----- ------- ---------
    1     best    Active
    1     good    cancel
    2     good    drop
    3     best    Active
    4     good    left
    5
    
    select empno,
    case when (abest is not null and bgood is not null) then abest
            when (abest is not null and bgood is null) then abest
            when (abest is null and bgood is not null) then bgood
            else null
            end as "level"
            from (
    select i.empno, 
           i.empstatus as abest,
           ii.empstatus as bgood
    from emp i,
         emp ii
    where i.emptype = 'best'
      and ii.emptype='good'
      and i.empno=ii.empno);
    
    EMPNO level
    ----- ---------
    1     Active
    
    1 row selected.
    Meanwhile, my original solution gives you 5 rows:
    select empno, empstatus as"High Stat"
      from emp
      where (       empno,     nvl(emptype,'~')  )
         in (Select empno, min(nvl(emptype,'~'))
             from emp
             group by empno )
      order by empno;
    
    EMPNO High Stat
    ----- ---------
    1     Active
    2     drop
    3     Active
    4     left
    5
    
    5 rows selected.
    Edited by: Steve Cosner on Nov 29, 2012 1:34 PM
  • 16. Re: query help....three conditions
    user346369 Expert
    Currently Being Moderated
    Your embedded select:
    select i.empno, 
           i.empstatus as abest,
           ii.empstatus as bgood
    from emp i,
         emp ii
    where i.emptype = 'best'
      and ii.emptype='good'
      and i.empno=ii.empno;
    Returns only one row:
    EMPNO ABEST     BGOOD
    ----- --------- ---------
    1     Active    cancel
    
    1 row selected.
  • 17. Re: query help....three conditions
    883189 Newbie
    Currently Being Moderated
    Steve,
    What you said is right.
    I am getting only one row... oh god...

    But the solution u provided is not understandable..though it is correct
    can u please explain it clearly
    I dont want to use ~ in the query mainly
    This min and max are so confusing
    It has nothing to do with the alphabetical order...

    Actually this is just sample data... This is not original data i provided...
    I dont want to provide the original data here... so i just created emp table with sample data
    The data and the values are different in my case...

    I wanted to use something like case/if else or easy method
    Can you please help me with it... I dont have time now...
    I need to get it done by today....
  • 18. Re: query help....three conditions
    user346369 Expert
    Currently Being Moderated
    I dont want to use ~ in the query mainly
    This min and max are so confusing
    It has nothing to do with the alphabetical order...
    It has everything to do with alphabetic order. "~" is just the highest character in the sort order of ascii characters -- it comes AFTER A..Z and a...z.
    This min and max are so confusing
    Just like NVL, you should learn what they do. I used MAX in the solution below. You MUST use Min or Max to find the correct row when you have multiple rows for one empno.
    I wanted to use something like case/if else or easy method
    Using your "easy method" with CASE is below. It took a lot longer to develop.
    I dont have time now... I need to get it done by today....
    Good luck!!!! :-)
    create table emp
        (empno varchar2(5), emptype varchar2(7), empstatus varchar2(9) );
    Insert into emp values('1', 'best', 'Active');
    Insert into emp values('2', 'good', 'drop');
    Insert into emp values('3', 'best', 'Active');
    Insert into emp values('1', 'good', 'cancel');
    Insert into emp values('4', 'good', 'left');
    Insert into emp values('5',  null,   null);
    set feedback on
    select * from emp order by empno,emptype;
    
    EMPNO EMPTYPE EMPSTATUS
    ----- ------- ---------
    1     best    Active
    1     good    cancel
    2     good    drop
    3     best    Active
    4     good    left
    
    Column t_high format a6
    
    Select empno,
           Case when emptype='best'  then '3'
                when emptype='good'  then '2'
                when emptype is null then '0'
                else                      '1'
           End as t_high,
           emptype
           from emp
    order by empno;
    
    EMPNO T_HIGH EMPTYPE
    ----- ------ -------
    1     3      best
    1     2      good
    2     2      good
    3     3      best
    4     2      good
    5     0
    
    6 rows selected.
    
    
    column t_max format a6
    Select empno,
           Max(Case when emptype='best'  then '3'
                    when emptype='good'  then '2'
                    when emptype is null then '0'
                    else                      '1'
               End)   as t_max
      from emp
      group by empno
    order by empno;
    
    EMPNO T_MAX
    ----- ------
    1     3
    2     2
    3     3
    4     2
    5     0
    
    5 rows selected.
    
    
    select empno, empstatus
     from emp
     where ( empno,
             Case when emptype='best'  then '3'
                  when emptype='good'  then '2'
                  when emptype is null then '0'
                  else                      '1'
             End )
        IN (Select empno,
             Max(Case when emptype='best'  then '3'
                      when emptype='good'  then '2'
                      when emptype is null then '0'
                      else                      '1'
                 End )
             from emp
             group by empno )
     order by empno;
    
    EMPNO EMPSTATUS
    ----- ---------
    1     Active
    2     drop
    3     Active
    4     left
    5
    
    5 rows selected.
  • 19. Re: query help....three conditions
    883189 Newbie
    Currently Being Moderated
    Steve,

    Thank u so much... u saved me....
    But can u explain with one example what does the IN select query does ?
  • 20. Re: query help....three conditions
    user346369 Expert
    Currently Being Moderated
    I ran the select and showed you the output of the "IN select" just before the last select above.

    All it does is finds the Empno and matching "Case..." output for the row you want, which is the one with "best", or if no row with "best" then the row with "good", or if neither of those, then the row with null emptype.

    The last query's "where ( empno, Case...End )" is a pairwise condition, so the "IN (Select..." needs to return a matching pair: empno, and the result of Case.
  • 21. Re: query help....three conditions
    883189 Newbie
    Currently Being Moderated
    Hi Steve,

    Thanks for ur patience in helping me to get out of this problem.

    I tried to insert a row into this table and testing it. Seems like one more condition is missing.

    insert into emp values('8','good','left')

    insert into emp values('8','best',null)

    When I ran the query u gave, it doesn't display the row with empno=8
    But as per the requirement, empno=8 should be displayed as good emptype has a empstatus of left.

    can you please help me to solve this... I somehow took extension and now when i am running the query, it isn't working...
  • 22. Re: query help....three conditions
    Chanchal Wankhade Journeyer
    Currently Being Moderated
    Hi,

    If you want left as a good you can Add one more condition in case in the solution provided by Steve..
    column t_max format a6
    Select empno,
           Max(Case when emptype='best'  then '3'
                    when emptype='good'  then '2'
                    when emptype='left'  then '2'
                    when emptype is null then '0'
                    else                      '1'
               End)   as t_max
      from emp
      group by empno
    order by empno;
  • 23. Re: query help....three conditions
    jeneesh Guru
    Currently Being Moderated
    Like
    select empno,
           max(empstatus)
            keep(dense_rank first order by 
                  decode(
                        emptype,'best',1,
                                 'good',2,
                                  null,4,
                                 3
                         )
                 ) mpstatus
    from emp
    group by empno;
    
    EMPNO MPSTATUS
    ----- ---------
    1     Active    
    2     drop      
    3     Active    
    4     left      
    5               
    8               
    
     6 rows selected 
  • 24. Re: query help....three conditions
    883189 Newbie
    Currently Being Moderated
    Hi jeneesh,

    but value is not being populated. The MPSTATUS should dhow up left for empno=8... how then?
  • 25. Re: query help....three conditions
    jeneesh Guru
    Currently Being Moderated
    880186 wrote:
    Hi jeneesh,

    but value is not being populated. The MPSTATUS should dhow up left for empno=8... how then?
    That means you want to order based on the EMPSTATUS first then based on EMPTYPE..
    select empno,
           max(empstatus)
            keep(dense_rank first order by 
                  decode(empstatus,null,1,0),
                  decode(
                        emptype,'best',1,
                                 'good',2,
                                  null,4,
                                 3
                         )
                 ) mpstatus
    from emp
    group by empno;
    
    EMPNO MPSTATUS
    ----- ---------
    1     Active    
    2     drop      
    3     Active    
    4     left      
    5               
    8     left      
    
     6 rows selected 
  • 26. Re: query help....three conditions
    883189 Newbie
    Currently Being Moderated
    Hi Jeneesh,

    Thank you so much for ur quick response when needed...
    But just have a final question...what if we need to decode the output...
    For Example, i wanted to display WON in place of empstatus=Active for best emptype ?

    Any idea on this?
  • 27. Re: query help....three conditions
    jeneesh Guru
    Currently Being Moderated
    Just DECODE it..
    select empno,
           max(decode(emptype,'best',decode(empstatus,'Active','WON',empstatus),empstatus))
            keep(dense_rank first order by 
                  decode(empstatus,null,1,0),
                  decode(
                        emptype,'best',1,
                                 'good',2,
                                  null,4,
                                 3
                         )
                 ) mpstatus
    from emp
    group by empno;
    
    EMPNO MPSTATUS
    ----- ---------
    1     WON       
    2     drop      
    3     WON       
    4     left      
    5               
    8     left      
    
     6 rows selected 
  • 28. Re: query help....three conditions
    883189 Newbie
    Currently Being Moderated
    Hi Jeneesh

    Thank u so much... It helped me a lot...
    Can you explain the query please...
    mainly -
    decode(
    emptype,'best',1,
    'good',2,
    null,4,
    3

    What does 3 indicate here ?
    Also, Keep statement I have never used before...
  • 29. Re: query help....three conditions
    user346369 Expert
    Currently Being Moderated
    decode(
           emptype,'best',1,
                   'good',2,
                    null,4,
                    3 )
    What does 3 indicate here ?
    Decode is just an Oracle shortcut (and is not ANSI SQL compliant) for a Case condition. The DECODE above means the same as:
    Case when emptype='best'  then 1
         when emptype='good'  then 2
         when emptype is null then 4
         else                      3
    End
    In both examples, 3 is returned if none of the other conditions are met.

    Also, Keep statement I have never used before...
    Try Google( keep dense_rank sql ) --> RANK, DENSE_RANK, FIRST and LAST Analytic Functions

    Edited by: Steve Cosner on Dec 3, 2012 1:48 PM

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points