Category: SQL

Ask Deft Flux: How do I Query a Hierarchical Bill of Material in Epicor?

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:

Continue reading

Data Dictionary Changes – JDE EnterpriseOne

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

Defeat Microsoft Access’ SQL Reformatter

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.

© 2018 Deft Flux

Theme by Anders NorenUp ↑