BAQ "subquery" help

Kinetic ERP
1

I’m sure this answer is on here but I am struggling to find what I need, sorry :frowning:

I want to do an old school summary on a separate table SQL thingy. something I often thought of as a “Sub Query” but now I am not sure.

I want to write a BAQ to summarize the Quote Line Engineering, Labor, Burden, and Material costs into a single BAQ by Quote Number.

In old school SQL it would be something like:
SELECT QuoteDtl.QuoteNum, QuoteDtl.QuoteLine, QuoteDtl.PartNum, (SELECT SUM(QuoteOpr.ProdStandard * QuoteOpr.ProdLaborRate) AS ProdTot FROM QuoteOpr GROUP BY QuoteOpr.QuoteNum, QuoteOprQuoteLine WHERE QuoteOpr.QuoteNum = QuoteDtl.QuoteNum AND QuoteOpr.QuoteLine = QuoteDtl.QuoteLine) AS ProductionTotal FROM QuoteDtl WHERE QuoteNum = @QuoterNum

Only focusing on Production Labor total in this example, but I would also hit burden and material with similar embedded (SELECT) type statements. When I try to do this in a formula field in the new “Kinetic” BAQ interface it tells me I may be doing some SQL injection :frowning:

I have tried my hand at SubQuerries but I am not getting what I want out of that when attempting InnerSubQuery or CTE though honestly not sure how to get fields from a SubQuery into the top query without it erroring on me. Not sure any of the Union type querries are the right one for my situation, but maybe I am wrong?!

PS Thanks in advance. Also I was hoping to do this in the new Kinetic interface, feels like we will need to learn to embrace this new interface and right now I am more familiar with the new interface as I am pretty new to Kinetic.

2

You maybe able to do it with windowing. The calculated field would be something like: SUM(QuoteOpr.ProdStandard * QuoteOpr.ProdLaborRate) OVER (Partition By QuoteDtl.QuoteLine)

Another would be making the subquery in the BAQ designer. Are you on Classic or Kinetic? I know Classic BAQ designer better so could help there.

2 Likes
3

As an aside, the inclusion of a written “SELECT” is what is causing the SQL Injection error.

2 Likes
4

I am trying to do this in Kinetic version, I would have to see if I can edit the menu to open up the Classic BAQ interface.

The OVER option listed isn’t working as it wants me to GROUP BY on ProdLaborRate and ProdStandard, the point is I want to Summarize the Labor information. I had it all add for Labor and Material, the problem I had with that one is that since I am linking QuoteOpr on QuoteDtl AND QuoteMtl on QuoteDtl so my amounts get duplicated because I am linking multiple tables to the base table.
I have written two individual BAQs and each one by itself is good, just can’t seem to get them in a singular BAQ.

5

@GThom idea is probably the way to go.

6

Not much luck simply removing the SELECT, it is now getting stuck on AS, WHERE, FROM…

7

Don’t use AS that comes in automatically since you name the calculation in another place.

8

second select must be an inner subquery with where clause, then you add it as calculated field to the first one

1 Like
9

OK I was thinking this, but struggle with what type of SubQuery this is and how to write the “WHERE” statement and then how to add the field at the top through a Formula. I did have luck getting a CTE and InnerSubQuery not erroring maybe I just need to figure out the Formula part.

10

What designer are you using? If in the browser, then there was a missing feature restored in 2025.1 to create where clause for correlated subquery. Try in smart client or in 2025.1

1 Like
11

Very doable.

You need to create a subquery. ( you will need to manually enter the join to the Top Query fields)

In your Top Query call the subquery as a calculated field.

12

Check out this post.

1 Like