I created a view in MS SQL. It worked perfectly. When I exported and imported my project into an Oracle environment, the view didn't work anymore. As far as I know, I used META SQL.
Hope someone can help!
SELECT OPRID
, DESCR1
, DESCR
, DESCR5
, DESCR6
, DESCR7
, YEAR
, WEEKOFYEAR
--When working more then 6 hours, subtract .5 hours break
, CASE WHEN (CONVERT(DECIMAL(10,1)
, DATEDIFF(MINUTE
, DG_MONDAY_START
, DG_MONDAY_END))/60) >= 6 THEN (CONVERT(DECIMAL(10,1)
, DATEDIFF(MINUTE
, DG_MONDAY_START
, DG_MONDAY_END))/60 - 0.5) ELSE (CONVERT(DECIMAL(10,1)
, DATEDIFF(MINUTE
, DG_MONDAY_START
, DG_MONDAY_END))/60) END AS 'MONDAY'
, CASE WHEN (CONVERT(DECIMAL(10,1)
, DATEDIFF(MINUTE
, DG_TUESDAY_START
, DG_TUESDAY_END))/60) >= 6 THEN (CONVERT(DECIMAL(10,1)
, DATEDIFF(MINUTE
, DG_TUESDAY_START
, DG_TUESDAY_END))/60 - 0.5) ELSE (CONVERT(DECIMAL(10,1)
, DATEDIFF(MINUTE
, DG_TUESDAY_START
, DG_TUESDAY_END))/60) END AS 'TUESDAY'
, CASE WHEN (CONVERT(DECIMAL(10,1)
, DATEDIFF(MINUTE
, DG_WEDNESDAY_START
, DG_WEDNESDAY_END))/60) >= 6 THEN (CONVERT(DECIMAL(10,1)
, DATEDIFF(MINUTE
, DG_WEDNESDAY_START
, DG_WEDNESDAY_END))/60 - 0.5) ELSE (CONVERT(DECIMAL(10,1)
, DATEDIFF(MINUTE
, DG_WEDNESDAY_START
, DG_WEDNESDAY_END))/60) END AS 'WEDNESDAY'
, CASE WHEN (CONVERT(DECIMAL(10,1)
, DATEDIFF(MINUTE
, DG_THURSDAY_START
, DG_THURSDAY_END))/60) >= 6 THEN (CONVERT(DECIMAL(10,1)
, DATEDIFF(MINUTE
, DG_THURSDAY_START
, DG_THURSDAY_END))/60 - 0.5) ELSE (CONVERT(DECIMAL(10,1)
, DATEDIFF(MINUTE
, DG_THURSDAY_START
, DG_THURSDAY_END))/60) END AS 'THURSDAY'
, CASE WHEN(CONVERT(DECIMAL(10,1)
, DATEDIFF(MINUTE
, DG_FRIDAY_START
, DG_FRIDAY_END))/60) >= 6 THEN (CONVERT(DECIMAL(10,1)
, DATEDIFF(MINUTE
, DG_FRIDAY_START
, DG_FRIDAY_END))/60 - 0.5) ELSE (CONVERT(DECIMAL(10,1)
, DATEDIFF(MINUTE
, DG_FRIDAY_START
, DG_FRIDAY_END))/60) END AS 'FRIDAY'
, '0' AS 'TOTAL'
--Total will be calculated in PeopleCode
FROM PS_DG_BPV_ROSTER
WHERE YEAR LIKE YEAR(TO_DATE(TO_CHAR(SYSDATE,'YYYY-MM-DD'),'YYYY-MM-DD'))
AND (DG_MONDAY_START != DG_MONDAY_END
OR DG_TUESDAY_START != DG_TUESDAY_END
OR DG_WEDNESDAY_START != DG_WEDNESDAY_END
OR DG_THURSDAY_START != DG_THURSDAY_END
OR DG_FRIDAY_START != DG_FRIDAY_END)
In your query, you use CONVERT command but in Oracle you must use CAST.
The type DECIMAL must be changed with NUMBER type as follow.
Instead of:
CONVERT(DECIMAL(10,1), field)
Use:
CAST(field as NUMBER(10,1))
Tell me if it's OK
EDIT After comment
In Oracle the Sql Server function DATEDIFF has been replaced with - sign, as a normal difference between two DATA field.
I advice you this site where you'll find the equivalent function between Oracle 10g and Sql Server 2008R2