-
ORA-1847 day of month must be between 1 & last day of month
Hello
Im trying to debug a troublesome trigger.
In my when clause im using this type of logic....
nvl(old.cust_flag, 'z') <> nvl(new.cust_flag,'z') or
nvl(old.last_updated_by, 99999) <> nvl(new.last_updated_by,99999) or
etc
but im having problems with the date format by this i mean
nvl(old.last_update,'9999') <> nvl(new.last_update,'9999')
Table Struct
cust_flag = VARCHAR150
last_updated_by = Number 15
last_update = Date
The problem is prob the format (ie '9999) above) but im not too sure what to put in when referencing a date???
I really hope someone can help...
Thanks in Advacne to anyone who can...
Carpe Diem
-
Hello,
If the second argument does not the same datatype as the first argument, it must be converted. Obviously, '9999' is not a valid date.
I believe you can use sysdate here:
nvl(old.last_update, sysdate+1) <> nvl(new.last_update, sysdate+1)
... quam minimum credula postero.
Ales The whole difference between a little boy and an adult man is the price of toys
-
Originally posted by ales
I believe you can use sysdate here:
nvl(old.last_update, sysdate+1) <> nvl(new.last_update, sysdate+1)
Since the original poster stated that his code is PL/SQL (trigger), I wouldn't use sysdate as suggested in the above code! If both :NEW.last_update and :OLD.last_update are NULL, then the above condition will most of the time result in FALSE, but sumetimes it will result in TRUE!!!!!
Better use some constant date instead of SYSDATE, like:
NVL(:OLD.last_update, TO_DATE(1,'J')) <> NVL(:NEW.last_update, TO_DATE(1,'J'))
Last edited by jmodic; 01-10-2003 at 04:05 PM.
Jurij Modic
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
-
Brabas wrote that he's using that in the WHEN clause of the trigger, not in the trigger body. The WHEN clause is a boolean SQL expression, not a PL/SQL code. The SYSDATE function is evaluated only once in a SQL expression, thus it's safe to use it in the manner I suggested.
Please correct me if I'm wrong.
Ales The whole difference between a little boy and an adult man is the price of toys
-
Yep, you are righ! I missed that "small" detail about code being part of the WHEN trigger clause (that's why I used colons before NEW and OLD).
In case of WHEN condition you are correct - sysdate is evaluated only once per expression, so your code is perfectly OK.
Jurij Modic
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|