Hi guys, this is probably simple but I cannot work it out. I have a Union BAQ which works, but the data is not displaying in the order I expected it to, I have tried changing the order in the subquery list but I then get an error. basically, I want the Quotes (Top level) to display before the Orders (Union).
try changing your current “top level” to a “CTE”
then make a new “top level” and add the CTE to the design pane like you add a subquery.
then do the sorting in that new top level.
Hi Darius, thanks for the response. I am taking your advice and attempting a CTE query. I have done a bit of reading and I think I need three queries, a CTE, a Union All and a Top Level. My CTE looks like this,
Am I right in thinking that my Union All would basically be basically the same query joined to the CTE on the canvas?
What I’m after is to see all the Quotes with multiple orders attached in the format of, quote details line followed by orders attached detail lines then the next quote details line etc. Is this the way to do it? Can it even be done?
Thanks for your help.
Can you put your query back like it was and show us a picture of your subquery list?
Hi Kevin,
My original query was a Union one which had a Top Level identical to the one above, with sales order fields set to null. The Union was the same query with the quote fields set to null. It worked ok when only one quote was returned, it displayed the quote data with all attached orders below, but when I tried more than one quote it displayed all the quote lines then all the order details below. I am trying to list quote, then attached orders (eventually with a sum of the order values), then the next quote with all attached orders, and so on. I did play around with advanced grouping but to no avail.
It’s the nulls and your joins causing a problem.
No need for a union, the query above should work like you want with default link criteria if you change the
relationship between quotehed and orderdtl to a left join. (all rows from quotehed)
Hi Kevin, I tried what you suggested, but it’s still not looking how I want it to,
As you can see I have three quotes here. What I am trying to achieve is, line one showing the quote details, then on the lines below just the order numbers and values (not repeating the quote detail), then the next quote, followed by the order numbers and values, and so on.
yeah, what you are really looking for is a full outer join, you want to see all quotes with or with with out orders and you want to see any orders that do not have quotes. unfortunately, I have never been able to get the full outer join to work in a BAQ, so I think you were on the right track with the Union.
To answer question on the CTE: don’t change anything in the design of your “top level,” don’t change anything in your “Union All”. Simply change the Query top of your “top level” from “top level” to “CTE”.
then add a new subquery as a new top level. In the design pane for your new top level select one thing: that “CTE” query which was previously the top level.
display the fields you want in that top level, sort it the way you want and you should be good.
Ok, so I’m back on this today. I have reverted back to a Uninion query, which as you can see is returning three quotes, correctly, with all the related orders correctly, but they are not listed beneath the related quote as I want them. Like Quote, Related Orders, Quote, Related Orders, Quote, Related Orders.
Any ideas?
You should be able to put an order by on the top level. You just need a column to order it by. Do you have the quote number in all of the levels?
Thanks Brandon, I replaced the null on the quote number in the Union query with the quote number and then I get what I’m after.
As a footnote, is it possible to sum the order value column for each quote, so we can quickly see the total value of orders against the quote?
my specialty!
This is where you can use windowing functions.
You’ll probably have to wrap everything from your top level to the subquery. (you’ll need to use () in the subquery table stuff)
Then once you get that, bring that into your new top level, and you can add a calculated field like this.
sum(columnYouWantToSum) over (partition by QuoteNumber)
This will add a column with the totals of the quote. All of the rows for the quote will be the same. But it’s a way to get the total in there.
you might be able to get fancy with some case statement to show the amount or not. But a number will not show empty, it will only show 0, so you would have to get even fancier with trying to use a character field and converting… Probably not work it.
I’m gonna remember that. I have a gl report I had to do a few func overs, that worked well until Oct 1, when Epicor decided to break some summary info out into extreme detail. Gonna have to start over and might need some help!
