How to implement the SQL query in MarkLogic javascript?

Question

I have an SQL query that I want to implement in Javascript query type in MarkLogic. The goal is to return all the URIs of the query result.

Below is the SQL query I want to convert:

SELECT * FROM DOCUMENT_TABLE
WHERE WADCTO IN ('WM', 'WO')
AND (WASRST NOT IN ('02', 'M', 'M9')) AND (WASTRX = 0)

Here is my current Javascript query that I tested in query console but is not returning the same number of rows like the SQL query:

cts.uris(null, null, 
  cts.andQuery([
    cts.collectionQuery("JDEdwards"), 
    cts.collectionQuery("WorkOrder_Document"),
    cts.andQuery([
      cts.orQuery([
        cts.elementRangeQuery(fn.QName("http://www.example.com/ads/JDEdwards/document", "WADCTO"),"=","WO"),
        cts.elementRangeQuery(fn.QName("http://www.example.com/ads/JDEdwards/document", "WADCTO"),"=","WM")
      ]),
      cts.andQuery([
        cts.elementRangeQuery(fn.QName("http://www.example.com/ads/JDEdwards/document", "WASRST"),"!=","02"),
        cts.elementRangeQuery(fn.QName("http://www.example.com/ads/JDEdwards/document", "WASRST"),"!=","M"),
        cts.elementRangeQuery(fn.QName("http://www.example.com/ads/JDEdwards/document", "WASRST"),"!=","09")
      ]),
      cts.elementRangeQuery(fn.QName("http://www.example.com/ads/JDEdwards/document", "WASTRX"),"=","0")
    ])
  ])
);

Note that I have setup range index for elements WADCTO, WASRST and WASTRX.

Is there anything missing or wrong with the logic of my code?

ehennum · Accepted Answer · 2018-09-24 19:21:40Z

A range query can specify an OR-related list of values. Also, the cts.notQuery() can negate a query. Possibly something similar to the following sketch might get closer.

cts.uris(null, null, 
  cts.andQuery([
    cts.collectionQuery("JDEdwards"),
    cts.collectionQuery("WorkOrder_Document"),
    cts.elementRangeQuery(fn.QName("...", "WADCTO"),"=", ["WO", "WM"]),
    cts.notQuery(
      cts.elementRangeQuery(fn.QName("...", "WASRST"),"=", ["02", "M", "09"])
      ),
    cts.elementRangeQuery(fn.QName("...", "WASTRX"),"=", "0")
    ]);

Things to check include

whether the target documents are in both collections
whether the namespaces are correct for the elements
whether the range indexes have the string data type (WASTRX was a number in the SQL version of the query)

If all else fails, try removing query clauses until the query works to find the problematic query clause.

Hoping that helps,

Collectives™ on Stack Overflow

How to implement the SQL query in MarkLogic javascript?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related