can i run sql query in the beginning of a report?

[wsam7]

I want to run a sql query before the report run, to tidy up the data in the table.

how to do this?
what i want to is truncate the table(is a temp one) and insert new data in and display it in the report. Can i do this?
and how?

thank you very much.

From,
Samuel Wong

 

[Zoltan_Gyimesi]

Hi Samual,

Generally you can not execute an SQL query from an UBE, because there "Run Executable" system function is not available - as far as I know on all SP level.

Maybe you can do it the following way:
1.) Create a dummy OW/E1 table
2.) Create REAL SQL trigger on that table, which executes your logic
3.) Issue e.g. an insert to the table from the UBE - you can here pass paramaters to the SQL query.
4.) Wit in a while loop, while SQL query does not change a status in the inserted record or do not delete it, notifying the UBE, that the SQL had done its job.

On the otherhand:
Q1.) Why don't you can do all the logic in an UBE?
Q2.) What is your platform and system configuration?

Regards,

Zoltán

 

[Zoltan_Gyimesi]

Hi Samual,

Ooops, I missed two other possible solution:

S1.) Via B34A1030 "Excute External Program, if it is available on your system.
S2.) Writing your own C wrapper BSFN, which executes the SQL.

Regards,

Zoltán

 

[eydeak]

If you are talking about a custom report, put code in the Initialize
Event of the first section to delete the records in the table and then
execute the query you want to run. If you are talking about base JDE,
then create a driver UBE that executes your SQL statements and then
calls the base JDE UBE.



If you don't know how to execute select/insert/update/delete from a UBE,
I suggest you read the section on Table I/O in the JDE Help. (Contents -
OneWorld Development Tools - Event Rule Design - Table I/O)



Ellen Deak

Senior Analyst/Programmer

Cooper Standard Automotive

OneWorld Xe Update 7 SP22_T, AS400 DB2 V5R2M0