Can we use DATEADD function in Optic API query in MarkLogic

user_18123235 0
Viewed 44

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.

marklogic
2 Answers

The column reference for BookingCreateDt doesn't need to be quoted. Even though in SQL you don't quote the interval value month, apparently it does need quotes so that when the op:sql-condition expression is parsed, it knows that it is a string literal.

Change your expression from:

op:sql-condition("dateadd(month, -6, 'BookingCreateDt'))

to:

op:sql-condition("dateadd('month', -6, BookingCreateDt)
Mads_Hansen_144190

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()
user_32154850
Related