On occasion, an Epicor ERP user will want to write a report that requires a hierarchical view of a part’s multi-level bill of material. In Epicor, we would prefer to do this using a Business Activity Query (BAQ). The Epicor 10 BAQ tool is much more powerful than the tool from version 9, so it comes close to being able to do this, but just misses the mark. In order to query a hierarchical BOM, we must write the SQL and create a view in the database. Once we create the view, we can use a BAQ to access it (in version 9 or version 10 as long as we’re using SQL Server), or we can query it directly using an SSRS report. Herewith, we provide the SQL:
Category: SQL
During an upgrade to JDE EnterpriseOne you will need to list any changes that have been made to your data dictionary, and using SQL or even a UBE, most developers would have no problem coming up with a way to compare the pristine data dictionary with the latest version. We thought, however, it might be nice to give those with less experience in this line a jump start. So here is some SQL that could be used to detect new data dictionary items and changed alpha descriptions. Continue reading
Many of us would just rather not use Microsoft Access for anything, but there are those times when you are forced to do it. In times like these, you have a problem. In times like these, you need a solution. The problem is that if you write SQL that is at all interesting, it quickly becomes unreadable. If you try to beautify your SQL with some nice line breaks and indentation, Microsoft Access will “fix” that for you the next time you open your query. We struggled with this for some time till we realized that Access will not touch SQL inside of an inline view. So your solution is to put your SQL inside of an inline view. Instead of seeing this after you save and open your query:
SELECT field1, field2, field3, field4, field5 FROM table1 a inner join table2 b on field1 = field2 and field3 = field4 WHERE field1 = 'junk' and field3 = 'that' and field5 is not null
You could see this:
select * from (
SELECT field1, field2,
field3, field4, field5
FROM table1 a inner join table2 b on field1 = field2
and field3 = field4
WHERE field1 = 'junk'
and field3 = 'that'
and field5 is not null
) as a
The only problem is that Access gets confused about the parenthesis around the view and replaces them with brackets. For us, replacing the brackets with parenthesis is a better situation than manually beautifying the code all over again every time we open it.