RE: We want to be able to execute the query with the least amount of moving parts as possible, which would require us querying directly to the Oracle ser
We have a table that we need to be able to join upon itself or filter itself to receive the data that we need .The table is very large, hundreds of millions of records, so we cannot read in the whole table also we are reading this from a production Oracle reporting server, so we cannot create our own view or table on the server itself. We want to be able to execute this query with the least amount of moving parts as possible, which would require us querying directly to the Oracle server .
We are currently trying to develop a design which executes the following query :
select /*+ parallel(8) */ COL1, min(COL2)
select * from TABLEA where COLDATE >= trunc(sysdate-31) and COLDATE < trunc(sysdate) and COL3 = ‘123’ and COL4 = ‘ABC’)
on tbla.COL1 = s2.COL1
group by s2.COL1
In this case, you would need to use the override option in the source instance of the design. You can create a data object to match the name, datatype, and position of the output of the query and use it as a source in the design. Add the query as override SQL in this source instance. This query will run on the source itself and the result of the query will be pulled from this.