CODEWITHSUNDEEP
marklogicmarklogic-8marklogic-9
Ryan
Ryan

Reputation: 175

How to implement the SQL query in MarkLogic javascript?

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?

Upvotes: 0

Views: 71

Answers (1)

ehennum
ehennum

Reputation: 7335

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,

Upvotes: 1

Related Questions