Re: To function or not to function, that is the question at hand

[CRPence <CRPbottle@xxxxxxxxx>]

  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?