jknj72
asked on
Oracle syntax
I cant figure out what is wrong with this procedure???
CREATE OR REPLACE PROCEDURE ANET.SP_TEST_WARNING
IS
BEGIN
DECLARE
nWarning Number;
nViolation Number;
BEGIN
Select (SELECT TO_NUMBER(PROPERTY_VALUE) FROM OMNI_PROPERTIES WHERE PROPERTY_NAME='TPSA_BLANK_ WARN_DAYS' ) INTO nWarning 'Warnings',
(SELECT TO_NUMBER(PROPERTY_VALUE) FROM OMNI_PROPERTIES WHERE PROPERTY_NAME='TPSA_MUST_R ECONCILE_D AYS_BLANK' ) INTO nViolation 'Violation',
CREATION_DATE, SYSDATE
FROM TPSA_MAIN;
END;
EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
WHEN OTHERS THEN
-- Consider logging the error and then re-raise
RAISE;
END SP_TEST_WARNING;
/
I get this error message
[Warning] ORA-24344: success with compilation error
11/126 PL/SQL: ORA-00923: FROM keyword not found where expected
11/5 PL/SQL: SQL Statement ignored
(1: 0): Warning: compiled but with compilation errors
CREATE OR REPLACE PROCEDURE ANET.SP_TEST_WARNING
IS
BEGIN
DECLARE
nWarning Number;
nViolation Number;
BEGIN
Select (SELECT TO_NUMBER(PROPERTY_VALUE) FROM OMNI_PROPERTIES WHERE PROPERTY_NAME='TPSA_BLANK_
(SELECT TO_NUMBER(PROPERTY_VALUE) FROM OMNI_PROPERTIES WHERE PROPERTY_NAME='TPSA_MUST_R
CREATION_DATE, SYSDATE
FROM TPSA_MAIN;
END;
EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
WHEN OTHERS THEN
-- Consider logging the error and then re-raise
RAISE;
END SP_TEST_WARNING;
/
I get this error message
[Warning] ORA-24344: success with compilation error
11/126 PL/SQL: ORA-00923: FROM keyword not found where expected
11/5 PL/SQL: SQL Statement ignored
(1: 0): Warning: compiled but with compilation errors
A number of issues -
declare is not used in a stored procedure and, even if it were, would come before the begin statement, so the beginning of your procedure should look something like
CREATE OR REPLACE PROCEDURE ANET.SP_TEST_WARNING
IS
nWarning Number;
nViolation Number;
BEGIN
In PL/SQL all selects must be selected into a variable and the syntax is
SELECT some column(s)
INTO some variable(s)
FROM some table(s)
WHERE some condition(s)
Additionally, the select must only return one value.
Your select statement is also selecting a creation_date and sysdate, which would also need to be inserted into some variable.
I don't know what it is that you want your procedure to accomplish, since there doesn't seem to appear there is any data manipulation. Perhaps you can provide a more detailed description of what you want from the procedure with some sample data and expected results.
declare is not used in a stored procedure and, even if it were, would come before the begin statement, so the beginning of your procedure should look something like
CREATE OR REPLACE PROCEDURE ANET.SP_TEST_WARNING
IS
nWarning Number;
nViolation Number;
BEGIN
In PL/SQL all selects must be selected into a variable and the syntax is
SELECT some column(s)
INTO some variable(s)
FROM some table(s)
WHERE some condition(s)
Additionally, the select must only return one value.
Your select statement is also selecting a creation_date and sysdate, which would also need to be inserted into some variable.
I don't know what it is that you want your procedure to accomplish, since there doesn't seem to appear there is any data manipulation. Perhaps you can provide a more detailed description of what you want from the procedure with some sample data and expected results.
ASKER
Heres what Im trying to do
I need to write a query that has to check to see if there should be a violation or warning being reutrned in a procedure
Here what I have. I have a program that creates permits and when a permit is created you have to reconcile that permit before you receive a violation email. Also, you will be sent a warning email before you violate. There is a table that holds the numbers that are set for a violation and a number for a warning..
So if warnings are set to 3 and a violation is set to 30 the logic would be
IF SYSDATE > (CREATE_DATE + Violation(30)) send out a violation
IF SYSDATE BETWEEN (CREATE_DATE + (Violation(30) - Warning(3)) AND (CREATE_DATE + Violation(30)) then send out warnings
Can you help me write this in a view. The view would return all the permits that have Warnings and or Violations
I need to write a query that has to check to see if there should be a violation or warning being reutrned in a procedure
Here what I have. I have a program that creates permits and when a permit is created you have to reconcile that permit before you receive a violation email. Also, you will be sent a warning email before you violate. There is a table that holds the numbers that are set for a violation and a number for a warning..
So if warnings are set to 3 and a violation is set to 30 the logic would be
IF SYSDATE > (CREATE_DATE + Violation(30)) send out a violation
IF SYSDATE BETWEEN (CREATE_DATE + (Violation(30) - Warning(3)) AND (CREATE_DATE + Violation(30)) then send out warnings
Can you help me write this in a view. The view would return all the permits that have Warnings and or Violations
Can you provide a description of the omni_properties and tpsa_main tables?
ASKER
I was able to do it with this
SELECT TRUNC(CREATION_DATE) as CREATION_DATE, EMPLOYEE_NUMBER
FROM TPSA_MAIN WHERE
TRUNC(SYSDATE) BETWEEN (TRUNC(CREATION_DATE) + (SELECT TO_NUMBER(PROPERTY_VALUE) FROM OMNI_PROPERTIES WHERE PROPERTY_NAME='TPSA_MUST_R ECONCILE_D AYS_BLANK' ) - (SELECT TO_NUMBER(PROPERTY_VALUE) FROM OMNI_PROPERTIES WHERE PROPERTY_NAME='TPSA_BLANK_ WARN_DAYS' )) --typically 30-3
AND (TRUNC(CREATION_DATE) + (SELECT TO_NUMBER(PROPERTY_VALUE) FROM OMNI_PROPERTIES WHERE PROPERTY_NAME='TPSA_MUST_R ECONCILE_D AYS_BLANK' ))--typica lly 30
AND STATUS IN('INCOMPLETE', 'BLANK');
I was trying to put them in variables for readability and ease of use but I guess this will work fine unless you have any suggestions?
Thanks
SELECT TRUNC(CREATION_DATE) as CREATION_DATE, EMPLOYEE_NUMBER
FROM TPSA_MAIN WHERE
TRUNC(SYSDATE) BETWEEN (TRUNC(CREATION_DATE) + (SELECT TO_NUMBER(PROPERTY_VALUE) FROM OMNI_PROPERTIES WHERE PROPERTY_NAME='TPSA_MUST_R
AND (TRUNC(CREATION_DATE) + (SELECT TO_NUMBER(PROPERTY_VALUE) FROM OMNI_PROPERTIES WHERE PROPERTY_NAME='TPSA_MUST_R
AND STATUS IN('INCOMPLETE', 'BLANK');
I was trying to put them in variables for readability and ease of use but I guess this will work fine unless you have any suggestions?
Thanks
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
that will do it
nWarning Number;
nViolation Number;
dCreation_date;
BEGIN
Select (SELECT TO_NUMBER(PROPERTY_VALUE) FROM OMNI_PROPERTIES WHERE PROPERTY_NAME='TPSA_BLANK_
(SELECT TO_NUMBER(PROPERTY_VALUE) FROM OMNI_PROPERTIES WHERE PROPERTY_NAME='TPSA_MUST_R
CREATION_DATE --, SYSDATE
INTO nWarning , nViolation , dCreation_date
FROM TPSA_MAIN;
END;
-- but this will only get you creation date from tpsa_main , the other values are fixed