I was thinking of doing the following and was wondering if it was possible.
Here is the script I was thinking of:
If checkbox=1:
queryclause = " shift = {root container.shift} and "
else:
queryclause = “and”
fpmi.db.runUpdateQuery(update progmode1 set assigned_operator = {this} where mach_num=%s %s assigned=%s), (machine, queryclause, assigned), “mocrosoft”)
Granted this is not the exact query because I am at home and dont recall exact layout but it give you an idea of what I am trying. I just wanted to run it past the experts and see if these were possible.
Or is there a command similar to the * for SQL. You see on the one page I have a check box if the box is checked then I want to display to the supervisor both 1st and 2nd shift operators, if the box is not checked then it will only display the list of the current shift being worked on.
Without getting into the specifics of your exact query, since you said it’s only an approximation, the answer is YES, you can definitely build up a query piece by piece and/or include parts based on variables.
This is actually a fairly common technique. As the result of a button press or action you have the power of jython to create what you want, but imagine you just wanted your display to change dynamically based on the input. It’s common to have various dynamic properties which are IF expressions (or similar statements), setting their value to either “” or a part of the query. For example, if you had a check box for “Limit to shift 1”, you could have a dynamic property that said something like if({checkbox.selected},“WHERE shift=1”,"") and then refer to that property in your query in place of the WHERE.
On more technique along the lines of this… It’s common that you might be trying to link one or more possibilities. Like, you may have one property for Shift, one for Operator, etc. How do you let the user pick any combination and still have a valid SQL query? The key is to have each property return either a blank string, or something like " AND shift=1" (with an AND or OR in the front). Then you make your query something like “SELECT * FROM table WHERE 1=1 {dynamic_where_clause}”. That 1=1 is the trick- you don’t have to worry about making your query correct, and who has an AND, who doesn’t, etc. They all can, because they’re all AND’d with an always true expression. Note: if you were using ORs you would obviously want an always-false expression, like 1=0.
Yes this helps out quite immensely. I thought that it might be possible. I hate the ride home from work because I will be driving along and suddenly have this idea. So then I have to post the idea for support and guidance on correct layouts and language and also so I dont forget about it by the next day and I can reference it and do the job. So thanks for understanding and thanks for the info.
In most cases you won’t need to use Jython, or at least minimize your use of scripting. The better approach uses dynamic variables with Expressions (such as your if statement). You can bind queries (or use Jython if you must) with queries that were dynamically built as strings.
