Getting error while using optic API to get count of certain column data for a data range from TDE views in MarkLogic

I am new to MarkLogic and need help in a issue debugging. I have created a view with MarkLogic TDE and would like to get the count of records for some date range from that view.

I have checked the option of trying optic API in this.

Tried this XQuery which gives me an error saying the BookingCreateDt is not available in the view, but it actually exists in my template view.

Can you please spare some time to check the problem.

Query :

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:select(("Ancillary_QuotePrice", "transMode"))
  =>op:where(
   
      op:and((
        op:eq(op:col('transMode'), 'Sea')
         ,
        op:gt(op:col('BookingCreateDt'), '2022-03-21T15:03:20')
      ))
      )
=>op:limit(1)
=>op:result()

And it returns this error -

[1.0-ml] SQL-NOCOLUMN: Column not found: BookingCreateDt

Solution

Your op.select() limits the rest of the results to just the 2 columns you mention("Ancillary_QuotePrice", "transMode").

Therefore, the columns you are testing against do not exist.

try:

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'), 'Sea')
         ,
        op:gt(op:col('BookingCreateDt'), '2022-03-21T15:03:20')
      ))
      )
=> op:select(("Ancillary_QuotePrice", "transMode"))
=> op:limit(1)
=> op:result()

However, your code just gives you one result in database order. The results have nothing to do with counting records. Perhaps that is what you want - but it does not match the title. If you are looking for a count:

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'), 'Sea')
         ,
        op:gt(op:col('BookingCreateDt'), '2022-03-21T15:03:20')
      ))
      )
=> op:group-by((), op:count(op:col("theCount"), op:col("transMode")))
=> op:result()

This would result in one record with a column called theCount holding the count of records matching your where clause. I used transMode since I know it existed in all records based on the where clause. you could use any column.