I have been doing a lot of maintenance programming lately and i run into a couple diff queries and i was just wondering what's it advantages over the others and when should you use each one or if you should based on standardization.
When and why should you use dynamic queries?
When and why should you use Hard Coded Queries?
When and why should you use defined queries?
Some report we do we use hard coded over that other and some of our screens we have we use dynamic and what not.
Also i was wondering if anyone knew of any white papers to help me with contructing queries all types.
thanks
David Pipes
Dynamic queries have an advantage when the possible selection criteria are numerous since one can then compose a query that corresponds directly to which criteria are in use in the particular case.
Some people also use dynamic queries to create re-usable code modules, but that doesn't sound like what you are seeing.
Hard coded queries make it easier to see what is going on and facilitate the use of various kinds of analysis tools, but when selection criteria are numerous, one can end up writing a lot of variations.
When and why should you use dynamic queries?
When and why should you use Hard Coded Queries?
In the "old days of Progress", I think it was up to version 7, there was no dynamic query feature. So it meant that you had to hard code every query, since the compiler required so. Nowadays you can use the dynamic query to reduce the # of programs and the # of code lines, since you now can create some generic helper programs that can do the main work. You can create a generic, dynamic query.p (or query.cls which can return a temp-table.
I think its wise to use static queries (hard coded) when you can, so it will give you compile time checks... Note that a static query includes a table reference in your r-code, , which means your program is sensitive for database schema changes. But this can be a good thing, since you will be told your r-code doesn't match the current database schema.
One of the drawbacks of the dynamic query is that you have to construct a query string. This means you will have to escape values (parameters) and you have to check if a value you're adding isn't the unknown value:
Now "great" will be "?".
Nowadays you can use the
dynamic query to reduce the # of programs and the #
of code lines, since you now can create some generic
helper programs that can do the main work. You can
create a generic, dynamic query.p (or query.cls
which can return a temp-table.
check the query manager in the Code Share section. It can be used both for creating completely dynamic queries, or for setting join & filter conditions on a static query.
It makes life a lot nicer than not having it.
that's why we always use SUBSTITUTE for string manipulation
now "great" will be "My name is: ?"
that's why we always use SUBSTITUTE for string
manipulation
ASSIGN name = ?
great = SUBSTITUTE("My name is: &1",name).
now "great" will be "My name is: ?"
And there is the QUOTER() function nowadays as well, so:
ASSIGN name = ?
great = "My name is: " + QUOTER(name).
will produce the same result....
Yes, but the SUBSTITUTE approach is recommended for making the application translatable.