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
I think the problem may be that the ? appears to be a reserved character as it is used to precede/indicate parameters, see the EP3 Reporting Dashboards, page 88. This page implies that the ? is used for specific purposes within the data providers.
I did a little test in my local EP3 environment, and wrote a query to pull back my keyword types:
SELECT ktt.keytypenum, ktt.keytype, ktt.datatype, ktt.keytypelen , CASE ktt.keytype WHEN 'Account #' THEN 'HELLO WORLD' WHEN 'Batch Deleted?' THEN 'HELLO WORLD' ELSE 'UNKNOWN' END as caseStatment , 'Batch Deleted?' AS batchDeletedFROM hsi.keytypetable kttORDER BY keytype
I return a few columns, I set up a Case statement, and I am returning a hardcoded value with ? in it.
The Case statement that evaluates the Account #, works nicely. The Case statement that evaluates the ? in Batch Deleted?, does not render properly. The last column is telling to me. In my query, I am simply returning the string 'Batch Deleted?' and you can see the system has replaced the ? with p@1. It's as if, the system sees the ? and attempts to replace it with a parameter value. I think that may explain the exception you are seeing with p3.
I am not sure if there is a way to escape a ? in the data provider, but I think that is where I would look to solve this.
