On Sun, Aug 16, 2009 at 4:55 PM, Leyne, Sean<Sean@...> wrote:

> Hi, Vlad,
>
> <SL> Actually, it was Sean who replied ;-)

Sorry about that, Sean... I was getting used to see Vlad answering
about CTE issues...

>> 3 - No elements/columns of CTE_3 is referenced anywhere in the final query...  so why it is there?
>
> I get CTE_2.VENDAS_MES_1, which is the sales in month 1 for a
> salesperson, and compare it to CTE_3.VENDAS_MES_1, which is the total
> sales in month 1, and so on. The final select would be like this:
>
> <SL> I would suggest you try to replace the CTEs with temporary tables/GTTs to store some intermediate data values for the query.  GTT would allow for the values to be placed into the appropriate month columns/slots and also have indexes added to facilitate appropriate joins.

I will try to do so, although my first choice would be to avoid
metadata changes when possible, even because i will use this
instruction within a report manager tool, and i would like to avoid to
give this possibility to report developers for now.

Anyway, do you think the fact CTE_3 is exploded once for each row when
i have a constant join condition is something that can be improved?

Regards,

Fabiano