Union and Append Sub Queries
19 November, 2012
Hi,
I want to generate a report that will display from two identical views i.e. a union, but the union must be of the calculated fields in the second sub query also.
I am able to get the results with a union sub query and two append queries, but how I get the two append queries merged together?
Please see attached screen shots.
I want to generate a report that will display from two identical views i.e. a union, but the union must be of the calculated fields in the second sub query also.
I am able to get the results with a union sub query and two append queries, but how I get the two append queries merged together?
Please see attached screen shots.
Hi Nick,
Unfortunately the images were not attached, can you please try re-attaching as we are trying to understand what results you were trying to achieve.
Examples of the result set would also be very useful (prior to any sub-queries).
Thanks,
David
Unfortunately the images were not attached, can you please try re-attaching as we are trying to understand what results you were trying to achieve.
Examples of the result set would also be very useful (prior to any sub-queries).
Thanks,
David
Hi David,
Here are the images. Apologies for that.
I am posting this query on behalf of a potential partner so I will ask him for an example of an output.
Regards,
Nick
Here are the images. Apologies for that.
I am posting this query on behalf of a potential partner so I will ask him for an example of an output.
Regards,
Nick
Attached example data
Hi Nick,
No problems and thanks for all the info.
I did have a consultant look at this though we cannot work out what the client is trying to achieve.
From the data we've seen you shouldn't need an append or union sub-query, though I believe we simply don't understand the results the are trying to get.
Are you able to find out what results they are expecting, and what exactly they are trying to achieve?
Thanks,
David
No problems and thanks for all the info.
I did have a consultant look at this though we cannot work out what the client is trying to achieve.
From the data we've seen you shouldn't need an append or union sub-query, though I believe we simply don't understand the results the are trying to get.
Are you able to find out what results they are expecting, and what exactly they are trying to achieve?
Thanks,
David
Hi David,
Please find attached more information as requested.
I hope this will help.
Regards,
Nick
Please find attached more information as requested.
I hope this will help.
Regards,
Nick
Hi Nick,
Thanks for the info.
We have gone through this, and even to the point of creating the database/table structure.
We don't see the need to create an append query, or even the calculated fields, a simply advanced union query is all you need.
E.g.
I did also create a calculated field in the Master/Sub query so I can see which results came from which table.
E.g (this is from the sub-query).
If you build your report the same as this , what results do you get?
Regards,
David
Thanks for the info.
We have gone through this, and even to the point of creating the database/table structure.
We don't see the need to create an append query, or even the calculated fields, a simply advanced union query is all you need.
E.g.
I did also create a calculated field in the Master/Sub query so I can see which results came from which table.
E.g (this is from the sub-query).
If you build your report the same as this , what results do you get?
Regards,
David
Hi,
I have managed to get a test case from the partner and this is essentially what he is trying to do.
There are two views (COMP_1_View and COMP_2_View) with which he wants to publish a consolidated report on quantity, sales amount and cost.
The union sub query will provide us with a solution. The problem lies with you have a customer code column from both views and it reports duplicates in this consolidated report.
How can we total up quantity, sales amount and cost without having any duplicates in the customer code column?
I have attached the following:
� Dump of the DB (MS SQL 2008)
� Comp1 report, comp2 report and consolidated comp report.
� Comp1 view and Comp2 view
Regards,
Nick
I have managed to get a test case from the partner and this is essentially what he is trying to do.
There are two views (COMP_1_View and COMP_2_View) with which he wants to publish a consolidated report on quantity, sales amount and cost.
The union sub query will provide us with a solution. The problem lies with you have a customer code column from both views and it reports duplicates in this consolidated report.
How can we total up quantity, sales amount and cost without having any duplicates in the customer code column?
I have attached the following:
� Dump of the DB (MS SQL 2008)
� Comp1 report, comp2 report and consolidated comp report.
� Comp1 view and Comp2 view
Regards,
Nick
Hi Nick,
Thanks for the data set, it has helped a great deal get the report you were after.
You will need to ;
-Use an append query with an outer join, so data will be returned when only 1 of the view returns data.
-Use calculated fields to add fields from both queries together.
These calculated fields will also need to contain CASE statements so that it can handle NULL values.
I have created this report for you and attached it to this post.
Please let us know if you run into any issues with this.
Regards,
David
Thanks for the data set, it has helped a great deal get the report you were after.
You will need to ;
-Use an append query with an outer join, so data will be returned when only 1 of the view returns data.
-Use calculated fields to add fields from both queries together.
These calculated fields will also need to contain CASE statements so that it can handle NULL values.
I have created this report for you and attached it to this post.
Please let us know if you run into any issues with this.
Regards,
David