Re: SQL error - syntax of date value not valid

passing an optional char parm to an SQL procedure. Default value is ' '.
I use the optional parm in the WHERE clause of a SELECT statement.

WHERE inEffDate = ' ' or date(inEffDate) = a.effDate

I am expecting SQL to first test if the parameter is blank. If it is,
satisfy the WHERE clause and not run the data function. But the code is
bombing with the "syntax of date value not valid" error.

Can I count on SQL to not evaluate the OR part of the WHERE clause when the
first condition evaluates to true?

here is the SQL statement. This fails with the syntax of date value error.

select COUNT(*)
INTO VCOUNT
from prugadm
a
where inEffDate = ' ' or date(inEffDate) = a.effDate ;

I rewrote it to make sure the input parameter was blanks, and it is:

select COUNT(*), case when inEffDate = ' ' then 'X'
else 'Y' end result
INTO VCOUNT, vResult
from prugadm a
where inEffDate = ' ' or
date(case when inEffDate = ' ' then '2018-04-01'
else inEffDate end) = a.effdate
fetch first row only ;

The complete SQL procedure:

CREATE or replace PROCEDURE prugadm_test(
in inEffDate char(10) default ' '
)
LANGUAGE SQL
BEGIN
declare vSqlCode decimal(5,0) ;
declare sqlCode int DEFAULT 0 ;

declare vSqlState char(5) ;
declare vErrText char(256) ;
declare sqlState char(5) ;
declare vCount int default 0 ;

DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
begin
SET vSqlCode = SQLCODE ;
SET vSqlState = SQLstate ;
get diagnostics exception 1 vErrText = message_text ;
end ;

select COUNT(*)
INTO VCOUNT
from prugadm
a
where inEffDate = ' ' or date(inEffDate) = a.effDate ;


END
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: http://amzn.to/2dEadiD