Hello
Working with CR8 and Oracle DB
I am trying to show the movements of goods we buy and sell.
I have two tables organized in the same way.
Table 1: PURCHASE ORDERS
PUR_ORDER# ITEM# PUR_QUANTITY PUR_PRICE PUR_DATE
1 DA019039 30 12.50€ 2009/01/02
1 DA025003 12 4.00€ 2009/01/02
36 DA019039 20 12.80€ 2009/03/25
Table 2: SALES ORDERS
SAL_ORDER# ITEM# SAL_QUANTITY SAL_PRICE SAL_DATE
6 DA019039 3 21.50€ 2009/02/12
14 DA025006 1 7.00€ 2009/02/24
25 DA019039 4 21.50€ 2009/04/01
I would like to get a report like
(GROUP)ITEM# DA019039
MOVE_DATE MOVE_TYPE QUANTITY PRICE
2009/01/02 PUR 30 12.50€
2009/02/12 SAL 3 21.50€
2009/03/25 PUR 20 12.80€
2009/04/01 SAL 4 21.50€
(GROUP)ITEM# DA025003
MOVE_DATE MOVE_TYPE QUANTITY PRICE
2009/01/02 PUR 12 4.00€
....
I do not know how to organize the links between tables to get to that result. If I only link on ITEM#, I get duplicated lines for every movement, and I do not know how to sort by date as dates come from two distinct tables.
Any help welcome.
Thanks in advance.
Working with CR8 and Oracle DB
I am trying to show the movements of goods we buy and sell.
I have two tables organized in the same way.
Table 1: PURCHASE ORDERS
PUR_ORDER# ITEM# PUR_QUANTITY PUR_PRICE PUR_DATE
1 DA019039 30 12.50€ 2009/01/02
1 DA025003 12 4.00€ 2009/01/02
36 DA019039 20 12.80€ 2009/03/25
Table 2: SALES ORDERS
SAL_ORDER# ITEM# SAL_QUANTITY SAL_PRICE SAL_DATE
6 DA019039 3 21.50€ 2009/02/12
14 DA025006 1 7.00€ 2009/02/24
25 DA019039 4 21.50€ 2009/04/01
I would like to get a report like
(GROUP)ITEM# DA019039
MOVE_DATE MOVE_TYPE QUANTITY PRICE
2009/01/02 PUR 30 12.50€
2009/02/12 SAL 3 21.50€
2009/03/25 PUR 20 12.80€
2009/04/01 SAL 4 21.50€
(GROUP)ITEM# DA025003
MOVE_DATE MOVE_TYPE QUANTITY PRICE
2009/01/02 PUR 12 4.00€
....
I do not know how to organize the links between tables to get to that result. If I only link on ITEM#, I get duplicated lines for every movement, and I do not know how to sort by date as dates come from two distinct tables.
Any help welcome.
Thanks in advance.