avoid long, custom SQL in lookup fields, use stored views

Got something cool to share with AppGini users? Feel free to post it here!
Post Reply
User avatar
onoehring
AppGini Super Hero
AppGini Super Hero
Posts: 1156
Joined: 2019-05-21 22:42
Location: Germany
Contact:

avoid long, custom SQL in lookup fields, use stored views

Post by onoehring » 2019-09-29 11:56

Hi,

just as I am improving my application, I thought, I drop a line. My situation: Having a rather complex SQL in an advanced lookup (by now that custom SQL is 5.511 characters (yes, >5k), spanning 66 lines if styled with SQL Pretty Printer ( https://dpriver.com/products/sqlpp/index.php ).

As this needs to be sent to the database, and the AG advanced lookup custom code window is .. not nice, I thought, I create a view from all that SQL.
In AG I can now use this simple statement:

Code: Select all

SELECT * FROM view_name AS src_table_name_of_lookup ORDER BY a, b, c, d, e DESC


Don't know if this helps anyone, but as your SQL grows more complex, this might be an idea.
Olaf

User avatar
a.gneady
Site Admin
Posts: 1281
Joined: 2012-09-27 14:46
Contact:

Re: avoid long, custom SQL in lookup fields, use stored views

Post by a.gneady » 2019-10-04 08:22

Thanks for the wonderful tip, Olaf :)
:idea: AppGini plugins to add more power to your apps:
  • DataTalk is an innovative AppGini plugin based on ChatGPT that allows you to interact with your AppGini database using natural language questions, without writing any SQL. Check the demo video
  • Mass Update plugin: Update multiple records at once and improve your workflow efficiency.
  • Check our other plugins and get a generous discount of up to 30% when buying 2 or more plugins.

Post Reply