This discussion is archived
1 2 3 Previous Next 30 Replies Latest reply: Dec 5, 2012 7:12 AM by 883189 RSS

query help....three conditions

883189 Newbie
Currently Being Moderated
Hi All,

I have a table with the following data

empno emptype empstatus
1     best     Active
2     good     drop
3     best     Active
1     good     cancel
4     good     left
5

This table consists of three columns
Emptype has two distinct values - good,best
Empstatus consists of one status - Active for best type
Empstatus consists of three status - Drop,cancel,left for good type.
One emp can get either any one of these three status in good type

The highest level of type is best followed by good

An emp can get both the types

I need to get the highest level of status achieved so far for an emp
For example, emp 1 has got both best and good types
Then the output for emp 1 will be

empno higheststatus
1     Active

If the emp didnot get any, it should display NULL.

The output for the above data should be
empno higheststatus
1     Active
2     drop
3     Active
4     left
5     

Thank u for ur help !!
  • 1. Re: query help....three conditions
    BoopathiL Newbie
    Currently Being Moderated
    Hi.,
    Try the following query

    Select empno, Min(Decode(emptype,'best',1,'good',2,Null)), Min(EMPSTATUS)
    From test_emp_Status
    Group By empno
    Order By empno;
  • 2. Re: query help....three conditions
    Frank Kulash Guru
    Currently Being Moderated
    Hi,

    If the possible values for emptype happen to be in alphabetic order (and 2 values will always be in either ASCending or DESCending order), then you don't have to use DECODE:
    SELECT       empno
    ,       MIN (empstatus) KEEP (DENSE_RANK FIRST ORDER BY emptype)
                   AS top_status
    FROM      a
    GROUP BY  empno
    ;
  • 3. Re: query help....three conditions
    883189 Newbie
    Currently Being Moderated
    Hi i tried both the querys
    both doesnt work... they are not giving right data
    I wrote my own query as follows:

    select empno,
    case when emptype ='best' then 'Active'
    when emptype='good' then 'drop' --348
    when achieved_level='good' then 'cancel' --198
    when achieved_level='good' then 'left' -- 69
    else null
    end as "emphighstatus"
    from emp
    where empno=1

    but the output i got is,

    empno emphighstatus
    1 Active
    1 cancel

    The output i should get is
    empno emphighstatus
    1 Active

    Can someone help me on this ? Thank u in advance
  • 4. Re: query help....three conditions
    Frank Kulash Guru
    Currently Being Moderated
    Hi,

    Sorry, I can't reproduce the problem.
    880186 wrote:
    Hi i tried both the querys
    both doesnt work... they are not giving right data
    I wrote my own query as follows:

    select empno,
    case when emptype ='best' then 'Active'
    when emptype='good' then 'drop' --348
    when achieved_level='good' then 'cancel' --198
    when achieved_level='good' then 'left' -- 69
    else null
    end as "emphighstatus"
    from emp
    where empno=1
    That doesn't look like either of the solutions posted above.
    but the output i got is,

    empno emphighstatus
    1 Active
    1 cancel

    The output i should get is
    empno emphighstatus
    1 Active

    Can someone help me on this ? Thank u in advance
    Whenever you have a problem, please post a little sample data (CREATE TABLE and INSERT statements, relevant columns only) from all tables involved.
    Also post the results you want from that data, and an explanation of how you get those results from that data, with specific examples.
    Always say which version of Oracle you're using (for example, 11.2.0.2.0).
    See the forum FAQ {message:id=9360002}
  • 5. Re: query help....three conditions
    883189 Newbie
    Currently Being Moderated
    I am sorry for the consusion

    The oracle version i use is 10.1.1 and my question is as follows:

    create table emp (empno number(2), emptype varchar2(10), empstatus varchar2(10);

    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,'good',null)
    insert into emp values(5,'best',null)

    emptype column consists of two values - best and good
    For best emptype, there will be only one empstatus - Active
    For good emptype, there will be three empstatus - drop,cancel,left.
    One empno can only have any one of the three empstatus in good emptype
    One empno can have both the emptype - best and good and also can be null

    The output should be empno, higheststatus columns
    higheststatus column is defined as follows:
    If an empno consists of both emptype, then the higheststatus is Active
    If an empno consists of only emptype as best then the higheststatus is good
    if an empno consists of only emptype as good then the higheststatus is any of the three - drop,cancel,left
    if an empno consists of none empstatus, the it will be blank

    But the empno should be unique as an empno can have only one higheststatus

    The higheststatus is not defined anywhere else, we need to define it by using some case or someother statements.

    I wrote my own query as follows:

    select empno,
    case when emptype ='best' then 'Active'
    when emptype='good' then 'drop' --348
    when achieved_level='good' then 'cancel' --198
    when achieved_level='good' then 'left' -- 69
    else null
    end as "higheststatus"
    from emp
    where empno=1

    but the output i got is,
    empno higheststatus
    1 Active
    1 cancel
    The output i should get is
    empno higheststatus
    1 Active

    Let me know if you have any questions.
  • 6. Re: query help....three conditions
    user346369 Expert
    Currently Being Moderated
    SQL> create table T2
      2    (empno varchar2(5), emptype varchar2(7), empstatus varchar2(9) );
    SQL> Insert into T2 values('1', 'best', 'Active');
    SQL> Insert into T2 values('2', 'good', 'drop');
    SQL> Insert into T2 values('3', 'best', 'Active');
    SQL> Insert into T2 values('1', 'good', 'cancel');
    SQL> Insert into T2 values('4', 'good', 'left');
    SQL> Insert into T2 values('5',  null,   null);
    SQL> set feedback on
    SQL> select * from T2 order by empno,emptype;
    
    EMPNO EMPTYPE EMPSTATUS
    ----- ------- ---------
    1     best    Active
    1     good    cancel
    2     good    drop
    3     best    Active
    4     good    left
    5
    
    6 rows selected.
    
    SQL> 
    SQL> Select empno, min(nvl(emptype,'~')) as "Min.Typ"
      2   from T2
      3   group by empno
      4   order by empno;
    
    EMPNO Min.Typ
    ----- -------
    1     best
    2     good
    3     best
    4     good
    5     ~
    
    5 rows selected.
    
    SQL> 
    SQL> select empno, emptype, empstatus
      2  from T2
      3  where (empno,nvl(emptype,'~'))
      4     in (Select empno, min(nvl(emptype,'~'))
      5         from T2
      6         group by empno )
      7  order by empno;
    
    EMPNO EMPTYPE EMPSTATUS
    ----- ------- ---------
    1     best    Active
    2     good    drop
    3     best    Active
    4     good    left
    5
    
    5 rows selected.
    
    SQL> 
    SQL> select empno, empstatus as"High Stat"
      2  from T2
      3  where (empno,nvl(emptype,'~'))
      4     in (Select empno, min(nvl(emptype,'~'))
      5         from T2
      6         group by empno )
      7  order by empno;
    
    EMPNO High Stat
    ----- ---------
    1     Active
    2     drop
    3     Active
    4     left
    5
    
    5 rows selected.
  • 7. Re: query help....three conditions
    Frank Kulash Guru
    Currently Being Moderated
    Hi,

    Oh, I see: the empstatus column in the table has nothing to do with this problem. The output column higheststatus depends only on empno and emptype.
    In that case:
    SELECT    empno
    ,       CASE
               WHEN  COUNT (DISTINCT emptype) = 2
                        THEN  'Active'
               WHEN  MAX (emptype) = 'best'
                        THEN  'good'
               WHEN  MAX (emptype) = 'good'
                        THEN  'drop'  -- or 'cancel', or 'left'
           END         AS highestempstatus
    FROM       emp
    GROUP BY  empno
    -- WHERE  empno     IN (1)     -- If wanted
    ORDER BY  empno
    ;
  • 8. Re: query help....three conditions
    883189 Newbie
    Currently Being Moderated
    Hi Frank,

    thank you for the logic....
    But the data is not showing up correctly... its not showing any of cancel or left empstatus
    nly the higheststatus is showing up as Active/ drop.....

    Also, I couldn't understand it exactly...
    What does max function do here ?

    Edited by: 880186 on Nov 29, 2012 11:40 AM
  • 9. Re: query help....three conditions
    883189 Newbie
    Currently Being Moderated
    Hi Steve,

    I didnot run the query
    Because I didn't get the logic...
    what does ~ do here?

    Edited by: 880186 on Nov 29, 2012 11:40 AM
  • 10. Re: query help....three conditions
    Frank Kulash Guru
    Currently Being Moderated
    880186 wrote:
    Hi Frank,

    thank you for the logic....
    But the data is not showing up correctly... its not showing any of cancel or left empstatus
    You said you would accept any of the 3:
    if an empno consists of only emptype as good then the higheststatus is any of the three - drop,cancel,left
    If you'd rather have 'cancel', then use 'cancel'. If you'd rather have 'left', then use 'left'. If you want 'cancel' sometimes, and 'drop' at other times, and 'left' at other times, explain how you decide which one you want. If you can say it in English, someone will help you do it in SQL.
    nly the higheststatus is showing up as Active/ drop.....
    Point out exactly where the query I posted is giving the wrong results, and explain how you get the right results in those places.
    Also, I couldn't understand it exactly...
    What does max function do here ?
    ,       CASE
               WHEN  COUNT (DISTINCT emptype) = 2
                        THEN  'Active'
               WHEN  MAX (emptype) = 'best'
                        THEN  'good'
               WHEN  MAX (emptype) = 'good'
                        THEN  'drop'  -- or 'cancel', or 'left'
           END         AS highestempstatus
    You said there were only 2 possible values for emptype: 'best' and 'good'. The requirements you posted were
    If an empno consists of both emptype, then the higheststatus is Active
    If an empno consists of only emptype as best then the higheststatus is good
    if an empno consists of only emptype as good then the higheststatus is any of the three - drop,cancel,left
    if an empno consists of none empstatus, the it will be blank
    The CASE expression above does that, in that order. By the time the second WHEN clause
    WHEN  MAX (emptype) = 'best'
    is evaluated, we know that there are not 2 distinct emptypes present for this empno. 2 is the maximum number possible, so, if there are not 2 distinct values, there must be 1 or 0 dstinct values. The next 2 WHEN clauses test whether there is 1 value for emptype. If there are 0 values, then MAX will return NULL, and the WHEN clause will not be TRUE. If there is 1 value of emptype, then MAX will return that value. (MIN will also return that value; it doesn't matter if you use MIN or MAX in this case.)
  • 11. Re: query help....three conditions
    883189 Newbie
    Currently Being Moderated
    frank,

    I am sorry to not explain it clearly... But this is how i can say it... let me know if its confusing...

    If the emp is in both emptype(good and best), Best emptype is to be considered and "Active" will be displayed in output
    If the emp is in only best emptype then "Active" will be displayed in output
    If the emp is in only good emptype then "(drop or cancel or left)" will be displayed in output as per what value is present in the table
    If the emp is in both/one type with no status then it will be null
  • 12. Re: query help....three conditions
    Frank Kulash Guru
    Currently Being Moderated
    Hi,
    880186 wrote:
    frank,

    I am sorry to not explain it clearly... But this is how i can say it... let me know if its confusing...
    It would be less confusing if you posted the exact results you want.
    If the emp is in both emptype(good and best), Best emptype is to be considered and "Active" will be displayed in output
    If the emp is in only best emptype then "Active" will be displayed in output
    If the emp is in only good emptype then "(drop or cancel or left)" will be displayed in output as per what value is present in the table
    If the emp is in both/one type with no status then it will be null
    Then change the CASE expression to:
    ,       CASE
               WHEN  MAX (emptype) = 'best'
                        THEN  'Active'
               WHEN  MAX (emptype) = 'good'
                        THEN  '(drop or cancel or left)'
           END         AS highestempstatus
    If emptype is 'best' on some rows, and 'good' on other rows for the same empno, then MIN (emptype) will be 'best', because 'best' comes before 'good' in alphabetic order.
    If the only value of emptype is 'good', then what exactly do you want? Do you want the 24-character literal '(drop or cancel or left)', as shown in the expression above, or do you want something that is derived from other columns in the table? If you want something derived from the table, explain how you get it.
    Does the existing empstatus column have something to do with this problem after all?
  • 13. Re: query help....three conditions
    user346369 Expert
    Currently Being Moderated
    880186 wrote:
    Hi Steve,

    I didnot run the query
    Because I didn't get the logic...
    what does ~ do here?
    NVL(emptype,'~') returns the value of emptype when it is not null, but when it IS null, I told it to return a single ~ (tilde) character. The tilde is the highest ascii letter in the normal character set, so when I ask for the MIN(nvl(emptype,'~')), Oracle returns the other values for emptype for that empno before it would return the tilde character.

    The MIN() function is there to return first "best", then "good", and if a null is found and none of the others, would return the tilde.

    The embedded select is used to find the row with your preferred emptype for each empno.

    ----------
    And I have a question for you:
    In your first post, you showed this
    empno  emptype  empstatus
      1      best    Active
      2      good    drop
      3      best    Active
      1      good    cancel
      4      good    left
      5
    Question: Is the "Active" value stored in your table, or do you want your query to return that value every time it finds emptype='best'? Your subsequent discussion makes it appear that it is a value supplied by SQL.

    More questions:
    Can your table have duplicate rows?
    Can one empno have multiple rows wher eemptype='good', but empstatus is different? If so, which value do you want?

    Edited by: Steve Cosner on Nov 29, 2012 1:09 PM

    Edited by: Steve Cosner on Nov 29, 2012 1:15 PM
  • 14. Re: query help....three conditions
    883189 Newbie
    Currently Being Moderated
    Frank,

    I guess I couldn't explain it clearly...
    I amr eally sorry for that...
    But I never mentioned I need it in alphabetical order though...
    anyhow, i tried a lot and finally i am getting the right output
    the query i used is,

    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


    Thank you so much for ur help...
    I really appreciate ur time...
    Thanks a lot.... sorry for the long discussion too...
1 2 3 Previous Next

Legend

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