How can you use the parameters in a CTE? That is, I want to create the list of values for the parameter like it is a table. If I did it in SQL it would be something like this:
WITH PartList(PartNum) AS (
SELECT * FROM (
VALUES ('123456'), ('100000')
)
AS V(PartNum) )
...
Except here, the values are coming from the parameter (let’s call it @thePartNums). In the Phrase Build tab I don’t see how to select a list of parameters directly as if it were a table.
In SQL I could do a similar thing with a join, e.g.
SELECT *
FROM Erp.Part
JOIN (
VALUES ('10-0101-6'), ('100')
) AS TableName(ColumnName)
ON TableName.ColumnName = Part.PartNum;
But then the question is how to use the parameter values as the VALUES in the join.
These are of course toy examples, but just to illustrate what I need to build my larger query.
I saw this thread, but it only shows how to use a list of values in e.g. the WHERE clause and not as data to the query.
Sorry, I’m not following where I am supposed to do this… I’ve got a CTE subquery but it’s not clear to me how to reference the parameters. They aren’t part of a filter so the tabs at the bottom don’t seem the place to do it.
In a BAQ, the lowest level query runs first. Sounds like in your case, it will be the CTE. Put a filter on the table with the parameter and your CTE will only contain the records you want, which means that any other query that is using the CTE results will be filtered.
So to explain a bit more what I’m try to do, I have a SQL query that I’m trying to convert to a BAQ. But the SQL is being dynamically generated from a list of parameters. Each parameter is generating a term in the WHERE clause.
What I want to do is something like this:
WITH PartList(PartNum) AS (
SELECT * FROM (
VALUES ('123456'), ('100000'), ...
) AS V(PartNum)
),
MatchingParts AS (
SELECT <fields>
COUNT(DISTINCT PartList.PartNum) AS MatchCount
FROM Erp.Part AS my_Part
LEFT OUTER JOIN dbo.PartXRefMfg AS PartXRefMfg
ON ...
LEFT OUTER JOIN Erp.Manufacturer AS Manufacturer
ON ...
LEFT OUTER JOIN Erp.VendPart AS VendPart
ON ...
JOIN PartList
ON <field> LIKE '%' + PartList.PartNum + '%'
OR <other terms>
GROUP BY my_Part.PartNum, <other fields of interest>
)
SELECT PartNum, <other fields of interest>
FROM MatchingParts
WHERE MatchCount = (SELECT COUNT(*) FROM PartList);
So this is supposed to return all parts that match all values. This is just my initial attempt - the end result will be more complicated as there are different types of filters and each must go in a different parameter as a different set of values, but I’m just trying to prove the concept at the moment.
Now to convert this SQL to a BAQ the main thing I don’t know how to do is get the values into the CTE as a table so that I can use it in subsequent parts of the query.
Indirectly. A front end web app allows a space separate list of terms. These are then split into different search types, for instance one type is for part numbers, another is for looking for values with tolerances, etc. These are then sent to the back end which is validating the data and is constructing a call the the BAQ service via the API. The idea I have is that each list of terms will goes into a separate parameter as multiple-values for that parameter, which I then need to use in joins to emulate the behavior of an arbitrary number of terms compared (which previously was done via constructing the SQL dynamically).
So, it sounds like each “search type” will need it’s own CTE with a filter parameter that will be determined by the front end. I would have each CTE output the part number and then you can do a union to get to one list and then use that as the base for the end report.
Yeah exactly, each search type will need its own CTE.
I’m not following what you mean by “have each CTE output the part number”. I need each CTE to output the entire list of part numbers for that parameter so that I can join that list of part numbers to the list of candidate records and have the ON statement perform the comparison logic.
I can’t see a way to do this with just using @thePartNums as an expression in a filter (e.g. SubQuery Criteria) because this is just selecting records from an existing list and unless I had a list that contained all possible part numbers (ha!) I can’t get a filter to return all the part numbers present in the IN expression list.
I’m recommending only outputting the part number for each CTE as I am guessing that at least one of the CTEs will not return results. You may have one search type that will not have any filters applied, which should result in a blank table for that CTE. If you just output the part number (and rev or whatever key field you need), then you can do a union which will give you the full list of part numbers that you need. Then use that CTE result as the main table for whatever the end output is.
Actually the outer term combining everything is an AND so every search term must match on that particular part in order for the part to show up in the final result. This is why I used the CTE twice in the SQL above, and then make sure at the end that MatchCount equals the number of terms.
So as an example, the search terms for the first type might be “10-”, “-6”, "51’, which means the part number or any of the other fields search must contain all of these substrings for that part to match.
Sounds very convoluted. I’m assuming you have “intelligent” part numbers? Not knowing the details of what you are actually doing makes it hard to provide a solution.
Don’t know if you can post the SQL from the existing, but that might help us.
The above SQL is basically turning dynamically generated SQL into a fixed query. So the previous code constructed SQL in a string by taking each term and then joining together the strings with AND. So a snippet of this:
var where_terms=terms.map((x,i)=>{
if (<case 1>) {
q = `
(my_Part.PartNum LIKE '%' + @PartNum${i} + '%' OR my_Part.PartDescription LIKE '%' + @PartNum${i} + '%'
OR PartXRefMfg.MfgPartNum LIKE '%' + @PartNum${i} + '%' OR VendPart.VenPartNum LIKE '%' + @PartNum${i} + '%'
OR Manufacturer.Name LIKE '%' + @PartNum${i} + '%')`
}
return q
}).join(" AND ")
By creating a literal table in the WITH statement containing the terms, we can then join this to our part information table ON the above conditions.
The part which I cannot figure out is how to essentially create this literal table from the list of values in the parameters. I just am not seeing a way to do it.
So, if you need to get a list of part numbers that meet all of the above, why not create 5 CTEs for each search type.
with PartNum(partnum)
as
(
select partnum
from Part
where partnum like %x%
),
PartDesc(partnum)
as
(
select partnum
from Part
where partdesc like %x%
)
MfgPart(partnum)
as
(
select mfgpartnum
from PartXRefMfg
where mfgpartnum like %x%
)
VendPart(partnum)
as
(
select venpartnum
from VendPart
where venpartnum like %x%
)
MfgName(partnum)
as
(
select partnum
from Manufacturer
where name like %x%
)
Then you can do another CTE to join the output from those to get a list of parts that only exist in all tables. Then use that CTE output to run your TopLevel query.
Well let’s keep it simple and figure the first CTE out before I try to add any more…
I’m not sure what %x% is referring to here… but if I do something like this:
SELECT partnum
FROM Erp.Part
WHERE PartNum IN @thePartNums
It won’t work because @thePartNums is a list of (in this case) partial part numbers.
So if for example @thePartNums = [ ‘10-’, ‘-6’, ‘51’ ] then the select would actually have to look like this:
SELECT partnum
FROM Erp.Part
WHERE PartNum LIKE '%10-%'
OR PartNum LIKE '%-6%'
OR PartNum LIKE '%51%'
So the number of terms would be equal to the length of the @thePartNums list. So to avoid dynamically creating the SQL statement (which we can’t do in a BAQ anyway) I’m try to set it up like this:
WITH PartList(PartNum) AS (
SELECT * FROM (
VALUES ('10-'), ('-6'), ('51')
) AS V(PartNum)
),
SELECT *
FROM Erp.Part
JOIN PartList
ON Part.PartNum LIKE '%' + PartList.PartNum + '%';
This works if I run it as SQL against MS SQL Server 2016.
But what I can’t figure out is how to turn this into a BAQ - specifically the part about introducing the literal VALUES construct into the query.
Honestly I think you’re stuck using a recursive CTE to UNIONLIKE PartNums from Erp.Part, popped through another table expression to DISINCT the output because recursive CTE’s require UNION ALL. That’ll return a set you can join on whatever the part you’re applying it to is.
I think I figured out a trick using [Ice].entry() that might work, though it might get kind of convoluted still.
You can set up a small CTE to generate a table of row numbers to start with, let’s call it ListGen:
Pick a table guaranteed to have as many rows as you need (how many options can users pack into that comma-separated parameter?), and pull it in. Under SubQuery Options, go with Result Set Rows: Top and set the Rows Number to that amount.
Under Display Fields, add the Company column from the table you picked and then a calculated integer field (let’s call it RowNum):
row_number() over(order by <table>.<some ID column>
e.g.,
row_number() over(order by PartTran.TranNum)
This CTE should be able to be shared by however many things you need to check, as it’s basically just providing a table of the current Company ID and some row nums. Make another CTE to split the parameter with your list and generate a table with the individual items in the list (let’s call this CTE SplitList):
Pull in the ListGen subquery.
For Display Fields, bring in that Company field and make a new calculated field, nvarchar of whatever size is the longest any one item in the list parameter can be:
[Ice].entry(ListGen.Calculated_RowNum, @q, ',')
-- @q is the BAQ parameter with a comma-separated list of strings
-- any wildcards to be used with LIKE need to be included
-- something like '%123456%,%100000%,%etc%'
-- or they can be added in this calculated field
Back to the Phrase Build tab, down below SubQuery Criteria, add a criterion like: Calculated StrPart <> '' expression to filter out blank rows when @q contains fewer items than the TOP n ROWS number.
Now you can bring the SplitList CTE into any other subqueries and join against it using a LIKE operation or whatever:
