beegeeblueboy
MIS
The SQL generated by Impromptru uses 'cdate' and has this error in SQL Server
[highlight #FCE94F]Msg 195, Level 15, State 10, Line 9
'cdate' is not a recognized built-in function name[/highlight]
I need advice please on how to edit the SQL to remove issue. I hope you can help.
Here is the script
select T1."EMPLOYEE_NUMBER" as c1,
((cdate(T1."START_DATE"))) as c2,
((cdate(T1."END_DATE"))) as c3,
T2."SURNAME" as c4,
T2."FIRST_FORNAME" as c5,
((cdate(T3."YEAR_START_DATE"))) as c6,
((cdate(T3."YEAR_END_DATE"))) as c7,
T3."ENTL_DAYS" as c8,
T3."EXTRA_DAYS" as c9,
T3."CFWD_DAYS" as c10,
T3."TAKEN_DAYS" as c11,
T3."EMP_HOL_ENTL_HOURS" as c12,
T3."EMP_HOL_EXTRA_HOURS" as c13,
T3."EMP_HOL_CFWD_HOURS" as c14,
T3."EMP_HOL_TAKEN_HOURS" as c15,
T3."EMP_HOL_HOURS_OR_DAYS_IND" as c16,
T4."LONG_DESC" as c17,
T3."ENTL_DAYS" + T3."EXTRA_DAYS" - T3."TAKEN_DAYS" as c18,
T3."EMP_HOL_ENTL_HOURS" + T3."EMP_HOL_EXTRA_HOURS" - T3."EMP_HOL_TAKEN_HOURS" as c19,
'' as c20,
T5."LONG_DESC" as c21
from "dbo"."D500M" T2,
((((("dbo"."D550M" T1 left outer join "dbo"."D583M" T3 on T1."PERSON_REF" = T3."PERSON_REF") left outer join "dbo"."D580M" T6 on T1."PERSON_REF" = T6."PERSON_REF") left outer join "dbo"."D455M" T7 on ((T6."PERSON_REF" = T7."PERSON_REF") and (T6."REF" = T7."EMP_POST_REF")) and (T6."START_COMPDATE" = T7."EMP_POST_START_COMPDATE")) left outer join "dbo"."D100M" T4 on T7."PERS_STR_REF_LEVEL5" = T4."REF") left outer join "dbo"."D200M" T5 on T6."REF" = T5."REF")
where (T1."PERSON_REF" = T2."PERSON_REF")
and (((((((cdate(T1."START_DATE"))) <= (@CURRENT_DATE)) and ((((cdate(T1."END_DATE"))) >= (@CURRENT_DATE)) or (((cdate(T1."END_DATE"))) IS NULL))) and ((((cdate(T6."START_DATE"))) <= (@CURRENT_DATE)) and ((((cdate(T6."END_DATE"))) >= (@CURRENT_DATE)) or (((cdate(T6."END_DATE"))) IS NULL)))) and ((((cdate(T3."YEAR_START_DATE")))) >= DATE '2022-08-01')) and ((((cdate(T3."YEAR_END_DATE")))) <= DATE '2023-07-31'))
and ((T4."LONG_DESC" = 'IT') or (T4."LONG_DESC" = 'E-SPACE'))
order by c18 desc,c17 asc,c4 asc
[highlight #FCE94F]Msg 195, Level 15, State 10, Line 9
'cdate' is not a recognized built-in function name[/highlight]
I need advice please on how to edit the SQL to remove issue. I hope you can help.
Here is the script
select T1."EMPLOYEE_NUMBER" as c1,
((cdate(T1."START_DATE"))) as c2,
((cdate(T1."END_DATE"))) as c3,
T2."SURNAME" as c4,
T2."FIRST_FORNAME" as c5,
((cdate(T3."YEAR_START_DATE"))) as c6,
((cdate(T3."YEAR_END_DATE"))) as c7,
T3."ENTL_DAYS" as c8,
T3."EXTRA_DAYS" as c9,
T3."CFWD_DAYS" as c10,
T3."TAKEN_DAYS" as c11,
T3."EMP_HOL_ENTL_HOURS" as c12,
T3."EMP_HOL_EXTRA_HOURS" as c13,
T3."EMP_HOL_CFWD_HOURS" as c14,
T3."EMP_HOL_TAKEN_HOURS" as c15,
T3."EMP_HOL_HOURS_OR_DAYS_IND" as c16,
T4."LONG_DESC" as c17,
T3."ENTL_DAYS" + T3."EXTRA_DAYS" - T3."TAKEN_DAYS" as c18,
T3."EMP_HOL_ENTL_HOURS" + T3."EMP_HOL_EXTRA_HOURS" - T3."EMP_HOL_TAKEN_HOURS" as c19,
'' as c20,
T5."LONG_DESC" as c21
from "dbo"."D500M" T2,
((((("dbo"."D550M" T1 left outer join "dbo"."D583M" T3 on T1."PERSON_REF" = T3."PERSON_REF") left outer join "dbo"."D580M" T6 on T1."PERSON_REF" = T6."PERSON_REF") left outer join "dbo"."D455M" T7 on ((T6."PERSON_REF" = T7."PERSON_REF") and (T6."REF" = T7."EMP_POST_REF")) and (T6."START_COMPDATE" = T7."EMP_POST_START_COMPDATE")) left outer join "dbo"."D100M" T4 on T7."PERS_STR_REF_LEVEL5" = T4."REF") left outer join "dbo"."D200M" T5 on T6."REF" = T5."REF")
where (T1."PERSON_REF" = T2."PERSON_REF")
and (((((((cdate(T1."START_DATE"))) <= (@CURRENT_DATE)) and ((((cdate(T1."END_DATE"))) >= (@CURRENT_DATE)) or (((cdate(T1."END_DATE"))) IS NULL))) and ((((cdate(T6."START_DATE"))) <= (@CURRENT_DATE)) and ((((cdate(T6."END_DATE"))) >= (@CURRENT_DATE)) or (((cdate(T6."END_DATE"))) IS NULL)))) and ((((cdate(T3."YEAR_START_DATE")))) >= DATE '2022-08-01')) and ((((cdate(T3."YEAR_END_DATE")))) <= DATE '2023-07-31'))
and ((T4."LONG_DESC" = 'IT') or (T4."LONG_DESC" = 'E-SPACE'))
order by c18 desc,c17 asc,c4 asc