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
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 ?
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 ?
