The query below returns 6 rows. LockOutPointID is returned as
[ul]
L1-E2
L1-E2
L1-E2
L1-E2
L1-E4
L1-E4
[/ul]
I would like to return only distinct rows, however, the image column cannot be in the query if I use the distinct keyword. Any suggestions on how to do this?
SELECT
LockOutPoints.LockOutPointID,
LockOutPoints.LockOutName,
LockOutPoints.LockOutPointLocation,
LockOutPoints.LockOutDevice
LockOutPoints.LockOutImage
FROM LockOutEnergySources
JOIN
LockOutPoints ON LockOutPoints.LockOutPointID = LockOutEnergySources.LockoutPoint
WHERE LockOutEnergySources.JBoxCode = '{Root Container.JBoxCode}'
Thanks,
Sure just use the GROUP BY clause. Something like:SELECT
LockOutPoints.LockOutPointID,
LockOutPoints.LockOutName,
LockOutPoints.LockOutPointLocation,
LockOutPoints.LockOutDevice
LockOutPoints.LockOutImage
FROM LockOutEnergySources
JOIN
LockOutPoints ON LockOutPoints.LockOutPointID = LockOutEnergySources.LockoutPoint
WHERE LockOutEnergySources.JBoxCode = '{Root Container.JBoxCode}' GROUP BY LockOutPoints.LockOutPointIDYou may have to apply aggreagate functions like MAX, MIN, AVG or something for certain columns. Try it and if it errors out apply the functions.
I decided to do this with a property change script. The first query returns all of the distinct Jboxes and I iterate through this list to retrieve the images. It may not be the cleanest but I can finish the job.
if event.propertyName == "Data":
strJBox = event.source.parent.JBoxCode
loPoints = system.db.runPrepQuery("SELECT DISTINCT LockOutPoints.LockOutPointID, LockOutPoints.LockOutName, LockOutPoints.LockOutPointLocation, LockOutPoints.LockOutDevice FROM LockOutEnergySources JOIN LockOutPoints ON LockOutPoints.LockOutPointID = LockOutEnergySources.LockoutPoint WHERE LockOutEnergySources.JBoxCode = ?", [strJBox])
headers = ["LockOutPointID","LockOutName","LockOutPointLocation","LockOutDevice","LockOutImage"]
data = []
for row in loPoints:
LockOutID=row["LockOutPointID"]
tempImage = system.db.runPrepQuery("SELECT LockOutPoints.blob FROM LockOutPoints WHERE LockOutPoints.LockOutPointID = ?", [LockOutID])
LockOutImage = tempImage[0][0]
data.append([LockOutID,row["LockOutName"],row["LockOutPointLocation"],row["LockOutDevice"],LockOutImage])
event.source.Data2 = system.dataset.toDataSet(headers, data)