Home » Developer & Programmer » Application Express, ORDS & MOD_PLSQL » End date greater than start date, but values can be null - how to code this
End date greater than start date, but values can be null - how to code this [message #489089] Thu, 13 January 2011 04:10 Go to next message
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 #489091 is a reply to message #489089] Thu, 13 January 2011 04:19 Go to previous messageGo to next message
cookiemonster
Messages: 13894
Registered: September 2008
Location: Rainy Manchester
Senior Member
What datatype are the two items?
What preset error message?
Re: End date greater than start date, but values can be null - how to code this [message #489093 is a reply to message #489091] Thu, 13 January 2011 04:25 Go to previous messageGo to next message
jts25
Messages: 8
Registered: January 2011
Junior Member
the dates are of datatype date, or in application express, date picker
When going through the stages of creating the validation, you specify the type of validation i.e. PL/SQL, then if its an expression or error, then on validation screen it asks for the validation code and error message, so that it's what I referred to as preset error message.
Re: End date greater than start date, but values can be null - how to code this [message #489094 is a reply to message #489093] Thu, 13 January 2011 04:29 Go to previous messageGo to next message
cookiemonster
Messages: 13894
Registered: September 2008
Location: Rainy Manchester
Senior Member
If they're dates why are you using to_date?
Re: End date greater than start date, but values can be null - how to code this [message #489095 is a reply to message #489094] Thu, 13 January 2011 04:31 Go to previous messageGo to next message
jts25
Messages: 8
Registered: January 2011
Junior Member
That's possibly where my beginner methods are coming in. When I'd searched on the net for possible ways, the to_date was used.

Is this not required?
Re: End date greater than start date, but values can be null - how to code this [message #489096 is a reply to message #489095] Thu, 13 January 2011 04:33 Go to previous messageGo to next message
cookiemonster
Messages: 13894
Registered: September 2008
Location: Rainy Manchester
Senior Member
to_date converts a string to a date. If your variables are date datatype what use is to_date?
Re: End date greater than start date, but values can be null - how to code this [message #489098 is a reply to message #489096] Thu, 13 January 2011 04:36 Go to previous messageGo to next message
jts25
Messages: 8
Registered: January 2011
Junior Member
Well now that I no that, it obviously isn't any use! Like I said, I'm fairly new to this so am bound to be prone to mistakes. That is why I put a help request on here so I could understand more from the experts.
Re: End date greater than start date, but values can be null - how to code this [message #489099 is a reply to message #489098] Thu, 13 January 2011 04:38 Go to previous messageGo to next message
cookiemonster
Messages: 13894
Registered: September 2008
Location: Rainy Manchester
Senior Member
So has that fixed the problem?
Re: End date greater than start date, but values can be null - how to code this [message #489101 is a reply to message #489099] Thu, 13 January 2011 04:49 Go to previous messageGo to next message
jts25
Messages: 8
Registered: January 2011
Junior Member
I took out reference to to_date, so code is now:

if :P6_ACTUAL_END_DATE is not null and :P6_START_DATE is not null then
if :P6_ACTUAL_END_DATE < :P6_START_DATE

end if;
end if;

But error message appears if the actual end date is less or greater than. Also if the field is null
Re: End date greater than start date, but values can be null - how to code this [message #489103 is a reply to message #489101] Thu, 13 January 2011 04:51 Go to previous messageGo to next message
cookiemonster
Messages: 13894
Registered: September 2008
Location: Rainy Manchester
Senior Member
Then the above code isn't being run. It's too simple to go wrong so the problem is something else entirely.
Re: End date greater than start date, but values can be null - how to code this [message #489106 is a reply to message #489103] Thu, 13 January 2011 04:54 Go to previous messageGo to next message
jts25
Messages: 8
Registered: January 2011
Junior Member
If that's the case, have you come across this kind of validation before? If so, do you have any help on what the problem might be?
Re: End date greater than start date, but values can be null - how to code this [message #489107 is a reply to message #489106] Thu, 13 January 2011 04:55 Go to previous messageGo to next message
cookiemonster
Messages: 13894
Registered: September 2008
Location: Rainy Manchester
Senior Member
I'm not even sure what tool you're using - doesn't appear to be oracle form builder - is it apex?
Re: End date greater than start date, but values can be null - how to code this [message #489108 is a reply to message #489107] Thu, 13 January 2011 04:56 Go to previous messageGo to next message
jts25
Messages: 8
Registered: January 2011
Junior Member
Yeah it's apex, sorry think I forgot to mention that at the beginning
Re: End date greater than start date, but values can be null - how to code this [message #489109 is a reply to message #489108] Thu, 13 January 2011 04:59 Go to previous messageGo to next message
cookiemonster
Messages: 13894
Registered: September 2008
Location: Rainy Manchester
Senior Member
Then we'll shunt this to the apex forum and hopefully someone who knows how it works will be able to sort out your problem.
Re: End date greater than start date, but values can be null - how to code this [message #489110 is a reply to message #489109] Thu, 13 January 2011 05:00 Go to previous messageGo to next message
jts25
Messages: 8
Registered: January 2011
Junior Member
Thanks, apologies for putting it in wrong forum
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 Go to previous message
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.
Previous Topic: Javascript not working for Firefox
Next Topic: USER vs APEX_PUBLIC_USER
Goto Forum:
  


Current Time: Sun Aug 09 13:06:51 CDT 2020