Can any one look into this????
Moderators: chulett, rschirm, roy
Can any one look into this????
Hi
We need to extract the data from multiple tables of different structure.There are three tables with different columns,we need to extract the two columns from first table,two columns from second table and one column from third table .
can any one suggest which stage I have to use to read the data from these tables?
We need to extract the data from multiple tables of different structure.There are three tables with different columns,we need to extract the two columns from first table,two columns from second table and one column from third table .
can any one suggest which stage I have to use to read the data from these tables?
You don't have much of a choice of stages - and that choice depends upon which database you are using. Do the 3 tables have a common key or column used?
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
Have you though about just using 3 Oracle Enterprise stages and linking the 3 streams using a funnel stage?
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
What's the basis of the join - the Cartesian product across all tables? If not, what is the basis for selecting corresponding rows from the three tables? For example, you could generate a key from/in each SELECT, and use that as a join key. But what's the right order from each table? More information is required.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Hi ArndW,ArndW wrote:Have you though about just using 3 Oracle Enterprise stages and linking the 3 streams using a funnel stage? ...
Here I am giving the clear picture we are using oracle database as source. There are three tables like this..
1 .Invoice_Details-
INVOICE_NO NOT NULL VARCHAR2(10)
INVOICE_DATE DATE
INVOICE_FROM DATE
INVOICE_TO DATE
CUSTOMER_ID NUMBER
AMOUNT FLOAT(126)
PAYMENT_DUE_DATE DATE
2.Payment_Details
PAYMENT_ID NOT NULL VARCHAR2(10)
INVOICE_NO VARCHAR2(10)
PAID_AMOUNT FLOAT(126)
PAID_DATE DATE
PAYMENT_METHOD VARCHAR2(15)
PAID_AT VARCHAR2(10)
3.prepaid_sales
CUSTOMER_ID NUMBER
PRODUCT_ID VARCHAR2(10)
SERVICE_ID VARCHAR2(10)
RECHARGE_DATE DATE
VALIDITY_DATE DATE
AMOUNT FLOAT(126)
SHOP_ID VARCHAR2(10)
SALESMAN_ID VARCHAR2(10)
and we need INVOICE_DATE,INVOICE_from,INVOICE_to,PAYMENT_DUE_DATEfrom invoice_details ,PAID_DATE from payment_details,RECHARGE_DATE,VALIDITY_DATE from prepaid_sales.
how I have to achive that?
Hi ArndW,ArndW wrote:Have you though about just using 3 Oracle Enterprise stages and linking the 3 streams using a funnel stage? ...
Here I am giving the clear picture we are using oracle database as source. There are three tables like this..
1 .Invoice_Details-
INVOICE_NO NOT NULL VARCHAR2(10)
INVOICE_DATE DATE
INVOICE_FROM DATE
INVOICE_TO DATE
CUSTOMER_ID NUMBER
AMOUNT FLOAT(126)
PAYMENT_DUE_DATE DATE
2.Payment_Details
PAYMENT_ID NOT NULL VARCHAR2(10)
INVOICE_NO VARCHAR2(10)
PAID_AMOUNT FLOAT(126)
PAID_DATE DATE
PAYMENT_METHOD VARCHAR2(15)
PAID_AT VARCHAR2(10)
3.prepaid_sales
CUSTOMER_ID NUMBER
PRODUCT_ID VARCHAR2(10)
SERVICE_ID VARCHAR2(10)
RECHARGE_DATE DATE
VALIDITY_DATE DATE
AMOUNT FLOAT(126)
SHOP_ID VARCHAR2(10)
SALESMAN_ID VARCHAR2(10)
and we need INVOICE_DATE,INVOICE_from,INVOICE_to,PAYMENT_DUE_DATEfrom invoice_details ,PAID_DATE from payment_details,RECHARGE_DATE,VALIDITY_DATE from prepaid_sales.
how I have to achive that?
balu124 wrote:Hi ArndW,ArndW wrote:Have you though about just using 3 Oracle Enterprise stages and linking the 3 streams using a funnel stage? ...
Here I am giving the clear picture we are using oracle database as source. There are three tables like this..
1 .Invoice_Details-
INVOICE_NO NOT NULL VARCHAR2(10)
INVOICE_DATE DATE
INVOICE_FROM DATE
INVOICE_TO DATE
CUSTOMER_ID NUMBER
AMOUNT FLOAT(126)
PAYMENT_DUE_DATE DATE
2.Payment_Details
PAYMENT_ID NOT NULL VARCHAR2(10)
INVOICE_NO VARCHAR2(10)
PAID_AMOUNT FLOAT(126)
PAID_DATE DATE
PAYMENT_METHOD VARCHAR2(15)
PAID_AT VARCHAR2(10)
3.prepaid_sales
CUSTOMER_ID NUMBER
PRODUCT_ID VARCHAR2(10)
SERVICE_ID VARCHAR2(10)
RECHARGE_DATE DATE
VALIDITY_DATE DATE
AMOUNT FLOAT(126)
SHOP_ID VARCHAR2(10)
SALESMAN_ID VARCHAR2(10)
and we need INVOICE_DATE,INVOICE_from,INVOICE_to,PAYMENT_DUE_DATEfrom invoice_details ,PAID_DATE from payment_details,RECHARGE_DATE,VALIDITY_DATE from prepaid_sales.
how I have to achive that?
Hi balu,
U can try in this way
use three oracle stage for three tables
use user defined query in the stage for required fields
using copy stage take copy of the output
Take the output and give this merge stage
and get the output
folks correct me if am wrong
Regards
Naveen.K
As per my understanding, the three tables can be joined using the following columns: CUSTOMER_ID and INVOICE_NO.
Using a single Oracle enterpise stage, you can write a join query and get the desired columns. Else use 3 Oracle Enterprise stages with simple select queries and 2 join/Lookup stages to get the desired output.
Using a single Oracle enterpise stage, you can write a join query and get the desired columns. Else use 3 Oracle Enterprise stages with simple select queries and 2 join/Lookup stages to get the desired output.
-
- Participant
- Posts: 334
- Joined: Fri Dec 01, 2006 5:17 am
- Location: Texas
I hope the following are true-
INVOICE_NO column is unique in Payment_Details
CUSTOMER_ID column is unique in prepaid_sales
USE ONLY ONE ORACLE STAGE,
In user defined query, put this sql statment
select A.INVOICE_DATE,
A.INVOICE_from,
A.INVOICE_to,
A.PAYMENT_DUE_DATE,
B.PAID_DATE,
C.RECHARGE_DATE,
C.VALIDITY_DATE
from invoice_details A,
payment_details B,
prepaid_sales C
where A.INVOICE_NO=B.INVOICE_NO AND
A.CUSTOMER_ID=C.CUSTOMER_ID;
Seems one of the solution.
let me know if you got other solution also.
INVOICE_NO column is unique in Payment_Details
CUSTOMER_ID column is unique in prepaid_sales
USE ONLY ONE ORACLE STAGE,
In user defined query, put this sql statment
select A.INVOICE_DATE,
A.INVOICE_from,
A.INVOICE_to,
A.PAYMENT_DUE_DATE,
B.PAID_DATE,
C.RECHARGE_DATE,
C.VALIDITY_DATE
from invoice_details A,
payment_details B,
prepaid_sales C
where A.INVOICE_NO=B.INVOICE_NO AND
A.CUSTOMER_ID=C.CUSTOMER_ID;
Seems one of the solution.
let me know if you got other solution also.
harshakumar mc
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact: