TDP Automation Error

Toad Data Point & Toad Intelligence Central
1

Hi TDP world, new user to TDP and trying to leverage the automation for some scripts. I have some existing SQLs that process fine when manually run, but they are erroring out when I try to run them through the automation / scheduler.

Any help would be much appreciated on the error, but it appears to me it does not like the temporary table in the last pass.

Exception Messages:
File_1 - ERROR: relation does not exist ODS_LIVE.AGENT.PO_SKU_FORECAST

ERROR: relation does not exist ODS_LIVE.AGENT.PO_SKU_FORECAST

Settings:

image
image803×307 16.9 KB

image

SQL (only portion related to error):
create temp table PO_SKU_FORECAST as
(SELECT MIN(C2.DATECODE)WEEK_STARTING,
DIV_NO ,DIV_NAME ,DEPT_NO ,CLASS_NO ,VENDOR_NO ,VENDOR_NAME ,FACTORY_NO ,FACTORY_NAME ,FOB ,SHIP_TO,PO_NO ,
REVISION_NO ,ORDER_DATE ,ORIG_START_SHIP_DATE ,SHIP_DATE ,ORIGINAL_CANCEL_DATE ,CANCEL_DATE ,ORIGINAL_ANTICIPATE_DATE ,
ANTICIPATE_DATE ,STYLE_NO ,STYLE_DESC ,SKU_NO ,SKU_DESC ,QTY_PLANNED ,TOTAL_COST ,TOTAL_RETAIL ,USED_REVISED_DATE ,
STYLE_LENGTH ,STYLE_WIDTH ,STYLE_HEIGHT ,STYLE_MASTERPACK_QTY,
TTL_SKU_CBMS,CONTAINERS_40_FT, SOURCE
FROM ALL_ITEMS_WITH_CBMS IT
JOIN RIQ_BIS_AC_LIVE..LU_FISCAL_CALENDAR_DATE cal
ON (IT.SHIP_DATE=CAL.DATECODE)
JOIN RIQ_BIS_AC_LIVE..LU_FISCAL_CALENDAR_DATE c2
ON (C2.WEEK_ID=CAL.WEEK_ID)
GROUP BY DIV_NO ,DIV_NAME ,DEPT_NO ,CLASS_NO ,VENDOR_NO ,VENDOR_NAME ,FACTORY_NO ,FACTORY_NAME ,FOB ,SHIP_TO,PO_NO ,
REVISION_NO ,ORDER_DATE ,ORIG_START_SHIP_DATE ,SHIP_DATE ,ORIGINAL_CANCEL_DATE ,CANCEL_DATE ,ORIGINAL_ANTICIPATE_DATE ,
ANTICIPATE_DATE ,STYLE_NO ,STYLE_DESC ,SKU_NO ,SKU_DESC ,QTY_PLANNED ,TOTAL_COST ,TOTAL_RETAIL ,USED_REVISED_DATE ,
STYLE_LENGTH ,STYLE_WIDTH ,STYLE_HEIGHT ,STYLE_MASTERPACK_QTY,
TTL_SKU_CBMS,CONTAINERS_40_FT, SOURCE
ORDER BY SOURCE) ;

SELECT *
FROM (
SELECT WEEK_STARTING,
DIV_NO ,DIV_NAME ,DEPT_NO ,CLASS_NO ,VENDOR_NO ,VENDOR_NAME ,FACTORY_NO ,FACTORY_NAME ,FOB ,SHIP_TO,PO_NO ,
REVISION_NO ,ORDER_DATE ,ORIG_START_SHIP_DATE ,SHIP_DATE ,ORIGINAL_CANCEL_DATE ,CANCEL_DATE ,ORIGINAL_ANTICIPATE_DATE ,
ANTICIPATE_DATE ,STYLE_NO ,STYLE_DESC , SUM(TOTAL_COST)TOTAL_COST ,SUM(TOTAL_RETAIL)TOTAL_RETAIL ,USED_REVISED_DATE ,
STYLE_LENGTH ,STYLE_WIDTH ,STYLE_HEIGHT ,STYLE_MASTERPACK_QTY,
SUM(TTL_SKU_CBMS)TTL_SKU_CBMS,SUM(CONTAINERS_40_FT)CONTAINERS_40_FT,SOURCE
FROM PO_SKU_FORECAST
GROUP BY WEEK_STARTING,DIV_NO ,DIV_NAME ,DEPT_NO ,CLASS_NO ,VENDOR_NO ,VENDOR_NAME ,FACTORY_NO ,FACTORY_NAME ,FOB ,SHIP_TO,PO_NO ,
REVISION_NO ,ORDER_DATE ,ORIG_START_SHIP_DATE ,SHIP_DATE ,ORIGINAL_CANCEL_DATE ,CANCEL_DATE ,ORIGINAL_ANTICIPATE_DATE ,
ANTICIPATE_DATE ,STYLE_NO ,STYLE_DESC , USED_REVISED_DATE ,
STYLE_LENGTH ,STYLE_WIDTH ,STYLE_HEIGHT ,STYLE_MASTERPACK_QTY,SOURCE)X
ORDER BY PO_NO,STYLE_NO,SOURCE;

2

Welcome to the Toad Data Point forum!

Are other parts of your script running ok? e.g. the portion of SQL (involving the temp table) that you shared... is that portion the only part of the script giving the error?

Some questions:

  • If your automation script has different tasks, in which task is the error occurring?
  • Are you using the same credentials to log into the database (assuming Oracle here)?
  • Is the temp table only used one time in your SQL script? That is, are any other subsequent statements referring to the same temp table? If not, you may want to consider executing your logic using a WITH clause to see if that helps... e.g.

WITH
PO_SKU_FORECAST as
(
SELECT MIN(
C2.DATECODE)WEEK_STARTING
, DIV_NO ,DIV_NAME
, << remainder of your temp table definition here, etc. >>;
)
SELECT *FROM
(
SELECT WEEK_STARTING,
DIV_NO ,DIV_NAME, << etc. >>
FROM PO_SKU_FORECAST
GROUP BY WEEK_STARTING, << etc. >>
ORDER BY << etc. >>
);

3

Thanks for the feedback!

  • If your automation script has different tasks, in which task is the error occurring?
    -- The only additional task in the automation I was trying to leverage was to run the SQL and send the export via email. Otherwise, the SQL runs just fine manually and I can export the data once processed.
  • Are you using the same credentials to log into the database (assuming Oracle here)?
    --Yes same credentials
  • Is the temp table only used one time in your SQL script? That is, are any other subsequent statements referring to the same temp table? If not, you may want to consider executing your logic using a WITH clause to see if that helps... e.g.
    --The additional SQL passes appear to be processing fine that use a temporary table, the piece provided seems to be when the error occurs. I will try to make some edits as you mentioned using a WITH clause to try my luck .