Is there a way to create a BAQ from its SQL code?

When you create a BAQ, it shows you the SQL code right on the front tab (General tab) of the BAQ. I can copy this code and edit it locally in my preferred editor, or play around with it in SQL Server Management Studio.

But is there a way to go in reverse? What I am thinking is that I want to copy my BAQ SQL code to github so that it can be easily shared, viewed, archived, managed, etc. But then I don’t have a way to go in reverse and copy the SQL code back into a new BAQ. Is there a way to do this?

No but it’s been discussed. Go vote for the idea of you think someone should try and find a way.


And this one too:


see Epicor Idea: Log In - Epicor Identity and vote for it.

Just did, while I appreciate all of the complexity that a BAQ can handle without touching SSMS or being super proficient in SQL, it’s very cumbersome if you do know SQL.

But even for those who know SQL, how many understand the database? Are users

  • Adding company/site/territory/field security to their SQL code?
  • handling _UD tables?
  • Will they also include columns temporarily stored in Ice.PatchFld?
  • And will they remember to update their SQL once PatchFld items are migrated to their native tables?

I’m not so sure that the SQL crowd wants to be bothered with all of that responsibility, but I may be wrong. :person_shrugging:


I think it’s more of a conversion utility - converting those things we can do in SQL into the BAQ syntax that may never have known you could do. I don’t want it to bypass any of the Epicor code, just convert my SQL to a BAQ.

Oh, I think I get it. Will the conversion utility add this for us? Will SQL authors test in SSMS first? They won’t provide the same results as a BAQ unless we do the stuff above. Is the idea to get us started instead of messing with the GUI? Just quick and dirty queries? I’m trying to understand the use cases where this makes our lives easier.

I’m all for making it easier to create BAQs. Given a choice on where to put Epicor’s effort first, I must admit that I like @josecgomez’s idea to go the other way and make the BAQs’ SQL more efficient.

Make BAQs Generate Stored Procs / Table Value Functions

Something as simple as being able to take just part of another BAQ, like a subquery, and copy paste into a new BAQ would be nice. Currently, I think the only way to do that is to copy the whole query and then cut out the stuff you don’t need.

Also, (and this is for the on prem people with access to the server), being able to take a query that Epicor is performing and grabbing it via SQL-profiler or some other tool and being able to make it into a BAQ could be very helpful too, or at least save a lot of time.

I can see it being useful for 2 scenarios:

  • Quick & Dirty
  • Super Advanced
1 Like

Also, for repetitive queries/fields, where you can copy paste code to things relatively quickly in a text editor, but you have to do one at a time in BAQ land. Calculated fields that are super similar but not quite (like things with ranges, like number of days, where you need 1-10, 10-30, 30-60 etc).

1 Like

Using BAQs within BAQs satisfies this need, no?

It helps. But I believe there are limitation with that right? Can you use that feature with Updateable BAQ’s? I haven’t had time to dig into that functionality yet. Also, you might be making the calculated fields for the first time.

Good question. What’s the SQL equivalent for UBAQs where we’re calling Business Objects? :thinking:

I was going to make a tool, but alas lack of time.
I did do some one-offs where I manipulated the baq dataset and resaved it to make a ton of fields.

It was fun and useful, still not sure if I actually saved any time though.

The BAQs and the SQL are pretty separate, I don’t think anyone is advocating for update SQL to be added to BAQ’s.

If they don’t know, wouldn’t they just do the same weird thing in BAQ designer? :man_shrugging: What about BAQ designer would stop them from performing an unnecessary filter or inaccurate join apart from the intellisense/suggested joins that they’d probably delete or ignore anyways?

Might not understand the question.

If someone writes:

SELECT *  FROM Erp.Customer

and runs it, they’ll get all customers from all companies regardless of territory. If we pass that SQL into “the tool” and then run the query, users will get only the customers in the current company UNLESS the cross company box is enabled, and only customers where the user is an authorized user of the territory BUT if they are not authorized users and their workforce record has view all territories then all customers are seen.

So how do we make sure the two queries match? Again, if this is just a “get me started tool” then maybe that’s OK. :person_shrugging:

1 Like

I think perfect is becoming the enemy of done in your case. Will there be caveats? Of course, but they can at least be explained. To say we shouldn’t make this tool because the conversion won’t 100% match all of the time is short sighted.


It is, but it’s also a conversation that should be had, so it can be debated and or disclosed.