When trying to run a custom CABI report that includes CA Service Desk Manager (SDM) SREL fields and the BETWEEN date filter, an error similar to the following is received:
A database error occurred. The database error text is: [DataDirect][ODBC OpenAccess SDK driver][OpenAccess SDK SQL Engine]AHD12002:Error in QueryHandler ServiceDesk-557915410-2 in method fetch_response: AHD03053:Bad where clause: Parse error at : "( ( priority IN (1, 2) AND open_date BETWEEN 'YYYY-MM-DD HH:MM:SS am' AND 'YYYY-MM-DD HH:MM:SS am' ) ) AND ( type = 'I')"(syntax error). (WIS 10901)
The error is the result of a CA SDM DOMSRVR limitation. Basically, the use of CA SDM SREL fields and the CABI BETWEEN date filter is not supported.
This is not necessarily a CABI issue, but rather a CA SDM DOMSRVR limitation. Since CA SDM and CABI uses the SDM object layer for report generation, the DOMSRVR is involved in the interpretation of CA SDM fields and CABI filters/functions.
If you enable trace logging on CA SDM sqlquery.c code, you will see the following error:
domsrvr:sa 5656 MILESTONE sqlquery.c 823 Submitting SQL query open_date BETWEEN 'YYYY-MM-DD HH:MM:SS am' AND 'YYYY-MM-DD HH:MM:SS am' to domsrvr insync_fetch: AHD12016:Attribute <SLREL attribute> is a local SREL
There is currently no solution to include the use of both a SLREL CA SDM field and the CABI BETWEEN filter. If you remove the CABI BETWEEN date filter, the CABI report will run fine. If you remove the CA SDM SREL attribute and leave the CABI BETWEEN date filter, the CABI report runs fine.