SQL Server Connection - Error coming on using [custom measure1]/[measure2] expression in visual

I am getting error when using SQL Server Connection and trying to create an expression using [custom measure1]/[measure2].
[custom measure1] is created at dataset level. [measure2] can be a regular measure column or another custom measure created at dataset level.

Example:
’Accepted’ custom measure at dataset level:
CASE WHEN [session_disposition_type_id] IN (1,2,16) THEN session_history_id END

’session_history_id’ is a regular measure.

Expression at visual level:
count(distinct([Accepted]))/count(distinct([session_history_id]))

Error:
Validation Error: (103, b"The identifier that starts with ‘(count(distinct ((CASE WHEN (TA_0.[session_disposition_type_id]) IN (1,2,16) THEN (TA_0.[session_history_id]) END)))/count(distinct (((TA_0.[session_history_id])))))*100’ is too long. Maximum length is 128.DB-Lib error message 103, severity 15:\nGeneral SQL Server error: Check messages from the SQL Server\n")

This similar scenario works fine when using ArcEngine connection. Please suggest.

@soumyaawasthi

  1. What is the full SQL that’s generated?

  2. Have you checked the SQL server logs for more info?

  3. What happens if you create the entire expression in the visual level (instead of dataset level). Does that work without error?

@shaun

  1. SQL generated is as below:
    SELECT TOP(5000) count(distinct((CASE WHEN (TA_0.[session_disposition_type_id]) IN (1,2,16) THEN (TA_0.[session_history_id]) END)))/count(distinct((TA_0.[session_history_id]))) as ‘count(distinct(sessions_accepted))/count(distinct(session_history_id))’
    FROM [IntradiemAnalysisMT_qa1001].[dbo].[SESSION_HISTORY] TA_0

  2. Same error comes when I create the entire expression in the visual level.

  3. I was not able to identify in SQL Server logs related to this error.

@soumyaawasthi

  1. What I don’t understand is the square brackets in your SQL. Is that indeed the SQL that server is processing? I don’t think square brackets are proper SQL syntax.

  2. Also maybe the single quotes is throwing things off, where the alias is specified. Maybe try using a smaller alias like “value” and not use single quotes around it?

  3. Check on MS SQL server : QUOTED_IDENTIFIER is set to either OFF or ON. It seems that also might impact processing of single vs double quotes.

@shaun

  1. This same query works fine in SQL Server:

  2. Working with same alias in SQL Server directly as shown in above screenshot.

  3. QUOTED_IDENTIFIER works fine.

@soumyaawasthi okay , I will have to check with product engineering. This might be an issue with the way the current driver work.

In the meantime, you might want try a few of the previous suggestions to see if that unblocks you.

BTW - what version of Arcadia are you using?

@shaun, thanks for the update. I have already tried using the expression directly at visual level and also with a smaller alias as ‘value’. The error remains same.

Version -> Arcadia Enterprise 4.5

Thanks!