I have a stored procedure as below,
ALTER PROCEDURE [dbo].[getLabTestDataBatchView]
@startDate as Datetime,
@endDate as Datetime
AS
BEGIN
SET NOCOUNT ON;
DECLARE @Columns1 as VARCHAR(MAX)
DECLARE @Columns2 as VARCHAR(MAX)
DECLARE @columns3 as VARCHAR(MAX)
SELECT DISTINCT lton.order_number AS OrderNo,
c.compound_name AS compoundName,
lt.batch_number AS batch_number,
CASE WHEN lt.machine_number = 0 THEN m.name
ELSE ltn.test_machine
END AS test_machine,
CASE WHEN lt.pass_customer_specs = 1 THEN 'Pass'
ELSE 'Fail'
END AS passCustomer
INTO #TEMP2
FROM lab_test lt
JOIN lab_test_compound ltc
ON ltc.id = lt.lab_test_compound_id
JOIN compound c
ON c.id = ltc.compound_id
LEFT JOIN lab_test_machine_configuration ltmc
ON ltmc.id = ltc.lab_test_machine_configuration_id
LEFT JOIN lab_test_machine ltn
ON ltn.id = ltmc.lab_test_machine_id
LEFT JOIN lab_test_measurement ltm
ON ltm.lab_test_machine_id = ltn.id
LEFT JOIN measurement m
ON m.id = ltm.measurement_id
LEFT JOIN lab_test_order_number lton
ON lton.lab_test_id = lt.id
WHERE lt.last_test = 1 AND lt.date_created >= @startDate AND lt.date_created <= @endDate
SELECT @Columns1 = COALESCE(@Columns1 + ', ','') + QUOTENAME(test_machine)
FROM
( SELECT DISTINCT test_machine
FROM #TEMP2) AS B
ORDER BY B.test_machine
;
SET @columns3 = ' SELECT compoundName,
OrderNo,
batch_number,
' + ISNULL(@Columns1,'''-'' AS [column]') +'
FROM ( SELECT *
FROM #TEMP2) AS PivotData
PIVOT (MIN(passcustomer)
FOR test_machine IN ('+ @Columns1 +')) AS pvt1
ORDER BY compoundName, batch_number'
EXEC(@columns3)
DROP TABLE #TEMP2
-- Insert statements for procedure here
END
Everything runs fine when I call it from a table data binding as,
But, when there is no data available for a selected date it throws an exception as below rather than returning 0 dataset !
Exception: Error running query:
SQLQuery(query=Execute getLabTestDataBatchView '2020-01-31 00:00:00', '2020-01-31 23:59:59', database=LoMOS)@0ms
On: LabTestResultBatch_Temp.Root Container.DateRangeSelected.data
caused by GatewayException: The statement did not return a result set.
caused by SQLServerException: The statement did not return a result set.
Ignition v7.9.10 (b2018112821)
Java: Oracle Corporation 1.8.0_144
Any Ideas ?
Thanks