Oracle Database Filter

[NN1]

I am trying to limit the data from the the oracle database to the input tool. Our database has upto 10 years of data. When I select the databast and bring it into the input tool, it is pulling all ten yeats. How can I restrict it to only one year or a month? If don't restrict it then the run time on the workflow is hours for just getting the data into the workflow.

Any guidance will be appreciated. Thanks​

[AbhilashR]

@NN1 - If you are writing a SQL to pull data from Oracle, you could could include a date condition (text in blue below) in its WHERE clause which would go something like

SELECT

COLUMN1,

COLUMN2

FROM TABLE

WHERE
BIZ_DATE >= TO_DATE('2019/01/01,'YYYY/MM/DD')

AND BIZ_DATE < TO_DATE('2020/01/01,'YYYY/MM/DD')

You can modify the date range to a month or year as you see fit.

Hope this helps.

[NN1]

Thank you. Is there an option to use the visual query builder and input the dates there instead of writing a SQL query?

[AbhilashR]

@NN1 - Yes, you could type in your date criteria via the visual query builder too. Selection the date column from your table in the Expression column and type in the criteria in the Criteria column. Something like the image below:

In your case select your date column in the Expression column twice, and paste the criteria below in the Criteria column:

Criteria 1:  >= TO_DATE('2019/01/01','YYYY/MM/DD')

Criteria 2:  < TO_DATE('2020/01/01','YYYY/MM/DD')

[NN1]

Thank you. That helped. How can we help with the run time. It's taking 20 minutes to bring in 4 million records.

[AbhilashR · Accepted solution]

@NN1 - you may need to include additional filters in your SQL to get more specific data for your analysis. The speed at which you retrieve data from Oracle is a function of how much data you are pulling, and also your connectivity to the Oracle server.

Including additional filter criteria will help you further define (by that extension reduce the records being pulled) the universe of data you need to pull into Alteryx, which should help you reduce data pull time.