How to add a cross apply to a BAQ

[SchuylerQHorky (Schuyler Horky)]

I know splitting strings is a solved problem, look at BAQ - Split string into multiple rows to learn more.
But I still wanted to get a CROSS APPLY working in the BAQ designer and I thought I’d share with you here how I did it. Needless to say, this has the possibility to work in all sorts of situations. This is running 2023.1.9 on prem with SQL Server 2019.

This example simply creates a “STRING_SPLIT” BAQ that inputs a string with separators and returns one row per value between those separators. For example, calling this BAQ with “10,20,30,40,50,60” will return six rows, each row with one of the six values.

STRING SPLIT BAQ demo

0. Create a BAQ parameter: STR to hold the value to be split (e.g. ‘1,2,3’). Set FORMAT to x(1000) or as long as the longest string you expect.
   

   

1. Create a BAQ parameter: SEPARATOR to hold the character (e.g. ‘,’ )

2. Create an InnerSubquery called DummyQuery
   

   

3. Create a calculated column in DummyQuery. Calculated_Value = 1
   

   

4. In your top level query, create 2 DummyQuery blocks. Call them DQ1 and DQ2.
   

   

5. Create a table connection from DQ1 to DQ2. It’s very important you do from DQ1 to DQ2.
   

   

6. In the table relations tab for the connection, enter the following:
   DQ1 field or any expression: DQ2.Calculated_Value
   Operation: =
   DQ2 field or any expression: DQ1.Calculated_Value CROSS APPLY STRING_SPLIT(@STR,@SEPARATOR) a

7. In your top level query, create a calculated column.
   DataType: NVARCHAR(1000).
   Expression: a.value
   

   

When you run, test with these parameters:

You will get:

You can see, using these steps we’re tricking Epicor into adding a CROSS APPLY to your joins list.

The dummy queries really are just that- dummy’s I needed so that we could sneak in the CROSS APPLY in the join condition expression.
Have fun! Like if this helped you.

[mbayley (Michael Bayley)]

Great post! worked perfectly

I’m curious if the SQL to BAQ feature in 2024.2 would make this easier.

SQL to BAQ?

I am going to drop this here as a reminder that we ARE making some really cool features that you have always requested. This feature is coming with Kinetic 2024.2 The image below is from the Feature Spotlight video that everyone will be able to view as part of our release. Here is one of the great new features (also referenced here: Is there a way to create a BAQ from its SQL code? ) This was in Epicor Ideas with 84 votes: Log In - Epicor Identity [undefined]

[knash (Ken Nash)]

This can be done in a CTE as well.

check out the baq in this post.

SHOPLOAD - Who out there uses it, and how?

Thanks for the assist. ShopLoadRG.baq (87.1 KB) Took what you had and did a CTE to extract the hours and date from those strings. Attaching the BAQ to show the additions. I didn’t refactor the query but you get the idea. Now I am going to create a summary layer, but that should be easy to do.

[kananga (John K)]

@mbayley

I’m curious if the SQL to BAQ feature in 2024.2 would make this easier.

If this gets attention from the right folks there’s a chance that the hardcoded string “cross apply” may (incorrectly) get whack-a-mole’d into input sanitization at some point. Can’t say I’d recommend depending on this in production.

Do be cautious about string variables large enough to contain mischief, at least until BAQ uses SQL side parameterization and adopts a least privilege approach.