It gets me all the time... especially when the validation uses a NOT EXISTS -- too many double negatives ;-)
:P1_eff_end >= :P1_eff_start OR :P1_eff_end IS NULL
Assuming your P7_BILLING_START_DATE is a mandatory field for this to work,
select 1 from dual where case when :P7_BILLING_END_DATE is null then 1 When :P7_BILLING_END_DATE is not null and :P7_BILLING_START_DATE <= :P7_BILLING_END_DATE then 1 else 0 end = 1
Ewaver wrote:APEX item session state values are all of type <tt>VARCHAR2</tt>. This code:
I am not able to get either of these to work for some reason. Neither are catching the End Date < Start Date scenario.
is performing a string comparison of P7_FTE_BILLING_END and P7_FTE_BILLING_START. This will return TRUE if P7_FTE_BILLING_END = '31/01/2013' and P7_FTE_BILLING_START = '04/02/2013'. When referencing APEX session state values as dates (or numbers) always perform explicit date (and number) conversions:
BEGIN IF :P7_FTE_BILLING_END >= :P7_FTE_BILLING_START OR :P7_FTE_BILLING_END IS NULL THEN RETURN TRUE; ELSE RETURN FALSE; END IF; END;
I'm also with Scott in preferring PL/SQL Expression validations:
... if to_date(:P7_FTE_BILLING_END, 'DD/MM/YYYY') >= to_date(:P7_FTE_BILLING_START, 'DD/MM/YYYY') ... if to_number(v('P7_EXPENSES')) > 200 ...
to_date(:p7_fte_billing_end, 'DD/MM/YYYY') >= to_date(:p7_fte_billing_start, 'DD/MM/YYYY') or :p7_fte_billing_end is null
Damir Vadas wrote:Do you want to re-read the entire thread to see why—subjective opinions on clarity notwithstanding—the fact this doesn't work is a serious drawback? Hint: ORA-01722: invalid number.
Maybe this is more clear:
nvl(:P1_eff_end,(:P1_eff_start+1)) >= :P1_eff_start;
Damir Vadas wrote:Can you substantiate that statement? What about when you add the 3 additional function calls required to actually make your example work in APEX?
We'll opinion may vary OR parts in logical evaluation are always slower then function in kernel, like mine example do.