I have discovered how to add aggregates (e.g. sum(), max(), etc.). Now I need to insert a innersubquery in the SELECT statement, similar to below. Help would be great…
, case when n.SN not in (
select n9.SN from OrderItemSN n9 left join OrderItem oi9 on oi9.ID = n9.OrderItems_ID
where n9.ShippingGroup is not NULL and oi.WorkOrder = oi9.WorkOrder
) then o.TrackingNumber else g.TrackingNumber end myTN
The above demonstrates a simple “case when column LIKE” but what I really need is a “case when (select column from table) then 1 end” statement. I tried the statement below but I’m getting a SQL Injection warning and the script will not run. It’s time to research injections. Help would be appreciated…
case when ECOMtl.PartNum in (
select ECOMtl.PartNum
from Erp.ECOMtl as [ECOMtl]
where ECOMtl.PartNum like ‘200-02[0-9][0-9]’)
then 1 end
You cannot use your own SELECT/FROM/WHERE in a BAQ. For your latest issue, it’s a matter of a rewrite of the query.
CASE
WHEN ECOMtl.PartNum LIKE '200-02[0-9][0-9]' THEN 1
END
Your original query is doing twice the work it needs to.
My original query has this:
case when Part.PartDescription like ‘%hook%’ then 1 end
which is similar to your answer.
The (select ? from table) example is “too verbose” for the purposes of discovery and testing. I need an example where the “case when” performs a internal query on a table:
select case when (select column from table) then 1 end
BAQ tries to do some SQL injection sanitization. Part of that is broadly prohibiting “select” in user entered text.
Even if you could, running a query as a select statement is a good thing to avoid. Think about it, you’d be running that query for every single row that’s returned. That’s a performance hit that might be okay at first but can tilt exponential with increasing data.
Better to join the referenced data if you can.
If not, plan B would be a subquery (aka, “inline table expression” outside the Epicor bubble). Something along the lines of (just copying from your query text here…):
select distinct
n9.SN
,[...any other necessary join fields, ie, "company"]
from OrderItemSN n9
left join OrderItem oi9
on oi9.ID = n9.OrderItems_ID
where n9.ShippingGroup is not NULL
and oi.WorkOrder = oi9.WorkOrder
Left outer join that subquery. If all you’re looking for is a matching SN it’s easy to get a 1:1 match to avoid using distinct where you don’t have to. Then you can say
case
when <subquery>.SN is null then 1
else null
end
Thank you, John!
I was handed the task of learning Kinetic / Epicor / ERP / BAQs / etc. just this past Monday… “as fast as you can”!
Anyway, I have since discovered how to create InnerSubQueries (properly?) and I believe I’m close to what you’re suggesting. I added another SubQuery, brought in ECOMtl again, calling it ECOMtl2, and created a single Yes/No (bit) display field in it:
case when ECOMtl2.PartNum like ‘200-00[0-9][0-9]’ then 1 end
I now see, in the BAQ / SQL script textarea:
blaa,
blue,
[find_200_00xx].[Calculated_is200_00xx] as [Calculated_is200_00xx]
In the “Test Entire Query or Switch To…” label, I test the 2nd subquery: find_200_00xx: Subquery: InnerSubQuery
It tests perfectly, giving me rows of ONLY the Yes/No checkboxes, all checked.
But when I “Run Query” the TopLevel SubQuery, it gives an error. It tells me to consult the server event log. Any hints where I can find that?
As for repeatedly executing a select stmt in the main query, I understand what you’re saying, and I’ve often wondered why it works so swimmingly… I’ve written hundreds of these “case when (select blah from table) then blah end” stmts and they all return tens (if not hundreds) of thousands of rows from several tables containing one-to-three million records each in a mere one-to-three seconds (which is great for our purposes). I can only guess that SSMS / TSQL has some fabulous optimization going on behind the scenes.
I will also research inline table expressions, “thank you very much” !