DSXchange

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?

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?

ArndW wrote: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?

Hi ArndW,

I am using oracle database and there is no common column in the three tables .

Have you though about just using 3 Oracle Enterprise stages and linking the 3 streams using a funnel stage?

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.

Sounds like a job interview question to me.

ArndW wrote:Have you though about just using 3 Oracle Enterprise stages and linking the 3 streams using a funnel stage? ...

Hi ArndW,
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?

ArndW wrote:Have you though about just using 3 Oracle Enterprise stages and linking the 3 streams using a funnel stage? ...

Hi ArndW,
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:

ArndW wrote:Have you though about just using 3 Oracle Enterprise stages and linking the 3 streams using a funnel stage? ...

Hi ArndW,
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.

I think bandish has written a good point .

As per my understanding there is one more option, you can create a dummy column in all the three tables ,assign the value 1 to that column and by using join in the query or using join stage ( avoid using lookup stage) you can achieve the desired result.

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.

ArndW wrote:Have you though about just using 3 Oracle Enterprise stages and linking the 3 streams using a funnel stage? ...

Hi Arndw,
Is funnel stage accept the data from multiple sources of different structures?

No. You have to make the structures the same. Column Generator stages are good for this.