10-29-2021 01:14 PM
Hi,
I created a customer query data provider that has CASE statement. If I remove the case statement from the query below, I won't get error, but I need the CASE statement, and the query works fine in SQL Server Management Studio. Error is happened when I Save my Data Provider.
Here is UnityClient Troubleshooting Message:
Select A.batchnum as "Scan Batch #", A.batchname as "Batch Name", A.queuename as "Scan Queue Name"
,Convert(varchar(25), DATEADD(mi, DATEDIFF(mi, GETUTCDATE(), GETDATE()), A.datestarted)) as "Date Started"
,Convert(varchar(25), DATEADD(mi, DATEDIFF(mi, GETUTCDATE(), GETDATE()), A.dateended)) as "Date Ended"
,DATEDIFF(Day, A.datestarted, getdate()) as "Days In Queue"
,A.totaldocuments as "DocumentCount", U.username as "UserName"
, CASE A.Status
WHEN 0 THEN 'Awaiting Index'
WHEN 1 THEN 'Index in Progress'
WHEN 2 THEN 'Awaiting Commit'
WHEN 3 THEN 'Incomplete Commit / Commit In Progress'
WHEN 4 THEN 'Incomplete Commit / Commit In Progress'
WHEN 5 THEN 'Incomplete Commit / Commit In Progress'
WHEN 6 THEN 'Incomplete Commit / Commit In Progress'
WHEN 8 THEN 'Committed'
WHEN 9 THEN 'Incomplete Purge'
WHEN 14 THEN 'Awaiting Full-Page OCR'
WHEN 15 THEN 'Awaiting Full Text Index'
WHEN 17 THEN 'Disconnected Scan - Checked Out'
WHEN 18 THEN 'Disconnected Scan - Incomplete Upload'
WHEN 19 THEN 'Incomplete Archive'
WHEN 20 THEN 'Secondary Awaiting Index'
WHEN 21 THEN 'Secondary Index in Progress'
WHEN 22 THEN 'Failed Automatic OCR'
WHEN 23 THEN 'Awaiting Doc Separation'
WHEN 24 THEN 'Image Segment Archiver'
WHEN 26 THEN 'Awaiting Reindex'
WHEN 27 THEN 'Reindex in Progress'
WHEN 30 THEN 'Administrator Repair'
WHEN 31 THEN 'Awaiting QA Image Quality Review'
WHEN 32 THEN 'Awaiting QA Review'
WHEN 33 THEN 'Awaiting QA Rescan'
WHEN 34 THEN 'Awaiting Manager Resolution'
WHEN 35 THEN 'Awaiting QA Reindex'
WHEN 36 THEN 'QA Reindex in Progress'
WHEN 38 THEN 'Awaiting PDF Conversion'
WHEN 40 THEN 'Error Correction Queue'
WHEN 42 THEN 'Awaiting Ad-Hoc OCR'
WHEN 43 THEN 'Awaiting External Index'
WHEN 44 THEN 'Awaiting Barcode Processing'
WHEN 46 THEN 'Awaiting Image Processing'
WHEN 47 THEN 'Custom Processing'
WHEN 48 THEN 'Ad-Hoc Rescan'
WHEN 51 THEN 'Awaiting Automated Index'
WHEN 52 THEN 'Awaiting Ad-Hoc Automated Index'
WHEN 55 THEN 'QA Review in Progress'
WHEN 67 THEN 'Awaiting Intelligent Automated Index Processing'
WHEN 68 THEN 'Awaiting Scan Queue Sorting'
WHEN 73 THEN 'Batch Deleted?'
WHEN 74 THEN 'Full Page OCR In Progress'
WHEN 76 THEN 'Advanced Capture in Progress'
ELSE 'Unknown'
END as "Batch Status"
FROM hsi.archivedqueue A LEFT JOIN hsi.useraccount U ON A.usernum = U.usernum
WHERE (A.queuenum = ##OB_OPTIONSELECT_ALL{(@{Queue}, -1)})
AND (A.datestarted Between @{DateRange})
AND (A.status <>8)
ORDER BY A.queuename, A.datestarted DESC
Thanks
Laura
11-01-2021 05:57 AM
Is there anything relevant in the Diagnostics Console running on the Application Server that your Unity Client is connected to? Or in that Application Server's Event Viewer?
Perhaps I'm wrong, but I don't believe that error message is pertinent to the "One or more errors occurred" message that you're getting. The error in Unity seems to be about creating a Performance Monitor performance counter.
12-01-2021 08:08 AM
Hi Sean,
I checked Diagnostics Console, I got following error. I do not know where "@P3" comes from. As you see my sql from previous post, it does not have P3 parameter. It has to do with CASE statement, if I remove CASE statement, it works fine, is there a special syntax for a case statement in Data Provider ?
12-10-2021 10:38 AM
I found the reason why it was not working, it is report dashboard that cannot handle a question mark in data provider, it does not matter if you have put it inside quotes or a comment . Is this issue only in EP2 version ?
12-10-2021 11:32 AM
I hate that I am just now seeing this string. I had exactly the same issue. When I removed the "?" from the SQL Query it worked. I am on EP3.
It's weird that it works fine in Report Services or right in a SQL Query but not in Reporting Dashboards!
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.