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.

I know since we posted the JDE date to real date function, everyone has been wondering where the real date to JDE date function is.  Well, “ask and you shall receive.”

Read the rest of this entry »

As long as we are discussing JD Edwards running on DB/2. Here’s another potentially useful function.  This one converts a value from one unit of measure to another based on the F41002 table.  It does this by converting the FromUOM to the primary UOM and thence to the ToUOM.

Read the rest of this entry »

During nine years of blackguarding the J. D. Edwards date format (also known as JDate, also misnamed Julian Date),  I have resisted learning to write DB/2 stored procedures because the need to use them while developing OneWorld applications is minimal and OneWorld is the only context in which I have encountered DB/2.  Well, I have finally encountered that special confluence of  necessity and available time that produces a tool — in this case a function. I broke down, I learned, and I wrote that JDate to DB/2 Date function. Here is that code, in case you should find it useful:

Read the rest of this entry »

We here at Deft Flux have always preferred Unix style operating systems and commands to Microsoft.  The problem was that our clients always use Microsoft operating systems and until recently, it was always too much of a hassle to get gnu utilities up and running on a Microsoft workstation, especially when you’re moving to a new one every few months.  Well, since the good folks at gnu started compiling programs for windows and providing installation programs (to cover dependancies), we decided every workstation needs gnu utilities.  First, we install gvim because it is the best text editor ever written.  Then we install grep, core utilities, and finally sed.  Thus we have assembled, on a Microsoft workstation, a group of what are arguably the most useful programs ever written, pound for pound.

Read the rest of this entry »

If you have ever used the JDEdwards cross-reference facility in an attempt to find where a certain table column is used, you understand that it can be difficult, especially when the alias for your column is common.

Let’s say you have a custom table [F550001] that has a column [URRF], and you need to find all programs that use the F550001.URRF. Well, you may have 50 custom programs that use F550001 and there may be 250 programs, native and custom, that use the alias URRF. You can see the difficulty in searching through 50 programs to find 5 that reference F550001.URRF.

If, however, you take the 50 custom programs that reference F550001 and export them to the spreadsheet program of your choice and then export the 250 programs that reference the URRF to another spreadsheet, you can filter the 50 references to F550001 down to only those programs that also reference URRF. Now, you still may have a few extra programs in your list — programs that don’t reference F550001.URRF — but you now have a much smaller list to work from.

Hello world.  Deft Flux is now a real company.