Use IF/Else or CASE Logic in SQL query for an Autofill Keyword Set, is this possible?

[Brodie_Franklin]

Hello everyone,

I'm not sure if this is possible but the scenario we're facing is that we have many external autofill keyword sets to help users when indexing.  Recently we've seen documents incorrectly indexed because someone will type in a primary keyword that is incorrect for the document but is a valid keyword value and does populate the related keywords.  The problem we're seeing is that someone will recognize their mistake and then type in another primary key, except this time it's an invalid primary key.  No data is returned for that primary key but the previous data, populated with the previous incorrect but valid primary key, is still there.  

I was hoping we could build some logic in the SQL query of the autofill to return blanks in the related keywords if the primary key is invalid, but so far am getting errors any time I try to add an IF/Else or CASE statement to the query in Config (works in SQL server).  

Has anyone had a similar issue and/or had any luck with adding conditional logic to SQL queries in external autofill keyword sets?

[Marcus_Christi4]

Yes sir - that's the tricky bit.  Nicely done.

Hi Brodie, You can probably accomplish what you need in a pure SQL SELECT statement without a Stored Procedure or Function involved. As Tom suggests, you could still package that SELECT statement inside a View in your database to keep the Autofill simple if you want to. Here's an example that looks at the hsi.useraccount table in the OnBase DB (just as an example), and Autofills a realname from a username. It assumes that there will always be either one or zero results. By UNIONing together any valid results and a blank row, and taking just one row, you'll always get either a blank row or a valid result. The idea could be adjusted a little if you sometimes need the user to pick from multiple valid rows. Oracle syntax would be very similar, you'd just need to "select from dual" to get the blank row. select top 1 username, realname from ( select username, realname from hsi.useraccount where username = '@primary' UNION select top 1 '' as username, '' as realname ) results