avoid long, custom SQL in lookup fields, use stored views
Posted: 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:
Don't know if this helps anyone, but as your SQL grows more complex, this might be an idea.
Olaf
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 DESCDon't know if this helps anyone, but as your SQL grows more complex, this might be an idea.
Olaf
