Hello,
I am using Database 12c Enterprise Edition Release 12.1.0.2.0
When I execute the following query, all are ok.
SELECT
A.F_INS_INSURED_ID INS_INSURED_ID, B.SURNAME|| ' ' || B.NAME|| ' ' || B.FATHER_NAME || ' ' || B.MOTHER_NAME FULLNAME,
A.F_YEAR YEAR,
A.F_MONTH MONTH,
TO_DATE('01/'||TO_CHAR(NOT_OK_MONTH(A.F_MONTH))||'/'||TO_CHAR((A.F_YEAR)),'DD/MM/RRRR') DATE_1,
TO_DATE('01/'||TO_CHAR(1)||'/'||TO_CHAR(2020),'DD/MM/RRRR') BETWEEN_1,
TO_DATE('01/'||TO_CHAR(6)||'/'||TO_CHAR(2020),'DD/MM/RRRR') BETWEEN_2
FROM CON_WORK_APD_INSURERS A,
CON\_WORK\_APD\_EMPLOYERS E,
REG\_INSURED B,
CON\_ALL\_EMPLOYERS C
WHERE B.INSURED_ID = a.F_INS_INSURED_ID
AND a.F_INS_INSURED_ID = '9939998'
AND C.EMP_EMPLOYER_ID = A.F_EMPLOYER_ID
--AND TO_DATE('01/'||TO_CHAR(NOT_OK_MONTH(A.F_MONTH))||'/'||TO_CHAR((A.F_YEAR)),'DD/MM/RRRR') BETWEEN
-- TO_DATE('01/'||TO_CHAR(1)||'/'||TO_CHAR(2020),'DD/MM/RRRR') AND TO_DATE('01/'||TO_CHAR(6)||'/'||TO_CHAR(2020),'DD/MM/RRRR')
AND A.WORAPDEM_BRANCH_CODE = E.BRANCH_CODE
AND A.WORAPDEM_YEAR = E.YEAR
AND A.WORAPDEM_SID = E.SID
AND NVL(A.COPIED_TO_HISTORY, '0') = '0'
AND A.STATUS = '1'
AND E.STATUS = '1'
AND E.APD_RECEIVED_FLAG IN ('1', '4', '3')
AND A.REG_CHECKED = '1'
AND E.REG_CHECKED = '1'
AND A.F_MONTH IS NOT NULL
AND A.F_YEAR IS NOT NULL
and the result is
but when I uncomment lines 17-18, which are the colums DATE_1, BETWEEN_1 and BETWEEN_2 then
it appears me the message ORA-01840.
what is wrong?
not_ok_month is a function
CREATE OR REPLACE FUNCTION NOT_OK_MONTH(P_MONTH IN NUMBER) RETURN NUMBER IS
BEGIN
IF P_MONTH BETWEEN 1 AND 12 THEN
RETURN P_MONTH;
ELSE
RETURN 1;
END IF;
END;
Thank you