Can we use DATEADD function in Optic API query in MarkLogic

Question

I am trying to get count of record from a view for a date range example count of a column value for last 6 months on some condition. Can we use sql function DATEADD to get the daterange in the query.

Here is my Optic API query where I tried to use the DATEADD which gives me error:

xquery version "1.0-ml"; 
import module namespace op="http://marklogic.com/optic" at "/MarkLogic/optic.xqy"; 

op:from-view("GTM2_Shipment", "Shipment_View", "") 
=> op:where( op:and(( op:eq(op:col('transMode'), 'Rail') , op:sql-condition("dateadd(month,-6,'BookingCreateDt')") )) ) 
=> op:group-by((), op:count(op:col("Rail_AncillaryCost"), op:col("Ancillary_QuotePrice"))) 
=> op:result()

Error:

OPTIC-INVALARGS: (err:FOER0000) Invalid arguments: map argument is not an expression

Please let me know what is the issue.

Answer 1

It is interesting to note that you can access all sorts of items during the optic runtime. xQuery libraries have been mapped through to be used during plan execution.

See here: XQuery Libraries Required for Expression Functions are your friend

For the example below, I mixed in a min and max date in 2 different ways to show usage.

xquery version "1.0-ml"; 


import module namespace op="http://marklogic.com/optic"
     at "/MarkLogic/optic.xqy";
import module namespace ofn="http://marklogic.com/optic/expression/fn"
    at "/MarkLogic/optic/optic-fn.xqy";
import module namespace osql="http://marklogic.com/optic/expression/sql"
    at "/MarkLogic/optic/optic-sql.xqy";     

let $three-years-ago  := xs:date(fn:current-date()-xs:yearMonthDuration("P3Y"))
let $plan := op:from-literals(
(
  map:new()=>map:with("name","Freddy Llama")=>map:with("eyeColor", "purple")=>map:with("dateOfBirth", xs:date("2014-12-10")),
  map:new()=>map:with("name", "Sandy Llama")=>map:with("eyeColor", "green")=>map:with("dateOfBirth", xs:date("2010-04-10")),
  map:new()=>map:with("name", "Didi Llama")=>map:with("eyeColor", "black")=>map:with("dateOfBirth", xs:date("2020-06-12"))
  
),
"llamaData")

=>op:where(op:lt(op:view-col("llamaData", "dateOfBirth"), $three-years-ago))
=>op:where(op:gt(op:view-col("llamaData", "dateOfBirth"), osql:dateadd('year',-9, ofn:current-date())))
return $plan=>op:result()