This discussion is archived
12 Replies Latest reply: Jan 24, 2013 2:07 PM by Peter vd Zwan RSS

Help to extract the Column Name depending on the row values

user8966924 Newbie
Currently Being Moderated
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 Journeyer
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Guru Moderator
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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

Legend

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