12 Replies Latest reply: Jan 24, 2013 4:07 PM by Peter vd Zwan RSS

    Help to extract the Column Name depending on the row values

    user8966924
      Hello All,

      I have a table with below format

      Table Name: Employees


      Emp Name Monday Tuesday Wednesday Thursday Friday Saturday

      John yes NULL yes yes NULL NULL
      Michael NULL yes NULL yes yes NULL
      Smith NULL yes yes NULL yes yes

      In the above I have explicitly mentioned NULL just for better understanding.

      I am trying to send an email using HTMLDB_MAIL.SEND which will have the body as following

      "John is absent on Tuesday, Friday and Saturday
      Michael is absent on Monday, Wednesday and Saturday
      Smith is absent on Monday and Thursday"

      I am trying to do this using cursor, fetching each row and matching the value with NULL. But I am not able to display the column name.

      Can somebody please help me?

      Regards,
      RS.

      Edited by: user8966924 on Jan 24, 2013 4:35 AM
        • 1. Re: Help to extract the Column Name depending on the row values
          Rahul_India
          user8966924 wrote:
          Hello All,

          I have a table with below format

          Table Name: Employees


          Emp Name Monday Tuesday Wednesday Thursday Friday Saturday

          John yes NULL yes yes NULL NULL
          Michael NULL yes NULL yes yes NULL
          Smith NULL yes yes NULL yes yes

          In the above I have explicitly mentioned NULL just for better understanding.

          I am trying to send an email using HTMLDB_MAIL.SEND which will have the body as following

          "John is absent on Tuesday, Friday and Saturday
          Michael is absent on Monday, Wednesday and Saturday
          Smith is absent on Monday and Thursday"

          I am trying to do this using cursor, fetching each row and matching the value with NULL. But I am not able to display the column name.

          Can somebody please help me?

          Regards,
          RS.

          Edited by: user8966924 on Jan 24, 2013 4:35 AM
          Can you post the DDL and DML
          • 2. Re: Help to extract the Column Name depending on the row values
            971895
            Hot code the value if the yes is coming...
            • 3. Re: Help to extract the Column Name depending on the row values
              Peter vd Zwan
              Hi,

              Try this:
              with employees as
              (
              select 'John' Emp_Name    , 'yes' Monday, null  Tuesday, 'yes' Wednesday, 'yes' Thursday, null  Friday, null  Saturday from dual union all
              select 'Michael' Emp_Name , null  Monday, 'yes' Tuesday, null  Wednesday, 'yes' Thursday, 'yes' Friday, null  Saturday from dual union all
              select 'Smith' Emp_Name   , null  Monday, 'yes' Tuesday, 'yes' Wednesday, null  Thursday, 'yes' Friday, 'yes' Saturday from dual 
              )
              select
                e.emp_name || ' is absent on ' ||
                replace (trim( ',' from nvl2(monday,null, 'Monday')
                || nvl2(Tuesday,null, ',Tuesday')
                || nvl2(Wednesday,null, ',Wednesday')
                || nvl2(Thursday,null, ',Thursday')
                || nvl2(Friday,null, ',Friday')
                || nvl2(Saturday,null, ',Saturday')), ',', ', ') txt
              
              from
                employees e
              
              where
                Monday          is null
                or Tuesday      is null
                or Wednesday    is null
                or Thursday     is null
                or Friday       is null
                or Saturday     is null
              ;
              
              
              TXT                                                                                                                   
              -----------------------------------------------------------------------------------------------------------------------
              John is absent on Tuesday, Friday, Saturday                                                                             
              Michael is absent on Monday, Wednesday, Saturday                                                                        
              Smith is absent on Monday, Thursday                                                                                     
              Regards,

              Peter
              • 4. Re: Help to extract the Column Name depending on the row values
                BluShadow
                Or another way... for fun (the above NVL2 method is simpler)...
                SQL> ed
                Wrote file afiedt.buf
                
                  1  with Emp as (select 'John' as Nm, 'yes' as Monday, null as Tuesday, 'yes' as Wednesday, 'yes' as Thursday, null as Friday, null as Saturday from dual union all
                  2               select 'Michael', null, 'yes', null, 'yes', 'yes', null from dual union all
                  3               select 'Smith', null, 'yes', 'yes', null, 'yes', 'yes' from dual)
                  4  select nm||' is absent '||
                  5         listagg(decode(lvl,1,'Monday',2,'Tuesday',3,'Wednesday',4,'Thursday',5,'Friday',6,'Saturday'),',') within group (order by rn) as absences
                  6  from (
                  7        select nm
                  8              ,lvl
                  9              ,row_number() over (partition by nm order by lvl) as rn
                 10        from   emp cross join (select level as lvl from dual connect by level <= 6)
                 11        where  decode(lvl, 1, Monday, 2, Tuesday, 3, Wednesday, 4, Thursday, 5, Friday, 6, Saturday) is null
                 12       )
                 13* group by nm
                SQL> /
                
                ABSENCES
                ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
                John is absent Tuesday,Friday,Saturday
                Michael is absent Monday,Wednesday,Saturday
                Smith is absent Monday,Thursday
                • 5. Re: Help to extract the Column Name depending on the row values
                  chris227
                  Please rethink about your data model. Comsider
                  create table emps ( empid number, name varchar2(30))
                  insert into emps values (1, 'John')
                  insert into emps values (2, 'Micheal')
                  insert into emps values (3, 'Smith')
                  
                  create table absence ( empid number ,day number)
                  insert into absence values (1,1)
                  insert into absence values (1,3)
                  insert into absence values (1,4)
                  insert into absence values (2,2)
                  insert into absence values (2,4)
                  insert into absence values (2,5)
                  insert into absence values (3,2)
                  insert into absence values (3,3)
                  insert into absence values (3,5)
                  insert into absence values (3,6)
                  
                  select
                   name
                  ||' is absence on '
                  ||to_char(to_date(day+6,'J'),'DAY')
                   abscence
                  from
                  absence a
                  join
                  emps e
                  on
                  a.empid=e.empid
                  order by name, day
                  
                  ABSCENCE 
                  John is absence on MONDAY  
                  John is absence on WEDNESDAY 
                  John is absence on THURSDAY  
                  Micheal is absence on TUESDAY  
                  Micheal is absence on THURSDAY  
                  Micheal is absence on FRIDAY  
                  Smith is absence on TUESDAY  
                  Smith is absence on WEDNESDAY 
                  Smith is absence on FRIDAY  
                  Smith is absence on SATURDAY  
                  
                  or
                  
                  select
                   name
                  ||' is absence on '
                  ||
                  listagg(trim(to_char(to_date(day+6,'J'),'Day')), ', ')
                  within group (order by day)||'.'
                   abscence
                  from
                  absence a
                  join
                  emps e
                  on
                  a.empid=e.empid
                  group by name
                  order by name
                  
                  ABSCENCE 
                  John is absence on Monday, Wednesday, Thursday. 
                  Micheal is absence on Tuesday, Thursday, Friday. 
                  Smith is absence on Tuesday, Wednesday, Friday, Saturday. 
                  Just for self study, thsi is i was aiming for
                  select
                  name
                  ||decode(decode(day,null,1,2),1
                          ,' is not absence on '
                          ,' is absence on '
                          )
                  ||
                  listagg(trim(to_char(to_date(wd+6,'J'),'Day')), ', ')
                  within group (order by wd)||'.'
                   absence
                  from
                  (select level wd from dual
                  connect by level < 8) w
                  left outer join
                  absence a
                  partition by (a.empid)
                  on
                  w.wd=a.day
                  join
                  emps e
                  on
                  a.empid=e.empid
                  group by name, decode(day,null,1,2)
                  order by name, absence
                  
                  ABSENCE 
                  John is absence on Monday, Wednesday, Thursday. 
                  John is not absence on Tuesday, Friday, Saturday, Sunday. 
                  Micheal is absence on Tuesday, Thursday, Friday. 
                  Micheal is not absence on Monday, Wednesday, Saturday, Sunday. 
                  Smith is absence on Tuesday, Wednesday, Friday, Saturday. 
                  Smith is not absence on Monday, Thursday, Sunday. 
                  Serveral constraints/keys to add would be usefull.

                  Edited by: chris227 on 24.01.2013 06:49

                  Edited by: chris227 on 24.01.2013 07:03

                  Edited by: chris227 on 24.01.2013 07:08

                  Edited by: chris227 on 24.01.2013 07:56
                  • 6. Re: Help to extract the Column Name depending on the row values
                    user8966924
                    Hello All,

                    Thanks for the reply.

                    But I want this to be dynamic which I can add it in a procedure or a function. Once I run, I should get all the absentee's names along with the day they were absent on.

                    Hi Chris, Sorry, but this is just an example that I gave. Don't want to change the design. Thanks for your suggestion btw :-)

                    Regards,
                    RS
                    • 7. Re: Help to extract the Column Name depending on the row values
                      chris227
                      select 
                       column_name
                      ,column_id
                      from all_tab_columns
                      where
                      table_name = 'EMPS'
                      There you are fetch the row in some array, take the column_id matching to the index of the array which is not null.
                      I cant do it for you since i didnt know pl/sql that well.

                      Edited by: chris227 on 24.01.2013 08:15
                      • 8. Re: Help to extract the Column Name depending on the row values
                        Peter vd Zwan
                        Hi,

                        I don't understand what you want to be dynamic.
                        The solution I gave is giving the result you asked for. You can use that SQL in a PL/SQL.

                        In general you should not use dynamic SQL if not needed.

                        Regards,

                        Peter
                        • 9. Re: Help to extract the Column Name depending on the row values
                          AlbertoFaenza
                          Hi,

                          just use Peter's query, collect into a nested table the output and create one CLOB variable from collection to use it as a body:
                          create or replace function getbodyemp
                          return clob 
                          is
                             p_body_out      clob;
                             type tp_tbemp   is table of varchar2 (100);
                             v_tbemp         tp_tbemp;
                          begin
                             -- collect the output into a nested table
                             with employees as
                             (
                             select 'John' Emp_Name    , 'yes' Monday, null  Tuesday, 'yes' Wednesday, 'yes' Thursday, null  Friday, null  Saturday from dual union all
                             select 'Michael' Emp_Name , null  Monday, 'yes' Tuesday, null  Wednesday, 'yes' Thursday, 'yes' Friday, null  Saturday from dual union all
                             select 'Smith' Emp_Name   , null  Monday, 'yes' Tuesday, 'yes' Wednesday, null  Thursday, 'yes' Friday, 'yes' Saturday from dual 
                             )
                             select
                               e.emp_name || ' is absent on ' ||
                               replace (trim( ',' from nvl2(monday,null, 'Monday')
                               || nvl2(Tuesday,null, ',Tuesday')
                               || nvl2(Wednesday,null, ',Wednesday')
                               || nvl2(Thursday,null, ',Thursday')
                               || nvl2(Friday,null, ',Friday')
                               || nvl2(Saturday,null, ',Saturday')), ',', ', ') txt
                             bulk collect into v_tbemp
                             from
                               employees e
                             where
                               monday          is null
                               or tuesday      is null
                               or wednesday    is null
                               or thursday     is null
                               or friday       is null
                               or saturday     is null
                             ;
                                    
                             for i IN 1 .. v_tbemp.count
                             loop
                                p_body_out := p_body_out || v_tbemp(i) || chr(10);
                             end loop;
                             
                             return p_body_out;
                                    
                          end getbodyemp;
                          /
                          
                          select getbodyemp() from dual;
                          
                          GETBODYEMP()                                                                    
                          --------------------------------------------------------------------------------
                          John is absent on Tuesday, Friday, Saturday                                     
                          Michael is absent on Monday, Wednesday, Saturday                                
                          Smith is absent on Monday, Thursday                                             
                                                                                                          
                                                                                                          
                          1 row selected.
                          Regards.
                          Al
                          • 10. Re: Help to extract the Column Name depending on the row values
                            chris227
                            I guess he is looking for some kind of reflection.
                            There was a brilliant thread in this place on this, but i didnt find it yet.
                            May be using refcursor is a way, dont know.
                            • 11. Re: Help to extract the Column Name depending on the row values
                              user8966924
                              Hello,

                              If I understand correctly then this would work only with the above data.

                              What if the data in the table is growing and I would want to just run a query to fetch the data?

                              As mentioned earlier, I am trying to use cursors but unsucessful till now

                              Regards,
                              RS
                              • 12. Re: Help to extract the Column Name depending on the row values
                                Peter vd Zwan
                                Hi,

                                Since you did not post a table creation script we had to make some our selfs.

                                if you want to use the employees table in your schema you can just comment the with part. like below:
                                /*
                                   with employees as
                                   (
                                   select 'John' Emp_Name    , 'yes' Monday, null  Tuesday, 'yes' Wednesday, 'yes' Thursday, null  Friday, null  Saturday from dual union all
                                   select 'Michael' Emp_Name , null  Monday, 'yes' Tuesday, null  Wednesday, 'yes' Thursday, 'yes' Friday, null  Saturday from dual union all
                                   select 'Smith' Emp_Name   , null  Monday, 'yes' Tuesday, 'yes' Wednesday, null  Thursday, 'yes' Friday, 'yes' Saturday from dual 
                                   )
                                */
                                   select
                                     e.emp_name || ' is absent on ' ||
                                ...
                                Regards,

                                Peter