Good Morning,
I created table:
CREATE TABLE DWG_LOG
( DL_ID_PK NUMBER NOT NULL ENABLE,
DL_DRWNG_ID_FK"NUMBER,
DL_REQUESTOR VARCHAR2(400 BYTE),
DL_PHONE VARCHAR2(40 BYTE),
DL_DATE_COPIED DATE,
DL_APPROVER VARCHAR2(400 BYTE),
DL_NOTES VARCHAR2(100 BYTE),
DL_REQ_EMAIL VARCHAR2(60 BYTE),
DL_DATE_DUE DATE,
DL_PAST_DUE VARCHAR2(400 BYTE))
The goal is to populate the DL_PAST_DUE field with a 1 if the date is greater than 90 days else leave NULL. With the help of other more senior developers from OTN I have narrowed my logic to using a CASE Statement instead of a FUNCTION. Below is the projected CASE statement that I am trying to use:
DECLARE
v_copied_date DWG_LOG.DL_DATE_COPIED%TYPE;
v_curr_date SYSDATE;
BEGIN
SELECT DL_DATE_COPIED INTO v_copied_date
FROM DWG_LOG
CASE
WHEN (v_curr_date - v_copied_date) > 90 THEN
INSERT INTO DWG_LOG (DL_PAST_DUE) VALUES ('1');
ELSE
INSERT INTO DWG_LOG (DL_PAST_DUE) VALUES ('0');
END CASE;
END;
/
I am getting the following errors when I run this code in SQL*PLus:
ERROR at line 10:
ORA-06550: line 10, column 4:
PL/SQL: ORA-00933: SQL command not properly ended
ORA-06550: line 7, column 4:
PL/SQL: SQL Statement ignored
ORA-06550: line 12, column 4:
PLS-00103: Encountered the symbol "ELSE" when expecting one of the following:
begin case declare end exception exit for goto if loop mod
null pragma raise return select update while with
<an identifier> <a double-quoted delimite
ORA-06550: line 14, column 8:
PLS-00103: Encountered the symbol "CASE" when expecting one of the following:
if
PLEASE, point me in the right direction I know my syntax is off I just don't know where. I will continue to research and refine my script.
HUMBLY SUBMITTED
Markus