× 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.



SQL has no logical data type, so "IsInRange(date)" alone is not a valid predicate. A "WHERE IsInRange(date) = 1" would have a valid predicate on the WHERE clause, for having an expression on either side [one a function and the other a literal] of a comparison operator [the equal sign].

The revised WHERE clause mimics a BETWEEN predicate:

WHERE
date BETWEEN ( SELECT DATE('20' CONCAT YY CONCAT MM
CONCAT DD CONCAT '000000')
FROM LIB/FILE )
AND ( SELECT DATE('20' CONCAT EYY CONCAT EMM
CONCAT EDD CONCAT '000000')
FROM LIB/FILE )

A variation on the VIEW Duane suggested should work fine to allow for encapsulating both expressions to form date fields for use in the subselects:

CREATE VIEW LIB/FILE_DateRange (SDate, EDate) AS
( SELECT
DATE('20' CONCAT YY CONCAT MM CONCAT DD CONCAT '000000')
, DATE('20' CONCAT EYY CONCAT EMM CONCAT EDD CONCAT '000000')
FROM LIB/FILE AS F
)

The revised WHERE clause using the VIEW and a BETWEEN predicate:

WHERE
date BETWEEN ( SELECT SDate FROM LIB/FILE_DateRange )
AND ( SELECT EDate FROM LIB/FILE_DateRange )

The expressions could instead be performed using a UDF for each, or one UDF with an input parameter to request either the start or end of the range:

WHERE date BETWEEN StartDate() AND EndDate()

or

WHERE date BETWEEN DateRange('S') AND DateRange('E')

Regards, Chuck

On 2/9/11 2:24 PM, Mike Wills wrote:
I might be able to work with this. But not quite where I was going
with this either. What I wrote before was the old query I had. Now
I need it to look more like:

where
date>= (SELECT DATE(CONCAT('20', CONCAT(YY, CONCAT('-'
, CONCAT( MM, (CONCAT('-', DD))))))) FROM LIB/FILE')
and
date<= (SELECT DATE(CONCAT('20', CONCAT(EYY, CONCAT('-'
, CONCAT(EMM, (CONCAT('-', EDD))))))) FROM LIB/FILE')

I was hoping to return a single value so I don't clutter my
statementswith all of that junk.

On Wed, Feb 9, 2011 at 4:06 PM, Christen, Duane wrote:

If the table where your YY MM DD date is stored contains a single
record, as your sql implies, then I would create a view:

Create View PeriodOpenDate
SELECT DATE(CONCAT('20', CONCAT(YY, CONCAT('-', CONCAT( MM,
(CONCAT('-', DD))))))) as openDate FROM LIB/FILE;

Mike Wills on February 09, 2011 3:39 PM wrote:

I am working on some SQL statements for an ASP.NET application.
One of the things that is required is to only display
information in an open period. The period is updated
automatically by the vendor software as the previous period is
closed. So I finding myself doing a bunch of sub selects like:

where
date>= (SELECT DATE(CONCAT('20', CONCAT(YY, CONCAT('-'
, CONCAT( MM, (CONCAT('-', DD))))))) FROM LIB/FILE')

Yes, each portion of the date is in separate fields.

Would making this query a function make the query more
efficient? I have never created a function before how would I
do that? My thought is having something like:

IsInRange(date)

So I can do "WHERE IsInRange(date)".

Or is there a better way?


As an Amazon Associate we earn from qualifying purchases.

This thread ...

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.