| End date greater than start date, but values can be null - how to code this [message #489089] |
Thu, 13 January 2011 04:10  |
 |
jts25
Messages: 8
Registered: January 2011
|
Junior Member |
|
|
Hi, I'm still trying to get my head around these validations. On my form based on a project, the user does not have to specify any of the dates (as they may not be known at the time)
Is there a way to check the dates are valid only if the field has data in?
I've tried the following under PL/SQL error:
begin
if :P6_ACTUAL_END_DATE is not null and :P6_START_DATE is not null then
if TO_DATE(:P6_ACTUAL_END_DATE) < TO_DATE(:P6_START_DATE)
end if;
end if;
end;
This doesn't work as just displays the preset error message, so any help would be much appreciated.
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: End date greater than start date, but values can be null - how to code this [message #489130 is a reply to message #489110] |
Thu, 13 January 2011 07:23  |
c_stenersen
Messages: 255
Registered: August 2007
|
Senior Member |
|
|
If I create two items and a validation like this with the type PL/SQL error the validation will fail. Your code isn't valid. For this type of validation a run without an error being raised will mean that it considers the values to be valid. If there are any code errors or exceptions the validation will show your defined error message. From the (help) text you'll get when clicking the link on the label of the "type" field in your validation properties:
Quote:PL/SQL Error: Passes if the PL/SQL runs without generating an error.
begin
if :P6_ACTUAL_END_DATE is not null and :P6_START_DATE is not null then
if TO_DATE(:P6_ACTUAL_END_DATE) < TO_DATE(:P6_START_DATE)
--You don't have a "then" here, so the code has an error.
--Also the if-sentence is empty and will then give an exception
end if;
end if;
end;
Use a "PL/SQL expression" validation instead. Then you can just write your condition directly. No need for any if statements or anything like that. When the expression given validates to true no error is raised.
Quote:PL/SQL Expression: Passes if the PL/SQL expression evaluates to TRUE.
check if either both items are null or if one is smaller than the other. (NB, this also means that if only one of the fields are null and the other one specified the validation will fail.)
You should also use a to_date on them. In ApEx date pickers are simply text fields with an extra little script to help the user put in the date by using a popup calendar. The users could even type in whatever they want (i.e. not a valid date), so you might want to check the format of them as well.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
