to run a named query. For test purposes value is hard coded in, but will be replaced with at variable tag read.
I am getting an error: File input, line 3 - index 2 is out of range
Here is the nameQuery:
SELECT SpecPoint.Descr AS 'Description',
SpecPoint.ScalarValue as 'Value',
SpecPointLimit.LowerLimit As 'Minimum',
SpecPointLimit.UpperLimit as 'Maximum'
--,SpecPointLimit.LowerLimit, SpecPointLimit.UpperLimit,
--, Spec.PlantID
--,SpecPoint.SpecID, SpecPointLimit.ID, Spec.ID
FROM SpecPoint
INNER JOIN Spec ON SpecPoint.SpecID = Spec.ID
INNER JOIN SpecPointLimit ON SpecPoint.ID = SpecPointLimit.SpecPointID
-- INNER JOIN ProcessType ON Spec.ProcessTypeID = ProcessType.ID
INNER JOIN SpecProduct ON SpecPoint.SpecID = SpecProduct.SpecID
INNER JOIN Product ON SpecProduct.ProductGUID = Product.GUID--,
-- Reference,
-- SpecNote
WHERE :SpecNum --SpecNbr + '-' + CAST(RevisionNbr AS VARCHAR) = :SpecNum --Product.Number = :SpecNum
AND Spec.StatusLevelID = 2
--AND SpecPoint.ScalarValue = 100
AND Spec.PlantID = 210
ORDER BY SpecPoint.Descr ASC;
I will remove the commented out parts when I finish the query.
Any ideas on why I have an index error?
Alright, I have made both of the changes by lrose and pturmel. I looks like it tried to run the query and I get a new error:
com.inductiveautomation.ignition.client.gateway_interface.GatewayException: com.inductiveautomation.ignition.client.gateway_interface.GatewayException: com.microsoft.sqlserver.jdbc.SQLServerException: An expression of non-boolean type specified in a context where a condition is expected, near 'AND'.
SELECT SpecPoint.Descr AS 'Description',
SpecPoint.ScalarValue as 'Value',
SpecPointLimit.LowerLimit As 'Minimum',
SpecPointLimit.UpperLimit as 'Maximum'
FROM SpecPoint
INNER JOIN Spec ON SpecPoint.SpecID = Spec.ID
INNER JOIN SpecPointLimit ON SpecPoint.ID = SpecPointLimit.SpecPointID
INNER JOIN SpecProduct ON SpecPoint.SpecID = SpecProduct.SpecID
INNER JOIN Product ON SpecProduct.ProductGUID = Product.GUID--,
WHERE SpecNbr + '-' + CAST(RevisionNbr AS VARCHAR) = :SpecNum
AND Spec.StatusLevelID = 2
AND Spec.PlantID = 210
ORDER BY SpecPoint.Descr ASC;
I forgot to add the columns/rows to look for with the parameter. I had just the parameter:
WHERE SpecNbr + '-' + CAST(RevisionNbr AS VARCHAR) = :SpecNum
Consider passing SpecNbr and RevisionNbr separately, with separate comparisions in the WHERE. Casting and concatenating are not efficient. You probably want to make sure to have indices on some or all of the columns you use in the WHERE clause, or this won't scale.