4 Replies Latest reply: Sep 30, 2009 10:47 AM by cd_2 RSS

    Convert sql select statement to oracle

      Hi All,

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


      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'))     
      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 ,

      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) )     
      case when location.payroll_status <> 'N'
      then dateadd(mm,1,location.next_pay_date)      
      else location.next_pay_date
      end as PaymentDate               ,
      isnull(grade_master.leave_type,'C') ,
      eDocsNetEmployeesLeave.StartDate ,          
      from eDocsNetEmployeesLeave ,
      eDocsNetEmployeesLeaveDetails ,
      eDocsNetLeaveTypes ,
      emp_master ,
      grade_master ,
      attendance_master ,
      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
        • 1. Re: Convert sql select statement to oracle
          Hans Forbrich
          Seems like you want to convert a SQL statement from the ??? dialect to the Oracle dialect. *(It would be useful to indicate the non-ANSI standard SQL you are starting with.)

          Part of the problem is that you use several date related functions. Some are unnecessary in Oracle and some need to translated into Oracle functions as found in the Functions section (chapter 5) of the SQL Reference manual at http://www.oracle.com/pls/db102/homepage

          Note that columns and expressions of type 'date' in ORacle always contain all of "yyyy mm dd hh mi ss" and you need to format and trauncate as necessary.

          Also note that '09-JAN-31' is NOT an Oracle date, but rather a character representation of a date which must be converted to/from a date expression. You will often need to use the to_date() and to_char() functions with a format mask as the second parameter. This is also descreibed in the first 2 chapters of the SQL Reference manual.
          • 2. Re: Convert sql select statement to oracle
            The function datename gives it away, it's a T-SQL function, I'd guess that we're talking mssql here.

            To the OP: Try this Sqlserver vs. Oracle comparison.

            • 3. Re: Convert sql select statement to oracle
              Hans Forbrich
              (I'd guessed that as well. Just annoys me that people talk about SQL Server as 'sql'. :-) )
              • 4. Re: Convert sql select statement to oracle
                Yeah, well you know how it is. Makes me wonder how many Oracle questions are asked in a mssql related forum, ;-)