VReport general idea

[Thorsten Hohage]

On 2009-02-26, at 14:47, Beatrix Willius wrote:

> Oh my god!!! This is much too complicated. What about a simple pivot  
> query? Group the data as you need it and do a pivot over it with the  
> count.


No it is NOT complicated, only much text to type as I stated several  
times in my mail. But at least when you need 12 columns, you need it  
and you need to type it.

You see the columnnames?  Of course Valentina offers a monthname()  
function, so in this case it is not difficult, but in many other cases  
it is difficult when you're going to use pivot functions. Then you'll  
end up with a big case-statement to find out the right "columname" for  
this row.

Furthermore when knowing how to do this in the way I've done, then you  
can do so many more things, e.g. think of systemmigration, so for the  
first 4 months using the old tables, and for all other the new ones.  
Insert logic, if in the middle of the year some more sales staff must  
be kept in mind, ...

And while thinking about given scenarios perhaps 50% can be smarter  
build with pivot, but the other part will be never build on pivot  
functions.


Of course there may be so many more smart ideas, how you can solve  
this with few lines of code and without duplicating all the code for  
each month manually. E.g. write a nice meta Stored Procedure  
generating the strings with a few given parameters and then simply  
call an execute() on this string :-)


But while we can discuss all this high-end-sql a beginner will perhaps  
be lost right before the word "pivot". So sometimes is a longish,  
simple statement where you can see the idea behind a better solution.


regards,

Thorsten Hohage
-- 

Valentina Technology Evangelist
generic objects  GmbH - Leiter Solution Center Nord