Hi,
We have encountered a requirement from our client where we have to take data from two tables in the same SQL query parallely... something which the UNION clause of SQL.
To define the problem more, suppose I have the following two tables:

1) Loan disbursement details
Fields: Employee id(pk), Loan Id(pk), Disbursement Date(pk), Amount

2) Loan Repayment details
Fields: Employee id(pk), Loan id(pk), Repayment Date(pk), Amount.

Now, an employee can be disbursed a loan on multiple dates and the employee can return back the loan on multiple dates.

NOw if I want to write an SQL query which should scan both the tables parallely and get an output chronologically as per the transactions on a loan. Something like a "loan register".

Now, as I think this could be solved if BaaN SQL had UNION kind of thing. Had there been a summary table which could have contained a combined transactions of both the tables, it would have been easy. But, I do not want to create anotehr table replicating the data of these two tables.

Does anyone have an idea how to solve this?

Thanks & Regards
Manu

What about sending the details to the report from both tables and using the report to sort using the same extern field
being populated by the disbursement or repayment date.

You can distinguish between what the date represents
by assigning a type.

just a suggestion, maybe you can explore using reference options.

Refer to the links -

References (http://www.baanboard.com/twiki/bin/view/Progmanerp)


<mode>
A mode indicating system action if reference does not exist; possible values are:

SKIP If a reference cannot be found, the record is skipped.
CLEAR If a reference is empty or absent, the referring record is filled with spaces or 0 (numeric).
SETUNREF The value of an undefined reference is filled with an 'undefined reference' sign, defined in the data dictionary, or with 0 (numeric).
CLEARUNREF The referred record is filled with spaces or 0 (zero) when reference fields are empty. When the reference is undefined the referred record is filled with an 'undefined reference' sign.

Depending on the reference definition in the data dictionary, the default reference mode is:

reference mode in DD UNREF mode
mandatory SETUNREF
mandatory unless empty CLEARUNREF
not mandatory CLEAR


SQL trace options (http://www.baanboard.com/programmers_manual_baanerp_help_functions_database_handling_sql_trace_options)

Dear BaanTech & NP,
Thanks for the help.
I knew about the report sorting option but cannot exercise this because of some peculiarity based on the requirement of the report and have to do this at the program script level only.

Moreover, the reference mode is useful when the tables are in master-detail level.

Here since I have to deal with both the tables parallel, I think this would not serve the purpose.

Thanks & Regards
Manu

Manu,

just wondering if you tried to explore using - on.main.table() (http://www.baanboard.com/programmers_manual_baanerp_help_functions_db_operations_on_main_table) function ?