On 21/11/2012 16:40, [email protected] wrote:
Hi John,
I think a smart SQL-Statement schould do.
OK that would probably a bit inflxible, since you have a lot of changes
concerning users and products.
But you could may be write a SQL-Generating routine that takes care of it.
I've been playing around a bit with this; it helps, but doesn't quite
hit the spot for me. So here I am again...
The problem for me is not so much gathering the results as presenting
them. I have a monstrous method to display a table (or view), which
builds the SQL query and appends WHERE and ORDER BY clauses as needed.
This works fine, for cases where I can define a table or a view of a
table, which means a fixed set of columns. I use temporary tables in
the cases where the table has to be generated dynamically (when I want
to pivot rows into columns), but as I said before, this is slow and ugly.
So apart from a temporary table, I thought about these possible solutions:
1) Create a real table every time a department is created and then use
triggers to add and drop columns as products are added and removed, and
to copy values when the "main" table is updated. The set of departments
changes relatively slowly, but it does change; the products go through
flurries of activity. I'm not sure how practical this is.
2) Use a function to return a virtual table. The problem here is that
the table format is fixed when the function is declared. I could perhaps
declare functions that return tables with maybe 100 columns and then
hope that no-one ever needs 101 products, but this isn't very
satisfactory either.
3) I've just been reading about procedures that return result sets, but
then the problem is that I can't decorate the query (a CALL rather than
a SELECT) with WHERE and ORDER BY clauses.
So for now, I'm still stuck.
Your suggestions and advice gratefully appreciated!
--
John English
