Я создал представление в MS SQL. Это сработало отлично. Когда я экспортировал и импортировал свой проект в среду Oracle, представление больше не работало. Насколько я знаю, я использовал META SQL.
Надеюсь, кто-то может помочь!
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)
CASE MONDAY WHEN ... THEN ... ELSE ... END, CASE TUESDAY WHEN ... THEN ... ELSE ... END
- person mavroprovato   schedule 06.03.2014CASE ... END AS MONDAY
- person mavroprovato   schedule 06.03.2014