To all:

Unix/Oracle/Apps 8.0.3/Env 8.0.3

Need to get a max value from a field in the database to then do a +1 to populate the existing field on the current Lawson form.

Is there any way to do embedded SQL in Lawson Cobol to pull back this max value?

Thanks in advance

You can embed a SYSTEM call as shown in this example:

So then you can write a small script that runs your sql call and returns the value ...

But you should be able to get to the max value by creating a custom index on that table or use 870-FIND-PREV-Index after searching for HIGH-VALUES to the existing index. If that's confusing I can elaborate.

I know I can go the index route - but we have so many custom indexes as it is and it's a major headache to add another one because of the dbreorg etc. We're 24x7 and downtime is hard to come by.

Where does the value get returned? Sorry if it's something simple I should have seen!

I really appreciate your help,
Laura

Ah jeez I knew you were gonna ask. Honestly I don't remember the EXACT syntax off top of my head but I believe its something


CALL "system" USING WS-COMMAND RETURNING INTO WS-WHATEVER

or

CALL "system" USING WS-COMMAND GIVING INTO WS-WHATEVER

I am leaning towards the second one.

OR maybe WS-RETURN is the variable that actually gets the return without any diff in syntax. Its been a while.

I would test to see if there is anything in WS-RETURN after you run the CALL command.

I thought I had an example somewhere around but can't find it.

What do you think of the 870-find-prev ? Any reason why you can't use that?

Another index is a good idea - I had thought of that - but as I said downtime is precious and hard to come by in the Lawson world. We run 24/7 and this is our busiest time of the year...

thanks - I'll give you other suggestions a shot

To all - FYI

I got it to work. I had to create the script, spool the output to a file from within the SQL script.

Then I opened the file in my Lawson program and read the record to get the information pulled from my SQL query.

Kind of hokey - but it works!

Thanks for your input.

I hate to hijack a post but sense the answer seems to have been found I can't resist. Like I said in my intro I am relatively new to Lawson. This includes writing shell scripts as well. In the quote above it was mentioned about running an SQL command from a script. PLEASE TELL ME HOW if it is possible from a shell script. We aren't allowed to create custom indexes here and that can get really annoying. Any help is appreciated.

mmrobinette - You may also be able to execute a SQL statement from within the 4GL program itself. Lawson's upgrade programs make limited use of a special database routines that actually execute the equivilant of SQL select statements. You can find the documentation in the "eapi-w" manual (Application Program Interfaces) - document number EAPI-803W-02 (or, EAPI-803U-02 I'm sure for Unix). Look under the "Index Filter Processing".

Use 880 Aggregate functions to find out max or min or average value of field , it will return the value but not the whole record
it does not work on date field ( defined in dbdef)