Incorrect syntax near the keyword 'OR' when using ##OB_OPTIONSELECT_ALL macro

[Kirk_Sumpter]

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?

[Jason_Knight]

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?

[Kirk_Sumpter]

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.

[Ryan_Wakefield]

Kirk,

Have you tried removing the parenthesis around the ##OB_OPTIONSELECT_ALL statement? Just thinking here.

Thanks.

[Jason_Knight]

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})