I would like to join my main input with another query, but I dont want to use a lookup because the lookup pulls 2 million records before joining with the main query by ID. I would rather hit the database a few hundred times to get the records for the second input. Is this functionality possible. I do like the functionality of the tMap and would like use it for the transformation.
Why not just use ONE tOracle Input and put your whole query in it - Joins and everything - let the database do all the work.
Yes - it won't visually show you that you are joining tables - but it would be a small price to pay.
Hi all, My best advice is to use the tELTOracleInput for every tables, a tELTOracleMap to perform your JOIN and transformation via Oracle database then a tELTOracleOutput to store result of the ELT transformation. ELT transformation generates a SQL query to perform operations and transformation, it's easy to design and very fast and efficient. Best regards.
SMaz -
The reason for the second Input is that I have a join to my ADDRESS table in the primary query via Input1. This query links to the sequence 1. But for this output file I also need the address information from sequence 2. This is not possible in one query.
cantoine -
I will give this a try next.
Hi all, My best advice is to use the tELTOracleInput for every tables, a tELTOracleMap to perform your JOIN and transformation via Oracle database then a tELTOracleOutput to store result of the ELT transformation. ELT transformation generates a SQL query to perform operations and transformation, it's easy to design and very fast and efficient. Best regards.
cantoine -
How do you then map the ELTOracleMap to my output file?
It looks like the tELTOracleOutput stores the result in a table in the database. I was hoping not to have to create temporary tables for this. Is that correct.
It appears that the ELTOracleInput and ELTOracltMap method won't work. This is very frustrating. This is one of the most basic functions of an ETL tool. I should have the ability to perform an unlimited number of queries by chaining together Inputs and tMaps, but there is no such thing as a join in the tmap only a lookup.
This will allow you call a single select for each row, passing in the lookup value and use the result in your output. Basically, it's tOracleInput -> tOracleRow -> tParseRecordSet -> -> tOracleOutput
I went to the training and they demonstrated how to get this done. Basically its as follows:
tOracleInput1 -> row1 -> tFlowToIterate -> Iterate -> tOracleInput2 -> row2 -> tMap -> tFileOutput
The tFlowToIterate places everything from Main1 into the globalMap then when in the SQL statement on the tOracleInput2 you can add a where clause that specifies a unique idea like WHERE UNIQUEID = " + row1.UniqueID. Finally in your tMap while you only see the schema from Main2 you can still use the items in row1. You just don't get to see the nice lines from the input schema to the output schema. The only other catch is that if you want to show values from row1 even where there are no values in row2 you need to select from the primary table of tOracleInput1 in tOracleInput2 and left outer join your connecting table and put all other where clauses in the left outer join itself, otherwise you turn your left outer join into an inner join. Here is the exact tOracleInput2 that I used in this job. Notice the second selector ADR_SEQ_NO in the join and not in the WHERE clause.
"SELECT DBR.DBR_NO, ADR.ADR_DBR_NO, ADR.ADR_SEQ_NO, ADR.ADR_NAME, ADR.ADR_ADDR1,
ADR.ADR_ADDR2, ADR.ADR_CITY, ADR.ADR_STATE, ADR.ADR_ZIP_CODE,
ADR.ADR_PHONE1, ADR.ADR_PHONE2, ADR.ADR_DOB_O FROM CDS.DBR LEFT OUTER JOIN CDS.ADR
ON DBR.DBR_NO = ADR.ADR_DBR_NO AND ADR.ADR_SEQ_NO = '02'
WHERE DBR.DBR_NO = '" + row1.DBR_NO + "'"
