1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

how to use hiredate

Discussion in 'SQL PL/SQL' started by jayadhana, Sep 27, 2012.

  1. jayadhana

    jayadhana Active Member

    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    55
    Hi
    i am a beginner in plsql.
    my requirement is "how to display month name and no. of employees in that month of entered month number" using emp table.
    using case expression.

    Thx
    D Dhananjaya.
     
  2. kiran.marla

    kiran.marla Forum Genius

    Messages:
    403
    Likes Received:
    52
    Trophy Points:
    505
    Location:
    Khammam
    How far have you tried on this homework. Place your script here.
     
  3. jayadhana

    jayadhana Active Member

    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    55
    hi kiran,
    I tried like this, i tried to match the month names.


    declare
    v_empno emp1.empno%type;
    v_job emp1.job%type;
    v_hiredate emp1.hiredate%type;
    begin
    select empno,job,hiredate into v_empno,v_job,v_hiredate from emp where empno=&v_empno;
    case v_hiredate
    when to_char(v_hiredate,'month') ='january' then
    display('he is mr '||v_job||' and working in our company');
    when to_char(v_hiredate,'month') ='february' then
    display('he is mr '||v_job||' and working in our company');
    when to_char(v_hiredate,'month')='march' then
    display('he is mr '||v_job||' and working in our company');
    when to_char(v_hiredate,'month')= 'december' then
    display('he is mr '||v_job||' and working in our company');
    end case;
    end;

    THx
    Dhananjaya.
     
  4. kiran.marla

    kiran.marla Forum Genius

    Messages:
    403
    Likes Received:
    52
    Trophy Points:
    505
    Location:
    Khammam
    Why are you equating with month names. Any requirement like that ???
     
  5. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    You can use straigth SQ for that as below.

    Code (SQL):
    SELECT  COUNT(*)|| ' People joined our company in the month of '|| TO_CHAR ( HIREDATE, 'month')    FROM emp
    GROUP BY TO_CHAR ( HIREDATE, 'month')
     
  6. Bharat

    Bharat Community Moderator Forum Guru

    Messages:
    1,747
    Likes Received:
    147
    Trophy Points:
    1,805
    Location:
    Vijayawada, India
    Hi Dhananjaya,

    Use trim function before to_char in when statement.

    This is just because if we use to_char(hiredate,'month') then it returns the month name. If we have June then it returns june but it appends with spaces after june just because it takes highest character month (ex. september --> 9 digit) and june have 4 digit and then it appends june with 5 spaces at the end. So we need to use trim.

    Other solution is use as shown below:

    to_char(hiredate,'fmmonth') = 'january'

    This works fine.