huh ?
Check for silly stuff. Have you a space after 'E373 '?
1 Like
Effectively makes the entire set of conditions a No-Op if none of the options are provided. Not terrific for performance.
1 Like
Probably want to trim() the parameter input and the column values itself to avoid this to avoid cases where the user adds a space, or if the data itself has an extraneous one.
From what I see, it makes the whole where clause useless: everything in it is always going to be true.
Or am I reading this wrong ?
1 Like
Can't seem to find exactly what is happening but if I use 'E373%' it seems to work.
Would that have potential to cause any issues in the future, or would that be ok to use?
That suggests that there are trailing spaces in the database columns. Try modifying the query to remove these using the TRIM function:
FROM [TransactionGroup].[dbo].[AQVision_Cognex]
WHERE TRIM(PartNumber) LIKE :partNumber
WHERE TRIM(MachineID) LIKE :machineID
WHERE DateTime BETWEEN :startDate AND :endDate
While ``E373%` might work now it could bite someone later if there are variants. I wouldn't.
No, you're correct, that's what I am saying. The query suggested by chatGPT is incorrect, because OR 1=1 turns the entire WHERE clause into a No-Op. So if that query was used it would always return all rows, and have horrible performance because SQL would still perform the evaluations.
Sorry, wasn't clear in my post, was actually expanding on your question and stating why it makes no sense.
2 Likes
Seems like this all worked. The pie charts and tables seem to be working much better and faster. Thanks for the help!