Toad World® Forums

Using File iterator in Toad Data Point


I created the above app in Toad for Oracle and is working fine.
Now i have to recreate same in Data point due to migration but i am not seeing any file Iterator function in Tool box which i used to create the app in Toad for Oracle.

Is file iterator option available or any replacement for that?

Hi, in Toad Data Point we have a Automation module where you are able to use different kind of activities. If you will provide me more details what you want to achieve we can try to navigate you.

Thanks Filip!

Thing is we run query each month to pull data from Production server. The query contains bind variables that pick Start date, End date and Store location from the local text file. The above app i created in Toad for Oracle which used File iterator option to pick dates and location from three different file. So the variables are replaced by the actual values from the file.
I am not seeing any file iterator option in Data point, so is there any way to replicate what i created in Toad for oracle.

SELECT a.cycle_count,
s.stocktake_date,
a.location,
a.dept,
a.class,
x.lot_num,
x.line_num,
x.store_item_sku_num,
c.item_desc,
a.item,
A.SNAPSHOT_UNIT_RETAIL,
SUM (a.SNAPSHOT_ON_HAND_QTY), /* Expected Count, Store’s SOH*/

   SUM (a.PHYSICAL_COUNT_QTY),          /*Service Count + SLC Adjustments*/
    SUM (a.SNAPSHOT_ON_HAND_QTY * snapshot_unit_retail) as snap_rtl,
    SUM (a.PHYSICAL_COUNT_QTY * snapshot_unit_retail) as count_rtl  

FROM table1 a /RMS count table/
, table2 s /* RMS schedule*/
, table3 x, item_master c
WHERE a.cycle_count = s.cycle_count
AND s.stocktake_date BETWEEN to_date(:StartDate,'DD-MON-YYYY') AND to_date(:EndDate,'DD-MON-YYYY') and
a.item = x.item and a.item = c.item
and x.status_code = 'A'
and c.status = 'A'
AND a.location in (:TEST_VAR)
GROUP BY a.cycle_count,
a.dept,
s.stocktake_date,
a.location,
a.class,
x.lot_num,
x.line_num,
x.store_item_sku_num,
c.item_desc,
a.item,
A.SNAPSHOT_UNIT_RETAIL

HAVING (SUM(ABS(COALESCE(a.SNAPSHOT_ON_HAND_QTY,0))) + SUM(ABS(COALESCE(a.SNAPSHOT_IN_TRANSIT_QTY,0))) +
SUM(ABS(COALESCE(a.PHYSICAL_COUNT_QTY,0)))) !=0

There will be more ways how to do this but you can try these steps:

  1. Use Variables activity for getting values from file(s):
  • as you can see I defined my File with variables first as a CSV connection so I can get a specific value.
  • In CSV I have a parameter for Date and Location.
  1. Then I use Select to File activity. As you can see I used there my values as a Bind variable in SQL and as Variable for my export location.

  2. You can schedule it to run every month. Some activities can help you with notification that your file was updated if needed.
    image
    Let me know if it solves your problem.

Filip

Hey Filip,

Thanks for the information and sorry for late reply.

I exactly replicated as you mentioned above but i am receiving error at runtime when it is starting to export data.
Error : File_1 - ORA-00907: missing right parenthesis

The query is running fine if using it directly.

Below is the snippet of query and i am using bind variable as date only once.

I a using access database from where providing date as data.
Shared access image too. I guess it may be related to column formatting or to_Date functions mismatch with data input.