HelpSpot Help Desk Software | HelpSpot Blog | HelpSpot Support

"syntax error at or near ";"" for Custom Where Clause


#1

In an automation trigger, I am trying to use a custom where clause that looks like this:

HS_Request.sEmail not in (select distinct sEmail from HS_Request where custom20 = ‘something’)

This is to prevent the trigger from hitting customers who’ve had any request in the past where custom field 20 is equal to ‘something’.

However, I get the error: “syntax error at or near “;””. The error message gives me a position for the error, which is at the first single quote before ‘something’. For some reason I can’t compare a custom field to a string? The sql works when I change it to the following:

HS_Request.sEmail not in (select distinct sEmail from HS_Request where custom20 = 1::text)

so I am pretty sure the issue is with the quotations. I get the same problem when using double quotes. Anybody know whats going wrong?


#2

I believe we replied in email, but you probably don’t need a custom where. You should be able to do that same thing just selecting the custom fields from the condition list.


#3

The main issue I am trying to raise here is that you don’t seem to be able to make comparisons to strings in the custom “where” clause. Whenever I put anything in quotes, single or double, I get the error: “syntax error at or near “;””

My other issue was that I’m trying to gather information about previous requests from the same customer, rather than info about the current request; I don’t want to eliminate the trigger from hitting requests where custom20 = ‘something’, instead I’m trying to eliminate the trigger from hitting customers that have had previously closed requests where custom20 = ‘something’. I’m pretty sure I am unable to make this happen by selecting custom fields from the condition list. I did find a workaround previously as noted by using 1.