[mapserver-users] Oracle SYSDATE in EXPRESSION

[Dan Little]

Again, you could do that with the stored procedure or in SQL with a view.
And have a column that returns the logical style class. For example,

CREATE VIEW
SELECT
  CASE WHEN permit_date < SYSDATE - 365 * 5 THEN 'old_permit'
   ELSE 'active_permit' END as permit_class,
   ... other columns ...
FROM permits;


Then in the mapbook...

CLASS
 EXPRESSION "[permit_class] == 'old_permit'"
 STYLE
   [.... style stuff ....]
 END
END

On Tue, Mar 29, 2016 at 11:20 AM, Basques, Bob (CI-StPaul) <
bob.basques at ci.stpaul.mn.us> wrote:

> Dan,
>
> That would work, but I was trying to color a 5 year old class one color,
> and the rest of them another, and do it all in one SQL call (single layer)
>
> I got it to work in the DATA connection, but was trying to do the STYLE
> based query in the CLASS block.
>
> Seems like it would be doable, but I’m not having any luck yet.  I’m
> guessing that the SYSDATE is not getting passed in correctly along with the
> INDATE value, at least based on what I see when I intentionally break the
> SQL to see the error.
>
> Also, there are only 1700 records to work with in there right now, quick
> return no matter what.
>
> bobb
>
>
> On Mar 29, 2016, at 11:10 AM, Dan Little <theduckylittle at gmail.com> wrote:
>
> You're probably better off with a view or querying against a stored
> procedure. There are a few advantages:
> 1. With a view you can create multiple arbitrary views.
> 2. It keeps the syntax of the mapfile cleaner.
> 3. A view is easy to change to a materialized view in Oracle.  That has a
> pile of performance benefits.
> 4. A stored procedure can be used to normalize the data better between
> Oracle and mapserver.
>
>
> On Tue, Mar 29, 2016 at 10:53 AM, Basques, Bob (CI-StPaul) <
> bob.basques at ci.stpaul.mn.us> wrote:
>
>> All Oracle Guru’s,
>>
>> How do I handle an expression for doing a timed query in Oracle:
>>
>> CLASS
>> *EXPRESSION ('[indate] > SYSDATE - 1825')*
>> COLOR 255 255 254
>> OUTLINECOLOR 0 0 0
>>       SIZE 10
>>       SYMBOL 'circle'
>> NAME "PW Sidewalk Permits"
>>     END
>>
>> I want to get all the Permits up to 5 years old to plot.  What should the
>> EXPRESSION line look like above.  I’ve tried all sort of different
>> combinations of quoting, etc.  I either get everything, or nothing.
>>
>> Thanks
>>
>> bobb
>>
>>
>>
>> _______________________________________________
>> mapserver-users mailing list
>> mapserver-users at lists.osgeo.org
>> http://lists.osgeo.org/mailman/listinfo/mapserver-users
>>
>
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/mapserver-users/attachments/20160329/bff37dc8/attachment.html>