xquerymarklogicmarklogic-optic-api

Declare column in variable is not working in Optic query MarkLogic

I am trying to declare the column in a variable and want to call that variable inside my program. Here is my optic query code which returns me - Column not found. Please help to understand the problem in my query.

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";  
declare option xdmp:mapping "false";
let $view := op:from-view("GTM2_Shipment", "Shipment_View")
let $count := op:view-col("Shipment_View", "Ancillary_QuotePrice")
return $view
=> op:where(op:and((
                   op:gt(op:col("$count"), 0)

                   ))
                   )
=> op:group-by("transMode", op:count("QuotePrice", "$count"))
=> op:result()

here is the result I get -

[1.0-ml] SQL-NOCOLUMN: Column not found: $count
Stack Trace
At line 1 column 2:
In sem:execute(plan:sparql(" * { sample(transMode) AS transMode) count($count) AS QuotePrice) { FILTER ($count gt xs:integer("0") and fn:format-dateTime(xs:dateTime(BookingCreateDt), xs:string("[Y0001]-[M01]-[D01]")) gt sql:dateadd(xs:string("month"), xs:int("-6"), fn:format-dateTime(xs:dateTime("2022-06-30T10:42:50.2654743+01:00"), xs:string("[Y0001]-[M01]-[D01]")))) } GROUP BY transMode }"), (), ()

I have also tried to declare the column like this- let $count := op:col("Ancillary_QuotePrice") And it doesnot recognize the column from the view and gives the same error.

Solution

  • Once $count is defined as an op:view-col(), then just use it as a variable. See suggestions below: