Execution settings Where clause, probably more of a SQL question probably but

,

Calling all SQL Masters…

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

You have the else statement with a comparison. You need that to be “Then” and do your comparison. That’s your syntax error.

the else is supposed to be the final Where return

Else, “Where -1 = Value of the Case”

Then you are missing the case where

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

Thanks for the help so far.

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')
1 Like

Thanks Brandon. Implemented it very similar to this approach. I was using manufacturer not vendor, but the principle still works after some tweaking.

For anyone interested in the results, probably a cleaner way to solve it but this works.
ResourceManufacturersOnPart.baq (61.0 KB)

1 Like