I didn’t provide any context around that last post and thought maybe I should.

In my shop, we provide the backend api support for our corporate systems web team. Basically, they build the interface and we build the data structures and primary engine components. One popular thing always needed was the demographic data itself. I started to see the trend of information being requested and opted to build a common api set to deliver that information. I built in some definition parms so that I could use it for different feeds, each with its own attributes.

However, for one particular need they needed to provide a list of values. So I leveraged associative arrays. I take the data in and dump into a temp table and run my query e.g. where id in (select value from gtt). All of these different types of ’search conditions’ are stored in a table and the web simply identifies the type of search and provides the list. I build the piece onto the base query dynamically and execute. This keeps me from having to build multiple new apis, I just add another search type entry to the table and the SQL context that goes with it. Works great… till yesterday when they wanted to do LIKE.

Since the inception of the original api set, I had created a criteria grid method which handles it just fine and more. That logic can be found here under the Dynamic SQL and Variable Parameter Input. However for them to switch to the new api, would have risked the go-live date which was in a few weeks.

Instead… after a bit of thinking… it came to me to use the approach I provided here and it resolved the issue. There… that explains the why a bit better.

P.S. If more time was permitted, I would steer them towards the criteria grid due to the substantially increased flexibility. Just my 2 cents for what its worth.