Results 1 to 6 of 6
  1. #1
    Join Date
    Dec 2005
    Posts
    2

    Unanswered: sql comments required

    hi friends,

    i need sql comments :-

    i need to execute more then 10 years experiance people based on join date from the employee table ;

    table struc:-

    select empcd,empname,JOINDT from c_t_empmst

    please help me

    Regards,
    flexi

  2. #2
    Join Date
    Dec 2007
    Posts
    253
    I don't understand. Who do you want to execute? Do you mean that you want to remove them from the table? Do you want to ... ahhh I think I realise now.

    OK, you'll first of al need to calculate their time served based on their date of hire. Have a look in the documentation at the MONTHS_BETWEEN function here

  3. #3
    Join Date
    Aug 2007
    Posts
    25
    If you are just trying to return the result set, you simply need to use the where clause: where JOINDT < sysdate-3650
    Kirk Wahl
    GM Powertrain SMCO

  4. #4
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Quote Originally Posted by aldankirk
    If you are just trying to return the result set, you simply need to use the where clause: where JOINDT < sysdate-3650
    This is not accurate. Use

    where JOINDT < add_months(trunc(sysdate),-120)

    3650 days is not 10 years ago, 120 months is 10 years ago.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  5. #5
    Join Date
    Jul 2008
    Posts
    5
    if you want to execute more then 10 years experiance people based on join date from the employee table ;


    then
    use the sytnax

    where
    extract(year from join_date) <= extract(year from sysdate) - 10

    for performance create the funcitonal index on join_date

    create index idx_join_date
    on <<table>>(extract(year from join_date))
    compute statistics;

  6. #6
    Join Date
    Dec 2007
    Posts
    253
    This would be innaccurate and is not the correct technique to use. BEILSTWH's suggest would be the best way by far

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •