I have got a very complicated view that I need to apply additional two join to it and also group it only by 3 columns of over twenty main columns the view returns.
I have applied a sub query before my new joins and tried to group it by the sub query select ( 3 columns ) but I cant get it working , is there any roles that stopping this condition, I appreciate any help, fairly beginner here. thanks
It is hard to offer any useful suggestions without seeing the code. If the view is very long/complicated, you might construct a simplified example and post it, along with the desired changes you need.
You can wrap the existing query in a CTE and then do the joins and grouping to the CTE, if that makes it simpler conceptually. For example,
;WITH cte AS ( --- Your original select query from the view here. ) SELECT c.Col1, c.Col2, t1.Col3, SUM(t2.Col5) FROM cte c INNER JOIN Table1 t1 ON t1.col7=c.col1 INNER JOIN Table2 t2 ON t2.col8=c.col2 GROUP BY c.Col1, c.Col2, t1.Col3;
Agree, it is definitely hard to answer without an example, not sure about company policy but it is something like below,
my new columns
left outer join
left outer join
where w.col1 = "vv"
and w.col2 = 0
I hope I could drew a better picture with above example sorry if that is a little messy
so What I want to do is to add another two joins as below
- left outer join t on t.tid = w.id and t.xx = w.xx
- and group it by my above three new columns ( t.col18 w.col19 t.col20)
I hope this make sense
any advice ?
this is old question, but thanks . I have another issue now which is more complicated , happy to share if you have som free time to help