× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



why not simply define the parm as a nullible date?

Then all you need is
WHERE inEffDate is not NULL and inEffDate = a.effDate

Charles


On Wed, Sep 5, 2018 at 10:22 AM Steve Richter <stephenrichter@xxxxxxxxx>
wrote:

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


As an Amazon Associate we earn from qualifying purchases.

This thread ...

Follow-Ups:
Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.