SELECT AND INSERT STATMENT PROBLEM 1

[Crystalboy1]

HI I AM USING ORACLE 10G. I AM TRYING TO RUN THIS SCRIPT IN A STORE PROCEDURE BUT IT GIVING ME ERROR. CAN ANYONE FIX IT FOR ME PLEASE ??

IF (SELECT DATEFIELD FROM TABLE)<>TODAYDATE THEN
INSERT INTO TABLE(DATEFIELD, FIELD2, FIELD3) VALUES(20/12/2008,25,30)
ELSE
END IF;

 

[SantaMufasa]

Crystalboy,

There are multiple issues with your code:[ul][li]Oracle (PL/SQL) does not support the construct, "IF <SELECT...> <conditional expression>...". You can work around this issue with this construct:

BEGIN
    <PL/SQL-valid SELECT...INTO <declared variable>...>;
    IF <declared variable> <conditional expression>...

[/li][li]The table name, "TABLE" is illegal since that is a reserved Oracle keyword.[/li][li]Within PL/SQL, an independent SELECT statement must return, into a declared memory variable, a result set of precisely one instance (i.e. row), else an exception (i.e., fatal error) occurs. The workaround, in such case, is to either formulate a WHERE clause for the SELECT statement that guarantees precisely one row returns, or to formulate a SELECT that returns a single aggregate-function result (e.g., "SELECT COUNT(*)..." or "SELECT MIN(<expression>)...", et cetera).[/li][li]Your INSERT statement fails because you ask Oracle to place the result of the mathematical ("divide by") expression "20/12/20008" (.000830013) into a DATE column. You should ("must" usually) use an Oracle TO_DATE function to translate a character literal into a valid DATE value.[/li][li]You must terminate your INSERT statement with a semicolon ([/li][li]Your code, "ELSE END IF;" is syntactically illegal.[/li][li]Your code logic is suspect...Why, if your DATEFIELD value from your table is not equal to today's date, would you want a literal date of "December 20, 2008" placed into your table?[/li][/ul]Obviously, the contrivance of your sample code generated some of your problems, but since you did not include the error message that Oracle (PL/SQL) generated, we cannot readily tell which of your coding problems generated the error that you are asking us to help resolve.


I have taken the liberty to create working code that approximates what you are syntactically asking about and works around the syntax/run-time errors that your code generates:

SQL> select * from tabl;

DATEFIELD     FILED2     FIELD3
--------- ---------- ----------
06-JAN-09         20         40

declare
   today_count number;
begin
    select count(*) into today_count from tabl where trunc(datefield) = trunc(sysdate);
    if today_count = 0 then
        insert into tabl values (to_date('20/12/2008','dd/mm/yyyy'),25,30);
   end if;
end;
/

SQL> select * from tabl;

DATEFIELD     FILED2     FIELD3
--------- ---------- ----------
06-JAN-09         20         40
20-DEC-08         25         30

Let us know if this (somehow) resolves your questions.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[Crystalboy1]

Thanks a lot Mufasa.