All,
I am fairly new to FM and am looking to union 4 separate queries with similar info. When I go to Actions/Define Query Set I can only do two at a time, so in the end I would have two unions unioned together, is there any other way I can do this in one shot? I was thinking about a sql script that unioned them but not sure how to go about that either...
Any advice would be greatly appreciated!
Thanks
g
Just by dragging and dropping you can extend the number of sets in the union. so, if you have the base union of 2 sets, then drag a third query and drop it to the right side of the set operator..
QuoteJust by dragging and dropping you can extend the number of sets in the union
Hi blom,
Are you referring to report studio or framework manager? I don't see how you can expand the query set in FM so would be interested to know if I'm missing something there.
Hi gronhoffga,
Can you get a view in the database created that performs the union? That is usually the best performing option.
Good idea on the view, I will contact my DBA's and see if they can set one up. Just out of curiosity would a union like this even be possible in FM or is this strictly a db level effort?
You can do set operations with query sets but I (like you) don't see how to union more than two queries at a time.
The other option is to create a data source query subject containing whatever SQL statement your heart desires. If you can have it done in the database that is preferable because it will likely perform better and result in minimized SQL.
I sometimes use a SQL query subject just to try out a query before pestering the DBAs to create it -- sort of prototyping the concept. Other times I'd use them when the DBAs can't or won't create database structures to facilitate reporting.
Quote from: gronhoffga on 12 Nov 2010 12:51:36 PM
Just out of curiosity would a union like this even be possible in FM or is this strictly a db level effort?
Let's say you have 4 queries A, B, C and D.
You can create union of A and B resulting in X. Then create union of X and C resulting in Y. Then create union of Y and D resulting in Z. Z is the union of all 4.
If you want to create one query subject containing all 4 unions, then go to Query Information tab of Z, click 'Test Sample', it will show you two SQLs, copy the Native SQL, create one Database Query subject and paste it there. This is the same as what Lynn suggested.
Quote from: Lynn on 12 Nov 2010 12:40:31 PM
Hi blom,
Are you referring to report studio or framework manager? I don't see how you can expand the query set in FM so would be interested to know if I'm missing something there.
Hi gronhoffga,
Can you get a view in the database created that performs the union? That is usually the best performing option.
Stupid me, I sometimes forget the forum I am in ;D I was indeed referring to RS.
I'll second the idea of generating database views. It may also help with generating minimized SQL as opposed to hardcode SQL Query subjects..