1 2 3 Previous Next 30 Replies Latest reply: Dec 5, 2012 9:12 AM by 883189 Go to original post RSS
      • 15. Re: query help....three conditions
        user346369
        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
          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
            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
              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
                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
                  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
                    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
                      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
                        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
                          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
                            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
                              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
                                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
                                  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
                                    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