Blogs

Query with User-Defined SQL and Ask At Runtime Parameters

By Darryl Hopkins posted 10-28-2010 17:35

  
Starting with the 2009 release of netFORUM Enterprise (aka netFORUM 3.0), you can create Queries with User-Defined SQL and ask at runtime parameters. Prior to that release, you could modify SQL but you could not prompt users for runtime parameters, which greatly restricted the power of this feature.

For the uninitiated, there is a "Create SQL" button on the Query designer visible to users in one of the Admin groups. This button will launch a page in which you can write any Transact SQL you wish, as long as you follow certain standards. With this feature you can write more complex SQL to power your queries. Great care should be taken to ensure your queries are optimized.

The reason I'm blogging on this feature is because it's very powerful but not very well known. Just today we helped a customer trying to write a complex query, and we used this feature to make it work. We have it documented on the Create Query SQL page in http://wiki.avectra.com.  I hope you will find this feature useful to expanding the reach and power of your queries.
4 comments
304 views

Related Content

Permalink

https://www.netforumusersgroup.org/blogs/darryl-hopkins/2010/10/28/query-with-user-defined-sql-and-ask-at-runtime-parameters

Comments

Daniel Tomberlin
07-20-2021 14:38

Below are examples of how to add "Ask at Run-Time" conditions to Create SQL:

Check Number Begins With:
WHERE pin_check_number LIKE N'<ask columnname="pin_check_number" />%'

Email Address Equal To:
WHERE email.eml_address = N'<ask columnname="eml_address" />'
(here the Query Tool has an Alias on co_email table, but it's not used in the parameter)

Matt Forrest
11-10-2010 19:45

The user community has been clamoring for this feature for years... and that's just at NRECA! ;-)
Very exciting news.
Thanks!!

Sahil Kapadia
11-09-2010 10:58

Hi Darryl,
That is extremely exciting. Can't wait until we migrate to 3.0 and implement it. It will greatly improve the Query Building experience and support to our end users.
Thanks,
Sahil

Jeff Miller
11-03-2010 23:06

Thanks for blogging about this Darryl! I didn't know of this feature - but it is DEFINITELY something that our power users will look forward to in the new version (not to mention my IT team!).
Thanks,
Jeff M.
ACFE