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]

A little late but...

Unfortunately, it's been a while.  A DBA helped me figure out a tricky way to do what you're trying to (presumably) do, but I can't remember exacts and I believe that was Oracle in any case.  I can say that you should be able to do most anything as a stored procedure as long as it returns rows off @Primary.

If you're getting errors it likely means that OnBase is trying to pull X keyword values back yet is only getting Y return values.  If it's seeing the empty values it may be dropping them out, so you would return blank strings for each field, in the correct sequence.

[Ediaz]

Hi Brodie. The way I see that you can meet your need is to place an SP (stored procedure), so you can do what you need.

You must consider two things:

    1. Ensure that the SP has a good performance, if it takes time to execute, you may have performance problems in the solution
   2. As Marcus mentioned you should keep in mind that you always send the @Primary, example: EXEC dbo.AutofillSP @ YouVariable = @Primary

Thanks

[Thomas_Reu]

SQL Server (or Oracle) provides a much better interface for viewing and maintaining sql code.  It also gives you an extra check, e.g. it works in sql server.  I do this all the time, by setting up the view I need in SQL Server.  For me, it seems like there is always data manipulation required to get the data ready for autofill. 

Anyway, my preference is never to put complex SQL into a 3rd party tool.  It just asks for trouble, since there is no way a 3rd party tool can be as sophisticated as SQL Server or Oracle .  Then once you have the sql view working you just do something like the following in your onbase sql select string.

select *
from External_DB_Name.schema.vw_name_Autofill
where ID = '@primary'

Don't forget to grant access to your view based on the DSN and User ID that you choose...  Also, if you aren't proficient in sql, then developing a view at the DB level gives you, or your DBA, easy access to a plethora of diagnostic tools, so your potentially inefficient sql doesn't crash the DB.

[Alex_French]

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