I am trying to make an idea work in Epicor using a bit of baq markup and a fancy where clause. Basically, trying to make a drop down that filters the approved manufacturers for a part, but if no part is entered, show all manufacturers (for some fancy BPM stuff). The sql code I have in question is:
case
when try_cast('[Manufacturer.MfgNum]' as integer) is not null
then cast('[Manufacturer.MfgNum]' as integer) = case
when '[Like:Part.PartNum]' = ''
then (-1)
else
case
when try_cast('[PartXRefMfg.MfgNum]' as integer) is not null
then cast('[PartXRefMfg.MfgNum]' as integer)
else (-1)
end
end
else (-1) = case
when '[Like:Part.PartNum]' = ''
then (-1)
else
case
when try_cast('[PartXRefMfg.MfgNum]' as integer) is not null
then cast('[PartXRefMfg.MfgNum]' as integer)
else (-1)
end
end
end
Essentially, I’m trying to check if the left joined manufacturer table has a Manufacturer.MfgNum, if it does, check against the PartXRefMfg.MfgNum for the
Where Manufacturer.MfgNum = PartXRefMfg.MfgNum.
If the part number isn’t filled in, they will both return -1 and check
Where -1 = -1, which is true for every row, showing all manufacturers.
I get a syntax error at one of the ‘=’ signs. Maybe it’s somewhere else, but I can’t figure out my issue.
Thank you all in advance!
*Edit - A image showing the code in a better format
I don’t understand what you are doing here. You have Then, “your value goes here” And they you are making that = to the next thing? That doesn’t make sense. If that’s is own case statement, then you need all of the case statement in the answer of the “Then” = (case etc.)
Yes, this is for the Where clause in the sql statement. In a where, you would normally do Where Table1.Field = Table2.Field, but in this case, I was trying to make that Where (T1 = T2) dynamic. Where (result of left hand side expression) = (result of right hand side expression).
I found this article on it, so maybe I’ll be able to rewrite it to use ANDS and ORS.
Edit* I also think that this is an equivalent equation, but it brings in no results even though it compiles through.
try_cast('[Manufacturer.MfgNum]' as integer) = case when '[Like:Part.PartNum]' = '' then case when try_cast('[PartXRefMfg.MfgNum]' as integer) is not null then cast('[PartXRefMfg.MfgNum]' as integer) else (-1) end end
I’m thinking I can get rid of the entire dynamic part of it by bringing in another part table onto the query that’s left joined, looking at the baq markup for part.partnum. This way in the Where clause, I can just compare the current markup to the column being null or not. Have to try this tomorrow. To be continued.
I think this is a simpler way to do this. You have a union between a query that inner joins the part and approved vendor, and part and vendor table but only join on Company so it’s effectively a cross join (you get all the vendors). You hard code the “count” on the approved vendor list to 0. You do a single sub select on the approved vendors and the part, and bring that in to the subquery with all of the vendors, so that if there is any approved vendors, the count will not be 0, if there are none, then that sub select will be 0.
Then you bring all of that to a top query and filter the union on only showing the rows with 0. So if the count of approved vendors is 0, you show all of the vendors from the cross join, and if the count is more than 0 (1+) you exclude the cross join and show the query with the inner join on part and AprVendor.
You can put your BAQ markup into the filter on part number that’s on the table query on the top level.
/*
* Disclaimer!!!
* This is not a real query being executed, but a simplified version for general vision.
* Executing it with any other tool may produce a different result.
*/
select
[CountAppovedSuppliers].[Part_PartNum] as [Part_PartNum],
[CountAppovedSuppliers].[AprvVend_VendorNum] as [AprvVend_VendorNum]
from (select
[Part].[PartNum] as [Part_PartNum],
[AprvVend].[VendorNum] as [AprvVend_VendorNum],
(0) as [Calculated_MyCount]
from Erp.Part as Part
inner join Erp.AprvVend as AprvVend on
Part.Company = AprvVend.Company
and Part.PartNum = AprvVend.PartNum
union
select
[Part_union].[PartNum] as [Part_union_PartNum],
[Vendor_union].[VendorNum] as [Vendor_union_VendorNum],
(((select
(count(1)) as [Calculated_myCount]
from Erp.Part as Part_count
inner join Erp.AprvVend as AprvVend_count on
Part_count.Company = AprvVend_count.Company
and Part_count.PartNum = AprvVend_count.PartNum
where (Part_count.PartNum = Part_union.PartNum)))) as [Calculated_myCount]
from Erp.Part as Part_union
inner join Erp.Vendor as Vendor_union on
Part_union.Company = Vendor_union.Company) as CountAppovedSuppliers
where (CountAppovedSuppliers.Calculated_MyCount = 0 and CountAppovedSuppliers.Part_PartNum = '12000')
