4 Replies Latest reply on Sep 30, 2009 3:47 PM by cd_2

    Convert sql select statement to oracle

    723269
      Hi All,

      Can anyone help me converting this Sql select statement to oracle ....



      -----------------------------------------Query--------------------------------------------------------------

      select emp_master.emp_code ,
      emp_master.dept_cd ,
      attendance_master.daily_attn_code ,
      attendance_master.linked_column ,
      case when location.payroll_status <> 'N' and eDocsNetEmployeesLeave.StartDate < dateadd(mm, 1 , convert(smalldatetime, datename(yy ,location.next_pay_date) + '/'+ datename(mm ,location.next_pay_date)+ '/01'))     
      then
      dateadd(mm, 1 , convert(smalldatetime, datename(yy ,location.next_pay_date) + '/'+ datename(mm ,location.next_pay_date)+ '/01'))
           
      when eDocsNetEmployeesLeave.StartDate < convert(smalldatetime, datename(yy ,location.next_pay_date) + '/'+ datename(mm ,location.next_pay_date)+ '/01')     
      then convert(smalldatetime, datename(yy ,location.next_pay_date) + '/'+ datename(mm ,location.next_pay_date)+ '/01') else eDocsNetEmployeesLeaveDetails.StartDate           
      end ,
      eDocsNetEmployeesLeaveDetails.NoOfDays,          

      case when eDocsNetEmployeesLeave.StartDate > location.next_pay_date     
      then convert(datetime , convert(varchar, dateadd(ss,-1, dateadd(mm, 1, convert(datetime , datename(yy,eDocsNetEmployeesLeave.StartDate)+ '/' + datename(mm,eDocsNetEmployeesLeave.StartDate)+ '/01') )),106) )     
      else      
      case when location.payroll_status <> 'N'
      then dateadd(mm,1,location.next_pay_date)      
      else location.next_pay_date
      end      
      end as PaymentDate               ,
      isnull(grade_master.leave_type,'C') ,
      eDocsNetEmployeesLeave.StartDate ,          
      eDocsNetEmployeesLeaveDetails.LeaveType
      from eDocsNetEmployeesLeave ,
      eDocsNetEmployeesLeaveDetails ,
      eDocsNetLeaveTypes ,
      emp_master ,
      grade_master ,
      attendance_master ,
      location
      where eDocsNetEmployeesLeaveDetails.RequestID     = eDocsNetEmployeesLeave.RequestID and
      eDocsNetEmployeesLeave.EmployeeID = emp_master.emp_code and
      eDocsNetEmployeesLeaveDetails.LeaveType = eDocsNetLeaveTypes.LeaveTypeID and
      eDocsNetLeaveTypes.loc_cd = emp_master.loc_cd and
      location.loc_cd = emp_master.loc_cd and
      attendance_master.loc_cd = emp_master.loc_cd and
      attendance_master.linked_column = eDocsNetLeaveTypes.LinkedAttendance and
      grade_master.loc_cd = emp_master.loc_cd and
      grade_master.grade_cd = emp_master.grade_cd and
      eDocsNetEmployeesLeaveDetails.RequestID      = @RequestID
      order by eDocsNetEmployeesLeaveDetails.StartDate




      ------------------------------------------------------------------------------------------------------------------

      Thanks in Advance
      Smiley