1

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>)
Rashmita Purkayastha
  • 457
  • 1
  • 8

1 Answers1

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")))) )
Mads Hansen
  • 63,927
  • 12
  • 112
  • 147
  • I have modified the select() query but then it gives me an error saying - [1.0-ml] SQL-NOCOLUMN: Column not found: Shipment_View.BookingCreateDt. I have declared the BookingCreateDt in Var3. – Rashmita Purkayastha Jul 04 '22 at 13:12
  • I think you may need to include `$Var3` in the preceding group-by and select, or it won't be available to be selected later on in that last `op:select()` – Mads Hansen Jul 04 '22 at 14:15
  • => op:group-by("BookingCreateDt",(op:count("Var2", $Var2),op:count("Var1", $Var1)))=>op:select(("BookingCreateDt",(op:as("multiply", op:divide(op:col("Var1"), op:col("Var2")))))) => op:select( ("BookingCreateDt",(op:as("percentage", op:multiply(100, op:col("multiply")))) )) – Rashmita Purkayastha Jul 04 '22 at 15:53
  • I have added the BookingCreateDt in GroupBy and all select statements and still it complains that the BookinhCreateDt column is not found. Any idea on how I can solve this please – Rashmita Purkayastha Jul 04 '22 at 15:54
  • Have you tried op:col(“BookingCrrateDt”) instead of “BookingCreateDt” – Mads Hansen Jul 04 '22 at 16:24
  • I have tried op:col and it still complains that the column is not found . – Rashmita Purkayastha Jul 06 '22 at 10:03