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?
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
If all else fails, try removing query clauses until the query works to find the problematic query clause.
Hoping that helps,