B9600430 – Dynamic SQL

Request – Within a Find/Browse form, a user would like to be able to search all records in a custom table that have string field “OBNM” equal to “P01012” OR “P004A”. While this task would be easy to complete using an SQL statement, it seems fairly tricky to do in JDE.

Potential Solution 1 – Apply the hide/show method.

I could just create two fields on the form. Then I could use the filter on the first field, and apply the hide and show technique in the “Grid Record is Fetched” event for the second field. (Show the records that match the second field and hide the other records.) Although this method works, it’s just too slow due to the fact that my custom table has 1.7 million records.

Potential Solution 2 – Use the Set Selection Criteria in the “Post Dialog is Initialized Event” method

Although this method seems like it should work, I couldn’t get it to match my requirement. Actually, I couldn’t get my “Set Selection” statement to work at all. When I inserted the statement in the “Post Dialog is Initialized Event”, and ran the program, it would hide all of the values in the field that I was doing a set selection against. (Yes, I put the “Set Selection Append Flag equal to “yes” statement before it.) Plus, it wouldn’t even filter on the correct records. This issue may just be related to 8.9 though because I believe I used this method successfully in older versions. (FYI – this method did work when I used it in a mock report.)

Potential Solution 3 – Use the B9600430 business function to dynamically create the SQL statements.

This is a new method that I found on JDELIST that looked like it might help me complete my task. Unfortunately, I’ve never tried it before. I looked at the P96400A application and tried to decipher how this bf could be used. I found that if I called the “Init Selection”, “SetSelection”, “OpenTable”, and “Add Select” business functions in sequence, that it will create the proper SQL statements in the debug log. The only problem is that the application still calls the main SQL statement afterwards. So the last SQL statement wins, and the application does not display the correct information. I tried to disable the base form SQL by putting a “Stop Processing” event on the “Grid Record is Fetched” event, but that didn’t work either.

Does anyone know how to disable the SQL statements in a grid? Has anyone every successfully used the B9600430 method on an application?

Thanks,
Kip.

8.9 SP2

Zero experience with this BSFN. But is it possible your issues with both solutions 2 & 3 are with the event you're placing the ER? Instead of Post Dialog Initialize, perhaps better served at Find Button Clicked? You said the last SQL wins, maybe you can make yours the last one.

For your potential solution 3:

To bypass to JDE SQL, simply don't put the JDE Find button on your form. Create your own find button. The ER on your find button with call the "Select" function in the B9600430. This will issue a select to SQL based on the "AddSelects" that you did prior to the "Select" call.

Once you do the Select, then you must manually populate the grid with the results of the "Fetch" call. This is the tricky part. On your Grid, there is an even call "Get Custom Grid Row". Use this event to manually populate information into the grid. On this event, call "Fetch" to get the data from each fetched row as follows:

1. In the ER for this event, call B9600430 Fetch. This will fetch the next row into the API buffer.
2. Use the B9600430 APIS to get individual field data (e.g. Get String Column will get the string column value for the DD field that you want.)
3. Set the GB variable to the value of the fetched field.
4. Call System function: Insert Grid Buffer Row (
FC Grid, VA frm_count, <Yes>, <No>, <Yes>, <Yes>, <No>)
5. Increment VA frm_count. This is the row count for the manual insert. Clear VA frm_count when you press your custom "Find" button.

Hope that is not too scary.

Best to try on a "test" app with minimal ER. Then try on "real" app.

Good luck!