Error in Optic query in MarkLogic - Invalid coercion: map:map

Question

I am getting an invalid map error when trying to get the percentage of a record based on a certain condition to the total count of another record. Can you please help to identify the issue here in my code.

The below code is trying to get the percentage of the count of all 6 months old AncillaryQuote price to the total no of Shipments References when the Ancillary quote price is greater than 0 . I am taking Booking Date to calculate the 6 months old date range.

Here is my optic query which I am trying -

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 $Var1 := op:view-col("Shipment_View", "Ancillary_QuotePrice")
let $Var2 := op:view-col("Shipment_View", "Shipment_Ref")
let $Var3 := op:view-col("Shipment_View", "BookingCreateDt")
return $view
=> op:group-by("transMode",(op:count("Var2", $Var2),op:count("Var1", $Var1)))
=> op:where(op:and((
                   op:gt($Var1, 0),op:gt(ofn:format-dateTime($Var3, '[Y0001]-[M01]-[D01]'),osql:dateadd('month',-6, ofn:format-dateTime(fn:current-dateTime(),'[Y0001]-[M01]-[D01]')))

                   ))
                   )
=>op:select(op:as("multiply", op:divide(op:col("Var1"), op:col("Var2"))))
=>op:select($Var3,op:as("percentage", op:multiply(100, op:col("multiply"))))
=> op:result()

Here is the error I am getting -

[1.0-ml] XDMP-AS: (err:XPTY0004) $qualifier as xs:string? -- Invalid coercion: map:map(<map:map xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" .../>) as xs:string
Stack Trace
In /MarkLogic/optic.xqy on line 685
In xdmp:eval("xquery version &quot;1.0-ml&quot;;&#10;import module namespace o...", (), <options xmlns="xdmp:eval"><database>16857865358322067141</database>...</options>)
In xdmp:eval("xquery version &quot;1.0-ml&quot;;&#10;import module namespace o...", (), <options xmlns="xdmp:eval"><database>16857865358322067141</database>...</options>)

Answer 1

The error is telling you that the $qualifier parameter is a map and cannot be converted to xs:string.

If you look at the signature of the op:select function:

op:select(
   $plan as map:map,
   $columns as columnIdentifier*,
   [$qualifier as xs:string?]
) as map:map

You will see that $qualifier is the third parameter.

When using the =>, the plan is being set as the first parameter for you. The columns for the op:select() are the second parameter. However, you need to provide those columns as a sequence (wrap with ()). Otherwise, it appears that you are specifying $Var3 as the columns to select, and then the percentage column as the $qualifier.

Change:

=> op:select($Var3, op:as("percentage", op:multiply(100, op:col("multiply"))))

to:

=> op:select( ($Var3,op:as("percentage", op:multiply(100, op:col("multiply")))) )