Hi,
The actual code is shown below.....
I can't recall the exact message but SQLOUT was mentioned
as being the fex in error and OUTPUT1 as being the file
which couldn't be written, though approx 14,000 records
had been written at this stage out of a total of approx.
480,000. This problem has occured twice in recent weeks
but the program has otherwise worked for almost 2 years, though the number of records being extracted is increasing slightly all the time.
Peter.
code follows.....
SQL ODBC
SELECT SAL.SO_VIEW_ID,
SAL.CO_BUSINESS_UNIT_ID AS BUZZER,
SAL.SO_ORDER_NUMBER_ID,
SAL.SO_LINE_ID,
SAL.SO_ORDER_TYPE_ID,
SAL.SO_LINE_TYPE_ID,
SAL.SO_DOC_TYPE_ID,
SAL.SO_DOCUMENT_NUMBER_ID,
SAL.SO_STATUS_LAST_ID,
SAL.SO_STATUS_NEXT_ID,
SAL.SO_ORDER_COMPANY_ID,
CAST(SAL.CS_CUSTOMER_ID AS CHAR(15)) AS OFF_CUST,
SAL.AD_ADDRESS_VER_ID AS SOLD_TO,
SAL.BL_BILL_TO_ID AS BILL_TO,
SAL.SH_SHIP_TO_ID AS SHIP_TO,
SAL.IT_BRANCH_ITEM_ID,
ITM_NO1.IT_ITEM_NO_ID AS JDE_SINNER,
ITM_NO1.IT_ITEM_NO_DESC AS PARTNUM1,
ITM_NO2.IT_ITEM_NO_DESC AS PARTNUM2,
ITM_NO1.IT_SALES_CATEGORY1_ID,
ITM_NO1.IT_SALES_CATEGORY1_DESC,
ITM_NO1.IT_SALES_CATEGORY2_ID,
ITM_NO1.IT_SALES_CATEGORY2_DESC,
ITM_NO1.IT_SALES_CATEGORY3_ID,
ITM_NO1.IT_SALES_CATEGORY3_DESC,
ITM_NO1.IT_ITEM_NO1_DESC,
ITM_NO1.IT_GL_POSTING_CATEGORY_ID AS POSTIE,
SAL.F_SOL_QTY_ORDERED,
SAL.F_SOL_QTY_SHIPPED,
SAL.F_SOL_EXT_QTY_CONV_FACTOR,
SAL.F_SOL_FRE_QTY_CONV_FACTOR,
SAL.F_SOL_DATE_ORDER,
SAL.F_SOL_DATE_SHIP,
SAL.F_SOL_DATE_ACTUAL_SHIP,
SAL.SO_REFERENCE1_ID,
SAL.SO_REFERENCE2_ID,
SAL.F_SOL_UNIT_WEIGHT,
SAL.F_SOL_UNIT_VOLUME,
SAL.F_SOL_GROSS_WEIGHT,
SAL.F_SOL_DATE_INVOICE,
SAL.GD_DATE_ID AS GLDATE1,
SAL.F_SOL_PRICE_UNIT_EXT AS BAS_VAL,
SAL.F_SOL_PRICE_INVOICE_EXT AS INV_VAL,
SAL.F_SOL_PRICE_UNIT_LIST_EXT AS NET_VAL,
SAL.F_SOL_PRICE_NET_NET_EXT AS NETNET_VAL,
SAL.F_SOL_COST_EXTENDED AS COST_VAL,
SAL.F_SOL_PRICE_UNIT_EXT_USD AS BAS_VAL_USD,
SAL.F_SOL_PRICE_INVOICE_EXT_USD AS INV_VAL_USD,
SAL.F_SOL_PRICE_UNIT_LIST_EXT_USD AS NET_VAL_USD,
SAL.F_SOL_PRICE_NET_NET_EXT_USD AS NETNET_VAL_USD,
SAL.F_SOL_COST_UNIT_EXT_USD AS COST_VAL_USD,
SAL.CT_ITEM_NO_ID,
SAL.CO_HBUSINESS_UNIT_ID AS OFF_BU,
UPPER(SAL.SO_DELIVERY_INTRUCTIONS1_ID) AS DELINST1,
UPPER(SAL.SO_DELIVERY_INTRUCTIONS2_ID) AS DELINST2,
COMP.CT_ITEM_NO_DESC,
COMP.CT_ITEM_NO1_DESC,
SUBSTR(COMP.CT_PROD_CTYPE_DESC,1,9) AS PRD_CONTYP
FROM E6DW_DATA.FAT_BSE_SALES_ORDER AS SAL
LEFT OUTER JOIN E6DW_DATA.ITT_LU_BRANCH_ITEM AS ITM_NO1
ON SAL.IT_BRANCH_ITEM_ID = ITM_NO1.IT_BRANCH_ITEM_ID
LEFT OUTER JOIN E6DW_DATA.ITT_LU_ITEM AS ITM_NO2
ON ITM_NO1.IT_ITEM_NO_ID = ITM_NO2.IT_ITEM_NO_ID
LEFT OUTER JOIN E6DW_DATA.CTV_LU_ITEM AS COMP
ON SAL.CT_ITEM_NO_ID = COMP.CT_ITEM_NO_ID
WHERE (SAL.SO_ORDER_COMPANY_ID
IN ('00001','00018','00019','00020','00029','00058','00070')) AND
(SAL.SO_VIEW_ID = 'S') AND
(SAL.SO_STATUS_LAST_ID IN ('620','900')) AND
(SAL.SO_STATUS_NEXT_ID = '999') AND
(YEAR(SAL.GD_DATE_ID) NOT IN (2003,2004)) AND
(SAL.SO_ORDER_TYPE_ID NOT IN ('E1','E2','E3','E4','E5'))
ORDER BY SAL.GD_DATE_ID DESC,
SAL.IT_BRANCH_ITEM_ID,
ITM_NO1.IT_ITEM_NO_ID,
ITM_NO2.IT_ITEM_NO_DESC,
SAL.CO_BUSINESS_UNIT_ID,
SAL.SO_ORDER_COMPANY_ID,
SAL.SO_ORDER_TYPE_ID,
SAL.SO_LINE_TYPE_ID,
SAL.SO_ORDER_NUMBER_ID,
SAL.SO_LINE_ID,
SAL.SO_DOC_TYPE_ID,
SAL.SO_DOCUMENT_NUMBER_ID FOR FETCH ONLY WITH UR;
TABLE
ON TABLE HOLD AS OUTPUT1 FORMAT ALPHA
END
-RUN
