07-10-2020 01:42 PM
We are on 18.0.1.49.
I have created a custom SQL data provider that works fine until I try to use the ##OB_OPTIONSELECT_ALL macro. Once I implement the macro, I keep getting "Incorrect syntax near the keyword 'OR'" in diagnostics console.
I feel like I've implemented exactly as I've seen other examples and in the MRG:
Here is the data provider SQL:
SELECT hsi.rimlog.usernum
,hsi.useraccount.username
,hsi.rimlog.logdate
,hsi.rimlog.actionnum
,hsi.rimlog.messagetext
FROM hsi.rimlog
INNER JOIN hsi.useraccount ON hsi.rimlog.usernum = hsi.useraccount.usernum
where (hsi.rimlog.logdate between @{DATERANGE})
AND hsi.rimlog.actionnum in (##OB_OPTIONSELECT_ALL{(@{ACTIONNUMBER}, 0)})
AND hsi.rimlog.usernum in (@{USERNUMBER})
If i just do a generic parameter without the macro for actionnum it works fine (except doesn't implement the all option in the query obviously), and as you can see there are no other "OR" statements, so it seems like the error has to be coming from the implementation of the macro. The SQL debug shows the macro being referenced, so I'm at a loss.
Any ideas?
07-10-2020 01:51 PM
Hi, Kirk. Thank you for using Reporting Dashboards. Could you try shifting the parentheses so that they wrap the entire actionnum clause? That is, where you have this:
AND hsi.rimlog.actionnum in (##OB_OPTIONSELECT_ALL{(@{ACTIONNUMBER}, 0)})
try changing it to this:
AND (hsi.rimlog.actionnum in ##OB_OPTIONSELECT_ALL{(@{ACTIONNUMBER}, 0)})
Does that help?
07-10-2020 03:20 PM
Hi Jason,
Thanks for replying.
I tried your suggested change, and it didn't even let me past the parameter configuration screen (in the data provider configuration wizard), firing a "Incorrect syntax near the keyword 'NULL'." error both in Unity and diagnostics console (app server) when I pressed next to go to the final configuration screen.
I then tried keeping your outside parentheses, but adding another set around the in clause like this:
AND (hsi.rimlog.actionnum in (##OB_OPTIONSELECT_ALL{(@{ACTIONNUMBER}, 0)}))
and now I'm back to the same original "Incorrect syntax near the keyword 'OR'" error.
07-10-2020 04:05 PM
Kirk,
Have you tried removing the parenthesis around the ##OB_OPTIONSELECT_ALL statement? Just thinking here.
Thanks.
07-13-2020 06:33 AM
I apologize for posting an answer that did not work. I have bad news and not-so-bad news. The bad news is that we don't currently support using ##OB_OPTIONSELECT_ALL with a multi-selection of values. It only works with single values.
The not-so-bad news is you can work around this by using an additional parameter. For example, I can add a new boolean parameter ALLACTIONS which, when checked, overrides the ACTIONNUMBER parameter by accepting all actionnum values.
SELECT hsi.rimlog.usernum
,hsi.useraccount.username
,hsi.rimlog.logdate
,hsi.rimlog.actionnum
,hsi.rimlog.messagetext
FROM hsi.rimlog
INNER JOIN hsi.useraccount ON hsi.rimlog.usernum = hsi.useraccount.usernum
where (hsi.rimlog.logdate between @{DATERANGE})
AND ((@{ALLACTIONS}=1) OR hsi.rimlog.actionnum in (@{ACTIONNUMBER}))
AND hsi.rimlog.usernum in (@{USERNUMBER})
Find what you came for
We want to make your experience in Hyland Connect as valuable as possible, so we put together some helpful links.