Get max record from F03B11 - Quickly

[kylev]

I am trying to get a quick way of running the following command in FDA.

“Select max(rpdoc) from proddta.f03b11 where rpco = 00001;”

I understand that I cannot run this from the FDA, however I used a WHILE statement to run through the table, but this is taking 5 min to run.

I would like to find another way of doing it as the 5 min will kill the users, or they will kill me if I bring this live.

I do not know how to program in C to create a BSFN, but I can do it in ER if there is a way.

Thanks in Advance.
E812-TR8.98.03-AS/400

 

[BOster]

JDEBASE aggregate API.

JDB_SetAggregate
JDB_FetchAggregate

 

[Adrian_Chimirel]

One quick and dirty way is the foreign table - create a SQL view and name it FSQL03B... and then add the table in E1 (no need to generate it) ... etc. (if you are not happy with the C path)

 

[peterbruce]

kylev,

Using the APIs that Brian suggested would entail C programming.
Another way to do this is using "virtual tables" in JDE.

Here is a short description of what to do, I advise you to also search JDEList for "Fake Table" and/or "Virtual Table" to get all the detail - instructions and traps - for using this method. Firstly create a database view using SQL you mentioned and name the view and it's columns according to JDE table and column naming conventions. Then create the same table structure and names in JDE.

 

[DBohner-(db)]

Are you trying to get a Record Count or the Max Document Number from the table for a specific company?

Simplified way for the Max Document number - is to create a new BSVW with the DOC 'DESC'.

If the DOC number is Descending in the BSVW - just do a simple Fetch to bring back the MAX Document Number

Fetch
CO = EVT_CO
DOC-> EVT_DOC

Since the SQL Statement will actually be similar to:
“Select rpdoc from proddta.f03b11 where rpco = 00001
Order By rpdoc DESC"

- Comprende???

(db)

 

[Zoltan_Gyimesi]

Hi Daniel,

[ QUOTE ]
Simplified way for the Max Document number - is to create a new BSVW with the DOC 'DESC'.

If the DOC number is Descending in the BSVW - just do a simple Fetch to bring back the MAX Document Number

[/ QUOTE ]

I am just curious:

On which Release/TR can you set Asc/Desc feature or define sort order on BSVW?

Do you have an index on F03B011, where DOC is Descending? If yes, is it a custom or vanilla one?

On XE SP23, In BSVW designer I can only select one of the existing indicies of the primary table.

Regards,

Zoltán

 

[BOster]

[ QUOTE ]
Are you trying to get a Record Count or the Max Document Number from the table for a specific company?

Simplified way for the Max Document number - is to create a new BSVW with the DOC 'DESC'.

If the DOC number is Descending in the BSVW - just do a simple Fetch to bring back the MAX Document Number

Fetch
CO = EVT_CO
DOC-> EVT_DOC

Since the SQL Statement will actually be similar to:
“Select rpdoc from proddta.f03b11 where rpco = 00001
Order By rpdoc DESC"


[/ QUOTE ]

This will definitely work and I have seen this technique used quite a bit.

The only problem with this is that you are asking the DB server to prepare a recordset with potentially thousands of records so you can retrieve one value. I would opt for a solution that optimizes the query on the DB server, either by using the JDEBASE APIs or a virtual table (sql view) as others have pointed out.

 

[bondlela]

This is how we do this. We create a index on F03b11 with CO, DOC(desc).

Do a select and fetch next using this index in your ER.

It should be fairly quick to retrieve the last Max doc from F03b11.

 

[DBohner-(db)]

Zoltan,
- Yes, the example was flawed, I've written an example document on the JDEResearch site that shows the complete steps to create and use the logic. Review the steps at JDEResearch's MAX (DOC) Example

Boster,
- Yes, you are correct. All rows that fit the criteria will be pulled back. Oracle does provide a fairly good example of how to use C API(s) to pull the MAX Column value at:

E1: BSFN: How to Get MAX/Aggregate using JDE API [ID 1337735.1]

Enjoy!

 

[AlexRO]

This is a very useful thread. I know I do not have any useful information to share, I just want to thank all of you for the information shared here.

Cheers,
Dan.

 

[Zoltan_Gyimesi]

Hi Sailaja,

Yes, creating new index is the trivial and simplest way, but I always consider very much to do it on a large table.
Side-effects:
- require extra DB space
- increase execution time on table operation on Insert, Update, Delete

Regards,

Zoltán

 

[kylev]

Thanks for all your help! It is much appreciated! I eventually just used an index, sorting DESC on the doc #. This works great. I know in the future, with more data, it could affect performance. But for now, the lookup rocks.

We are using it to find the largest DOC# in the F03b11, then checking the next number for invoices and making sure it is greater than the max(DOC) in F03B11. If the NN is not larger, throw up an error. It works flawlessly, and quite quick….for now. Eventually we are going to do all our invoicing through JDE, but for now the Excel invoices are going to be around and bite us in the butt every once in a while, so this will be our fix until then.

 

[ssambandam]

[ QUOTE ]
I am trying to get a quick way of running the following command in FDA.

“Select max(rpdoc) from proddta.f03b11 where rpco = 00001;”

I understand that I cannot run this from the FDA, however I used a WHILE statement to run through the table, but this is taking 5 min to run.

I would like to find another way of doing it as the 5 min will kill the users, or they will kill me if I bring this live.

I do not know how to program in C to create a BSFN, but I can do it in ER if there is a way.

Thanks in Advance.
E812-TR8.98.03-AS/400

[/ QUOTE ]