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