I have a .fex that takes 9 hours to run in Report Caster. Since this is obviously too long to let something run, I captured some of the SQL to do a SQL Passthru. The original .fex has three WebFocus joins and the SQL I captured replaces the first join and table file. The SQL Passthru works and each object opens in the Report Painter, but when I schedule the report to run in Report Caster I get an error.

ERROR WRITING OUTPUT FILE: OAS0002B
ERROR IN READING NEXT RECORD : OAS0002B
ERROR IN READING NEXT RECORD : OAS0002B
Task error: ERROR WRITING OUTPUT FILE: OAS0002B

I am fairly new to WebFocus and don't know the code real well. This report was written by a consultant, so it looks especially complicating. Can someone please review my code to see if anything jumps out and looks wrong. The code is below and thank you!!

-* File oas0003b_sql_passt_test.fex
SET ASNAMES=ON
-*
-*JOIN
-*AX_CIF.AX_CIF.ID AND AX_CIF.STATIC_NUMBER IN AX_CIF TO MULTIPLE
-*AX_ACCOUNT.AX_ACCOUNT.ID AND AX_ACCOUNT.AX_ACCOUNT.UKEY IN AX_ACCOUNT
-*AS J0
-*END
ENGINE SQLORA SET DEFAULT_CONNECTION AMS1
SQL SQLORA PREPARE SQLOUT FOR
SELECT t1.id,
max(t1.id)
/*+ FULL(T1) FULL(T2) */
FROM import.ax_cif t1,
import.ax_account t2
WHERE t2.id = t1.id
AND t2.ukey = t1.static_number
AND t1.source = 'CIF'
AND t1.bad_households = 'N'
AND t1.bbt_flag = 'N'
AND t2.employees IS NULL
AND t2.active_inactive = 'A'
AND (t2.primary_business_account = 'Y'
OR t2.primary_retail_account = 'Y')
AND (t2.customer_acct_classification IN ('B', 'N')
OR t2.customer_acct_classification = 'R'
AND t2.static_irm_segment_description IS NULL
OR t2.customer_acct_classification = 'R'
AND t2.static_irm_segment_description = 'Private Banking'
OR t2.customer_acct_classification = 'R'
AND t2.static_irm_segment_description = 'Wealth Management'
OR t2.customer_acct_classification = 'R'
AND t2.static_irm_segment_description IN ('Core', 'Top Core')
AND t1.officer_title = 'PRIVATE BANKER I'
OR t2.customer_acct_classification = 'R'
AND t2.static_irm_segment_description IN ('Core', 'Top Core')
AND t1.officer_title = 'PRIVATE BANKER II'
OR t2.customer_acct_classification = 'R'
AND t2.static_irm_segment_description IN ('Core', 'Top Core')
AND t1.officer_title = 'PRIVATE BANKER III'
OR t2.customer_acct_classification = 'R'
AND t2.static_irm_segment_description IN ('Core', 'Top Core')
AND t1.officer_title = 'PRIVATE BANKING MGR I'
OR t2.customer_acct_classification = 'R'
AND t2.static_irm_segment_description IN ('Core', 'Top Core')
AND t1.officer_title = 'PRIVATE BANKING MGR II'
OR t2.customer_acct_classification = 'R'
AND t2.static_irm_segment_description IN ('Core', 'Top Core')
AND t1.officer_title = 'PRIVATE BANKING MGR III'
OR t2.customer_acct_classification = 'R'
AND t2.static_irm_segment_description IN ('Core', 'Top Core')
AND t1.officer_title = 'WM ADVISOR I'
OR t2.customer_acct_classification = 'R'
AND t2.static_irm_segment_description IN ('Core', 'Top Core')
AND t1.officer_title = 'WM ADVISOR II'
OR t2.customer_acct_classification = 'R'
AND t2.static_irm_segment_description IN ('Core', 'Top Core')
AND t1.officer_title = 'WM ADVISOR III'
OR t2.customer_acct_classification = 'R'
AND t2.static_irm_segment_description IN ('Core', 'Top Core')
AND t1.officer_title = 'WM ADVISOR IV'
OR t2.customer_acct_classification = 'R'
AND t2.static_irm_segment_description IN ('Core', 'Top Core')
AND t1.officer_title = 'WM GROUP DIRECTOR'
OR t2.customer_acct_classification = 'R'
AND t2.static_irm_segment_description IN ('Core', 'Top Core')
AND t1.officer_title = 'WM TEAM DIRECTOR I'
OR t2.customer_acct_classification = 'R'
AND t2.static_irm_segment_description IN ('Core', 'Top Core')
AND t1.officer_title = 'WM TEAM DIRECTOR II')
AND (t1.officer_status_code = 'T'
OR t1.officer_code IS NULL
OR t1.officer_code = '00000'
OR t1.officer_code = '99999'
OR t1.officer_code = '0'
OR t1.officer_code = '9999'
OR NOT t1.officer_title IN ('BN SALES & SERVICE MGR I', 'BN SALES & SERVICE MGR II', 'BSO CONSTRUCTION SPEC I', 'BSO CONSTRUCTION SPEC II', 'BSO CONSTRUCTION SPEC III', 'BSO CONSTRUCTION SPEC IV', 'BSO CONSTRUCTION SPEC V', 'BUSINESS BANKER I', 'BUSINESS BANKER II', 'BUSINESS BANKER III', 'BUSINESS BANKER IV', 'BUSINESS BANKING MGR I', 'BUSINESS BANKING MGR II', 'BUSINESS DEPOSITS OFF I', 'BUSINESS DEPOSITS OFF II', 'BUSINESS DEPOSITS OFF III', 'BUSINESS DEPOSITS OFF IV', 'BUSINESS SVCS OFFICER I'
, 'BUSINESS SVCS OFFICER II', 'BUSINESS SVCS OFFICER III', 'BUSINESS SVCS OFFICER IV', 'BUSINESS SVCS OFFICER V', 'CB CORP ACCTS OFFICER I', 'CB CORP ACCTS OFFICER II', 'CB CORP ACCTS OFFICER III', 'CB CORP ACCTS OFFICER IV', 'CITY/AREA EXECUTIVE I', 'CITY/AREA EXECUTIVE II', 'CITY/AREA EXECUTIVE III', 'CITY/AREA EXECUTIVE IV', 'CITY/AREA EXECUTIVE V', 'CITY/AREA EXECUTIVE VI', 'CORP ACCTS OFFICER I', 'CORP ACCTS OFFICER II', 'CORP ACCTS OFFICER III', 'CORP ACCTS OFFICER IV', 'CORPORATE BANKER I'
, 'CORPORATE BANKER II', 'CORPORATE BANKER III', 'CORPORATE BANKING MGR', 'FINANCIAL CENTER MGR I', 'FINANCIAL CENTER MGR II', 'FINANCIAL CENTER MGR III', 'FINANCIAL CENTER MGR IV', 'FINANCIAL CENTER MGR V', 'FINANCIAL CTR LDR I', 'FINANCIAL CTR LDR II', 'FINANCIAL CTR LDR III', 'FINANCIAL SERVICES MGR I', 'GROUP PRIVATE BNKG MGR', 'PERS TRUST SPECIALIST I', 'PERS TRUST SPECIALIST II', 'PERS TRUST SPECIALIST III', 'PRIVATE BANKER I', 'PRIVATE BANKER II', 'PRIVATE BANKER III', 'PRIVATE BANKING MGR I'
, 'PRIVATE BANKING MGR II', 'PRIVATE BANKING MGR III', 'PRIVATE FIN ADVISOR I', 'PRIVATE FIN ADVISOR II', 'PRIVATE FIN ADVISOR III', 'PRIVATE FIN SVCS MGR I', 'PRIVATE FIN SVCS MGR II', 'PRIVATE FIN SVCS MGR III', 'PROBLEM LOAN MANAGER', 'REG SPEC ASSETS OFF I', 'REG SPEC ASSETS OFF II', 'REGIONAL CREDIT OFF I', 'REGIONAL CREDIT OFF II', 'RELATIONSHIP BANKER I', 'RELATIONSHIP BANKER II', 'RETAIL SVCS OFF I', 'RETAIL SVCS OFF II', 'RETAIL SVCS OFF III', 'SENIOR CREDIT OFFICER I', 'SENIOR CREDIT OFFICER II'
, 'WM ADVISOR I', 'WM ADVISOR II', 'WM ADVISOR III', 'WM ADVISOR IV', 'WM GROUP DIRECTOR', 'WM TEAM DIRECTOR I', 'WM TEAM DIRECTOR II')
OR substr(t2.primary_center_number, 1, 3) <> substr(t1.officer_center_number, 1, 3))
GROUP BY t1.id
ORDER BY t1.id;
END
TABLE FILE AX_CIF
PRINT
ID
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE HOLD AS OAS0002A
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
UNITS=IN,
PAGESIZE='SCREEN',
LEFTMARGIN=0.000000,
RIGHTMARGIN=0.000000,
TOPMARGIN=0.000000,
BOTTOMMARGIN=0.000000,
SQUEEZE=ON,
ORIENTATION=PORTRAIT,
$
TYPE=REPORT,
GRID=OFF,
FONT='ARIAL',
SIZE=9,
COLOR='BLACK',
BACKCOLOR='NONE',
STYLE=NORMAL,
RIGHTGAP=0.125000,
TOPGAP=0.013889,
BOTTOMGAP=0.027778,
$
ENDSTYLE
END
JOIN CLEAR *
JOIN
ID IN OAS0002A TO MULTIPLE AX_CIF.AX_CIF.ID IN AX_CIF AS J1
END
JOIN
AX_CIF.AX_CIF.ID AND AX_CIF.STATIC_NUMBER IN OAS0002A TO MULTIPLE
AX_ACCOUNT.AX_ACCOUNT.ID AND AX_ACCOUNT.AX_ACCOUNT.UKEY IN AX_ACCOUNT
AS J2
END

DEFINE FILE OAS0002A
BUSINESS_ACCOUNTS/P08=IF (PRIMARY_BUSINESS_ACCOUNT EQ 'Y') THEN 1 ELSE 0;
RETAIL_ACCOUNTS/P08=IF (PRIMARY_RETAIL_ACCOUNT EQ 'Y') THEN 1 ELSE 0;
SNS_ACCOUNTS/P08=IF (CUSTOMER_ACCT_CLASSIFICATION EQ 'R') AND (SERVICE_CLUSTER EQ 'SNS') THEN 1 ELSE 0;
END
TABLE FILE OAS0002A
PRINT
AX_CIF.BIRTH_DATE
AX_CIF.DATE_DATABASE
AX_CIF.ID
AX_CIF.NAME1
AX_CIF.OFFICER_CENTER_NUMBER
AX_CIF.OFFICER_CODE
AX_CIF.OFFICER_NAME
AX_CIF.OFFICER_STATUS_CODE
AX_CIF.OFFICER_TITLE
AX_CIF.RES_ADDRESS1
AX_CIF.RES_CITY
AX_CIF.RES_STATE
AX_CIF.STATIC_NUMBER
AX_ACCOUNT.BALANCE_1
AX_ACCOUNT.BALANCE_2
AX_ACCOUNT.BALANCE_3
AX_ACCOUNT.PRIMARY_BRANCH_NAME
AX_ACCOUNT.CUSTOMER_ACCT_CLASSIFICATION
AX_ACCOUNT.PRIMARY_BUSINESS_ACCOUNT
AX_ACCOUNT.PRIMARY_CENTER_NUMBER
AX_ACCOUNT.PRIMARY_CITY_NAME
AX_ACCOUNT.PRIMARY_RETAIL_ACCOUNT
AX_ACCOUNT.PRIMARY_REGION_NAME
AX_ACCOUNT.SERVICE_CLUSTER
AX_ACCOUNT.SOCIAL_SEC_TAX_ID
AX_ACCOUNT.STATIC_BUS_SEGMENT_DESCRIPTION
AX_ACCOUNT.STATIC_BUS_SEGMENT_FLAG
AX_ACCOUNT.STATIC_IRM_SEGMENT_DESCRIPTION
AX_ACCOUNT.UNIQUE_SERVICES
BUSINESS_ACCOUNTS
RETAIL_ACCOUNTS
SNS_ACCOUNTS
WHERE ( AX_CIF.BBT_FLAG EQ 'N' );
WHERE ( AX_CIF.BAD_HOUSEHOLDS EQ 'N' );
WHERE ( AX_CIF.SOURCE EQ 'CIF' );
WHERE ( AX_ACCOUNT.ACTIVE_INACTIVE EQ 'A' );
WHERE ( AX_ACCOUNT.EMPLOYEES IS MISSING );
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE HOLD AS OAS0002B
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
UNITS=IN,
PAGESIZE='SCREEN',
LEFTMARGIN=0.000000,
RIGHTMARGIN=0.000000,
TOPMARGIN=0.000000,
BOTTOMMARGIN=0.000000,
SQUEEZE=ON,
ORIENTATION=PORTRAIT,
$
TYPE=REPORT,
GRID=OFF,
FONT='ARIAL',
SIZE=9,
COLOR='BLACK',
BACKCOLOR='NONE',
STYLE=NORMAL,
RIGHTGAP=0.125000,
TOPGAP=0.013889,
BOTTOMGAP=0.027778,
$

ENDSTYLE
END
DEFINE FILE OAS0002B
SERVICE_TYPE/A14=
DECODE SERVICE_CLUSTER(
'BCK' 'DEPOSITS'
'BLN' 'LOANS'
'BSL' 'LINES'
'BSV' 'DEPOSITS'
'CCD' 'LINES'
'CCT' 'LINES'
'CLD' 'LOANS'
'CLN' 'LOANS'
'ICK' 'DEPOSITS'
'INV' 'INVESTMENTS'
'IRA' 'DEPOSITS'
'LOC' 'LINES'
'MLN' 'LOANS'
'PCK' 'DEPOSITS'
'PSV' 'DEPOSITS'
'SBL' 'LOANS'
'SLC' 'LINES'
'SLI' 'LINES'
'SMM' 'DEPOSITS'
'TCD' 'DEPOSITS'
'TRU' 'TRUSTS'
ELSE 'MISCELLANEOUS');
RETAIL_12MNTH_AVG_DEP/D12C=IF (SERVICE_TYPE EQ 'DEPOSITS') AND (CUSTOMER_ACCT_CLASSIFICATION EQ 'R') THEN BALANCE_3 ELSE 0;
RETAIL_TOT_INVEST/D12C=IF (SERVICE_TYPE EQ 'INVESTMENTS') AND (CUSTOMER_ACCT_CLASSIFICATION EQ 'R') THEN BALANCE_2 ELSE 0;
RETAIL_COMM_OUT/D12C=IF (SERVICE_TYPE EQ 'LINES') AND (CUSTOMER_ACCT_CLASSIFICATION EQ 'R') THEN BALANCE_1 ELSE
IF (SERVICE_TYPE EQ 'LOANS') AND (CUSTOMER_ACCT_CLASSIFICATION EQ 'R') THEN BALANCE_2 ELSE 0;
RETAIL_TOTAL_TRUST_BAL/D12C=IF (SERVICE_TYPE EQ 'TRUSTS') AND (CUSTOMER_ACCT_CLASSIFICATION EQ 'R') THEN BALANCE_2 ELSE 0;
BUSINESS_12MNTH_AVG_DEP/D12C=IF (SERVICE_TYPE EQ 'DEPOSITS') AND (CUSTOMER_ACCT_CLASSIFICATION NE 'R') THEN BALANCE_3 ELSE 0;
BUSINESS_TOT_INVEST/D12C=IF (SERVICE_TYPE EQ 'INVESTMENTS') AND (CUSTOMER_ACCT_CLASSIFICATION NE 'R') THEN BALANCE_2 ELSE 0;
BUSINESS_COMM_OUT/D12C=IF (SERVICE_TYPE EQ 'LINES') AND (CUSTOMER_ACCT_CLASSIFICATION NE 'R') THEN BALANCE_1 ELSE
IF (SERVICE_TYPE EQ 'LOANS') AND (CUSTOMER_ACCT_CLASSIFICATION NE 'R') THEN BALANCE_2 ELSE 0;
BUSINESS_TOTAL_TRUST_BAL/D12C=IF (SERVICE_TYPE EQ 'TRUSTS') AND (CUSTOMER_ACCT_CLASSIFICATION NE 'R') THEN BALANCE_2 ELSE 0;
BUSINESS_SC_BCK/D12C=IF (SERVICE_CLUSTER EQ 'BCK') AND (CUSTOMER_ACCT_CLASSIFICATION NE 'R') THEN BALANCE_3 ELSE 0;
BUSINESS_SC_BLN/D12C=IF (SERVICE_CLUSTER EQ 'BLN') AND (CUSTOMER_ACCT_CLASSIFICATION NE 'R') THEN BALANCE_2 ELSE 0;
BUSINESS_SC_BSL/D12C=IF (SERVICE_CLUSTER EQ 'BSL') AND (CUSTOMER_ACCT_CLASSIFICATION NE 'R') THEN BALANCE_1 ELSE 0;
BUSINESS_SC_SBL/D12C=IF (SERVICE_CLUSTER EQ 'SBL') AND (CUSTOMER_ACCT_CLASSIFICATION NE 'R') THEN BALANCE_2 ELSE 0;
BUSINESS_SC_SLI/D12C=IF (SERVICE_CLUSTER EQ 'SLI') AND (CUSTOMER_ACCT_CLASSIFICATION NE 'R') THEN BALANCE_1 ELSE 0;
END
TABLE FILE OAS0002B
BY
DATE_DATABASE
BY
STATIC_NUMBER
BY
PRIMARY_BRANCH_NAME
BY
CUSTOMER_ACCT_CLASSIFICATION
BY
PRIMARY_CENTER_NUMBER
BY
PRIMARY_CITY_NAME
BY
NAME1
BY
PRIMARY_REGION_NAME
BY
SOCIAL_SEC_TAX_ID
BY
STATIC_BUS_SEGMENT_DESCRIPTION
BY
STATIC_BUS_SEGMENT_FLAG
BY
STATIC_IRM_SEGMENT_DESCRIPTION
BY
UNIQUE_SERVICES
BY
BIRTH_DATE
BY
ID
BY
OFFICER_CENTER_NUMBER
BY
OFFICER_CODE
BY
OFFICER_NAME
BY
OFFICER_STATUS_CODE
BY
OFFICER_TITLE
BY
RES_ADDRESS1
BY
RES_CITY
BY
RES_STATE
BY
PRIMARY_BUSINESS_ACCOUNT
BY
PRIMARY_RETAIL_ACCOUNT
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE HOLD AS OAS0002C
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
UNITS=IN,
PAGESIZE='SCREEN',
LEFTMARGIN=0.000000,
RIGHTMARGIN=0.000000,
TOPMARGIN=0.000000,
BOTTOMMARGIN=0.000000,
SQUEEZE=ON,
ORIENTATION=PORTRAIT,
$
TYPE=REPORT,
GRID=OFF,
FONT='ARIAL',
SIZE=9,
COLOR='BLACK',
BACKCOLOR='NONE',
STYLE=NORMAL,
RIGHTGAP=0.125000,
TOPGAP=0.013889,
BOTTOMGAP=0.027778,
$

ENDSTYLE
END
TABLE FILE OAS0002B
SUM
BUSINESS_ACCOUNTS
RETAIL_ACCOUNTS
SNS_ACCOUNTS
BUSINESS_12MNTH_AVG_DEP
BUSINESS_TOT_INVEST
BUSINESS_COMM_OUT
BUSINESS_TOTAL_TRUST_BAL
BUSINESS_SC_BCK
BUSINESS_SC_BLN
BUSINESS_SC_BSL
BUSINESS_SC_SBL
BUSINESS_SC_SLI
RETAIL_12MNTH_AVG_DEP
RETAIL_TOT_INVEST
RETAIL_COMM_OUT
RETAIL_TOTAL_TRUST_BAL
BY
STATIC_NUMBER AS 'CIF_KEY'
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE HOLD AS OAS0002D FORMAT FOCUS INDEX STATIC_NUMBER
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
UNITS=IN,
PAGESIZE='SCREEN',
LEFTMARGIN=0.000000,
RIGHTMARGIN=0.000000,
TOPMARGIN=0.000000,
BOTTOMMARGIN=0.000000,
SQUEEZE=ON,
ORIENTATION=PORTRAIT,
$
TYPE=REPORT,
GRID=OFF,
FONT='ARIAL',
SIZE=9,
COLOR='BLACK',
BACKCOLOR='NONE',
STYLE=NORMAL,
RIGHTGAP=0.125000,
TOPGAP=0.013889,
BOTTOMGAP=0.027778,
$

ENDSTYLE
END
JOIN CLEAR *
JOIN STATIC_NUMBER IN OAS0002C TO CIF_KEY IN OAS0002D AS J3
DEFINE FILE OAS0002C

DISPLAY_ACCOUNT_TYPE/A8=IF (CUSTOMER_ACCT_CLASSIFICATION EQ 'B') THEN 'Business' ELSE
IF (CUSTOMER_ACCT_CLASSIFICATION EQ 'R') THEN 'Retail' ELSE ' ';
DISPLAY_SEGMENT/A25=IF (CUSTOMER_ACCT_CLASSIFICATION EQ 'B') THEN STATIC_BUS_SEGMENT_DESCRIPTION ELSE
IF (CUSTOMER_ACCT_CLASSIFICATION EQ 'R') THEN STATIC_IRM_SEGMENT_DESCRIPTION ELSE ' ';

SELECT_01/A1=IF (DISPLAY_SEGMENT EQ 'Micro') AND (BUSINESS_COMM_OUT EQ 0) THEN 'N' ELSE 'Y';
SELECT_02/A1=IF (PRIMARY_BUSINESS_ACCOUNT EQ 'Y') THEN 'Y' ELSE 'N';
SELECT_03/A1=IF (STATIC_BUS_SEGMENT_FLAG NE 'C') AND (STATIC_BUS_SEGMENT_FLAG NE 'D') THEN 'Y' ELSE
IF (STATIC_BUS_SEGMENT_FLAG EQ 'C') AND (BUSINESS_SC_BCK GE 50000) THEN 'Y' ELSE
IF (STATIC_BUS_SEGMENT_FLAG EQ 'C') AND (BUSINESS_12MNTH_AVG_DEP GE 100000) THEN 'Y' ELSE
IF (STATIC_BUS_SEGMENT_FLAG EQ 'D') AND (BUSINESS_SC_BLN NE 0) THEN 'Y' ELSE
IF (STATIC_BUS_SEGMENT_FLAG EQ 'D') AND (BUSINESS_SC_BSL NE 0) THEN 'Y' ELSE
IF (STATIC_BUS_SEGMENT_FLAG EQ 'D') AND (BUSINESS_SC_SBL NE 0) THEN 'Y' ELSE
IF (STATIC_BUS_SEGMENT_FLAG EQ 'D') AND (BUSINESS_SC_SLI NE 0) THEN 'Y' ELSE 'N';
DISPLAY_BIRTH_DATE/YYMD=IF (CUSTOMER_ACCT_CLASSIFICATION EQ 'R') THEN HDATE(BIRTH_DATE,'YYMD') ELSE 0;
DISPLAY_DATE_DATABASE/YYMD=HDATE(DATE_DATABASE,'YYMD');
DISPLAY_OFFICER_CODE/A10=' ';
DISPLAY_OFFICER_NAME/A40=' ';
DISPLAY_OFFICER_STATUS/A8=IF (OFFICER_STATUS_CODE EQ 'A') THEN 'Active' ELSE 'Inactive';
SMALLER_UNIQUE_SERVICES/I3=UNIQUE_SERVICES;
DISPLAY_UNIQUE_SERVICES/A2=IF (SMALLER_UNIQUE_SERVICES LT 10) THEN ' ' | EDIT(SMALLER_UNIQUE_SERVICES,'$$9')
ELSE EDIT(SMALLER_UNIQUE_SERVICES,'$99');
DISPLAY_DUAL_ACCOUNTS/A03=IF (BUSINESS_ACCOUNTS NE 0) AND (RETAIL_ACCOUNTS NE 0) THEN 'YES' ELSE ' ';
DISPLAY_SNS_ACCOUNTS/A03=IF (SNS_ACCOUNTS NE 0) THEN 'YES' ELSE ' ';
DISPLAY_12MNTH_AVG_DEP/D12C=BUSINESS_12MNTH_AVG_DEP;
DISPLAY_TOT_INVEST/D12C=BUSINESS_TOT_INVEST;
DISPLAY_COMM_OUT/D12C=BUSINESS_COMM_OUT;
DISPLAY_TOTAL_TRUST_BAL/D12C=BUSINESS_TOTAL_TRUST_BAL;
RECORD_CODE_01/A02='01';
RECORD_CODE_02/A02='02';
BLANK_STATIC_NUMBER/A25V=' ';
BLANK_DISPLAY_ACCOUNT_TYPE/A08=' ';
BLANK_PRIMARY_CENTER_NUMBER/A08V=' ';
BLANK_OFFICER_CODE/A10V=' ';
BLANK_OFFICER_NAME/A40V=' ';
BLANK_OFFICER_TITLE/A40V=' ';
BLANK_OFFICER_CENTER_NUMBER/A08V=' ';
BLANK_DISPLAY_OFFICER_STATUS/A08=' ';
BLANK_DISPLAY_OFFICER_CODE/A10=' ';
BLANK_DISPLAY_OFFICER_NAME/A40=' ';
BLANK_SOCIAL_SEC_TAX_ID/A09V=' ';
BLANK_NAME1/A40V=' ';
BLANK_RES_ADDRESS1/A40V=' ';
BLANK_RES_CITY/A25V=' ';
BLANK_RES_STATE/A02V=' ';
BLANK_DISPLAY_BIRTH_DATE/YYMD=0;
BLANK_DISPLAY_UNIQUE_SERVICES/A02=' ';
BLANK_DISPLAY_SEGMENT/A25=' ';
BLANK_CUSTOMER_ACCT_CLASSIFICATION/A01V=' ';
BLANK_DISPLAY_DUAL_ACCOUNTS/A03=' ';
BLANK_DISPLAY_SNS_ACCOUNTS/A03=' ';
END
TABLE FILE OAS0002C
SUM
BLANK_DISPLAY_ACCOUNT_TYPE AS 'DISPLAY_ACCOUNT_TYPE'
BLANK_PRIMARY_CENTER_NUMBER AS 'PRIMARY_CENTER_NUMBER'
BLANK_OFFICER_CODE AS 'OFFICER_CODE'
BLANK_OFFICER_NAME AS 'OFFICER_NAME'
BLANK_OFFICER_TITLE AS 'OFFICER_TITLE'
BLANK_OFFICER_CENTER_NUMBER AS 'OFFICER_CENTER_NUMBER'
BLANK_DISPLAY_OFFICER_STATUS AS 'DISPLAY_OFFICER_STATUS'
BLANK_DISPLAY_OFFICER_CODE AS 'DISPLAY_OFFICER_CODE'
BLANK_DISPLAY_OFFICER_NAME AS 'DISPLAY_OFFICER_NAME'
BLANK_SOCIAL_SEC_TAX_ID AS 'SOCIAL_SEC_TAX_ID'
BLANK_NAME1 AS 'NAME1'
BLANK_RES_ADDRESS1 AS 'RES_ADDRESS1'
BLANK_RES_CITY AS 'RES_CITY'
BLANK_RES_STATE AS 'RES_STATE'
BLANK_DISPLAY_BIRTH_DATE AS 'DISPLAY_BIRTH_DATE'
BLANK_DISPLAY_UNIQUE_SERVICES AS 'DISPLAY_UNIQUE_SERVICES'
BLANK_DISPLAY_SEGMENT AS 'DISPLAY_SEGMENT'
BLANK_CUSTOMER_ACCT_CLASSIFICATION AS 'CUSTOMER_ACCT_CLASSIFICATION'
BLANK_DISPLAY_DUAL_ACCOUNTS AS 'DISPLAY_DUAL_ACCOUNTS'
BLANK_DISPLAY_SNS_ACCOUNTS AS 'DISPLAY_SNS_ACCOUNTS'
DISPLAY_12MNTH_AVG_DEP AS '12MNTH_AVG_DEP'
DISPLAY_TOT_INVEST AS 'TOT_INVEST'
DISPLAY_COMM_OUT AS 'COMM_OUT'
DISPLAY_TOTAL_TRUST_BAL AS 'TOTAL_TRUST_BAL'
BY
DISPLAY_DATE_DATABASE
BY
PRIMARY_REGION_NAME
BY
PRIMARY_BRANCH_NAME
BY
ID
BY
BLANK_STATIC_NUMBER AS 'STATIC_NUMBER'
BY
RECORD_CODE_01 AS 'RECORD_CODE'
WHERE ( SELECT_01 EQ 'Y' );
WHERE ( SELECT_02 EQ 'Y' );
WHERE ( SELECT_03 EQ 'Y' );
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE HOLD AS OAS0002E FORMAT FOCUS
ON TABLE SET STYLE *
UNITS=IN,
PAGESIZE='SCREEN',
LEFTMARGIN=0.000000,
RIGHTMARGIN=0.000000,
TOPMARGIN=0.000000,
BOTTOMMARGIN=0.000000,
SQUEEZE=ON,
ORIENTATION=LANDSCAPE,
$
TYPE=REPORT,
FONT='ARIAL',
SIZE=9,
COLOR='BLACK',
BACKCOLOR='NONE',
STYLE=NORMAL,
RIGHTGAP=0.125000,
TOPGAP=0.013889,
BOTTOMGAP=0.027778,
$

ENDSTYLE
END
TABLE FILE OAS0002C
SUM
DISPLAY_ACCOUNT_TYPE
PRIMARY_CENTER_NUMBER
OFFICER_CODE
OFFICER_NAME
OFFICER_TITLE
OFFICER_CENTER_NUMBER
DISPLAY_OFFICER_STATUS
DISPLAY_OFFICER_CODE
DISPLAY_OFFICER_NAME
SOCIAL_SEC_TAX_ID
NAME1
RES_ADDRESS1
RES_CITY
RES_STATE
DISPLAY_BIRTH_DATE
DISPLAY_UNIQUE_SERVICES
DISPLAY_SEGMENT
CUSTOMER_ACCT_CLASSIFICATION
DISPLAY_DUAL_ACCOUNTS
DISPLAY_SNS_ACCOUNTS
DISPLAY_12MNTH_AVG_DEP AS '12MNTH_AVG_DEP'
DISPLAY_TOT_INVEST AS 'TOT_INVEST'
DISPLAY_COMM_OUT AS 'COMM_OUT'
DISPLAY_TOTAL_TRUST_BAL AS 'TOTAL_TRUST_BAL'
BY
DISPLAY_DATE_DATABASE
BY
PRIMARY_REGION_NAME
BY
PRIMARY_BRANCH_NAME
BY
ID
BY
STATIC_NUMBER
BY
RECORD_CODE_02 AS 'RECORD_CODE'
WHERE ( SELECT_01 EQ 'Y' );
WHERE ( SELECT_02 EQ 'Y' );
WHERE ( SELECT_03 EQ 'Y' );
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE HOLD AS OAS0002F FORMAT FOCUS
ON TABLE SET STYLE *
UNITS=IN,
PAGESIZE='SCREEN',
LEFTMARGIN=0.000000,
RIGHTMARGIN=0.000000,
TOPMARGIN=0.000000,
BOTTOMMARGIN=0.000000,
SQUEEZE=ON,
ORIENTATION=LANDSCAPE,
$
TYPE=REPORT,
FONT='ARIAL',
SIZE=9,
COLOR='BLACK',
BACKCOLOR='NONE',
STYLE=NORMAL,
RIGHTGAP=0.125000,
TOPGAP=0.013889,
BOTTOMGAP=0.027778,
$
TYPE=TITLE,
STYLE=BOLD,
$
TYPE=TABHEADING,
SIZE=12,
STYLE=BOLD,
$
TYPE=TABFOOTING,
SIZE=12,
STYLE=BOLD,
$
TYPE=HEADING,
SIZE=12,
STYLE=BOLD,
$
TYPE=FOOTING,
SIZE=12,
STYLE=BOLD,
$
TYPE=SUBHEAD,
SIZE=10,
STYLE=BOLD,
$
TYPE=SUBFOOT,
SIZE=10,
STYLE=BOLD,
$
TYPE=SUBTOTAL,
BACKCOLOR=RGB(210 210 210),
$
TYPE=ACROSSVALUE,
SIZE=9,
$
TYPE=ACROSSTITLE,
STYLE=BOLD,
$
TYPE=GRANDTOTAL,
BACKCOLOR=RGB(210 210 210),
STYLE=BOLD,
$
ENDSTYLE
END
USE
OAS0002E AS OAS0002E
OAS0002F AS OAS0002E
END
TABLE FILE OAS0002E
PRINT
PRIMARY_BRANCH_NAME AS 'Branch'
ID AS 'Household ID'
DISPLAY_ACCOUNT_TYPE AS 'Account,Type'
STATIC_NUMBER/A15 AS 'Static,CIF,Number'
PRIMARY_CENTER_NUMBER AS 'Primary,Center,Number'
OFFICER_CODE AS 'Current,Primary CIF,Officer #'
OFFICER_NAME AS 'Current,Primary CIF,Officer Name'
OFFICER_TITLE AS 'Current,Primary CIF,Officer Title'
OFFICER_CENTER_NUMBER AS 'Officer,Center,Number'
DISPLAY_OFFICER_STATUS AS 'Current,Primary CIF,Officer Status'
DISPLAY_OFFICER_CODE AS 'Primary,Officer Number,(To Be Assigned)'
DISPLAY_OFFICER_NAME AS 'Primary,Officer Name,(To Be Assigned)'
SOCIAL_SEC_TAX_ID AS 'SSN/TaxID'
NAME1 AS 'Primary,Name'
RES_ADDRESS1 AS 'Address'
RES_CITY AS 'City'
RES_STATE AS 'State'
DISPLAY_BIRTH_DATE AS 'Birth,Date'
12MNTH_AVG_DEP AS '12 Month,Average,Deposits'
TOT_INVEST AS 'Total,Investments'
COMM_OUT AS 'Committed Line/,Outstanding Loan,Balance'
TOTAL_TRUST_BAL AS 'Total,Trust,Balances'
DISPLAY_UNIQUE_SERVICES AS 'Number,of,Services'
DISPLAY_SEGMENT AS 'Segment'
DISPLAY_SNS_ACCOUNTS AS 'Scott and, Stringfellow,Account'
BY PRIMARY_REGION_NAME NOPRINT
ON PRIMARY_REGION_NAME PAGE-BREAK
BY DISPLAY_DATE_DATABASE NOPRINT
BY PRIMARY_BRANCH_NAME NOPRINT
BY ID NOPRINT
BY RECORD_CODE NOPRINT
BY STATIC_NUMBER NOPRINT
BY CUSTOMER_ACCT_CLASSIFICATION NOPRINT
BY OFFICER_CODE NOPRINT
HEADING
"Inactive and Unassigned Relationship Management Report AS-OF: " "
"Region : " "
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT EXL2K
ON TABLE SET STYLE *
UNITS=IN,
PAGESIZE='SCREEN',
LEFTMARGIN=0.000000,
RIGHTMARGIN=0.000000,
TOPMARGIN=0.000000,
BOTTOMMARGIN=0.000000,
SQUEEZE=ON,
ORIENTATION=LANDSCAPE,
$
DEFMACRO=COND0001,
MACTYPE=RULE,
WHEN=N5 EQ '01',
$
DEFMACRO=COND0002,
MACTYPE=RULE,
WHEN=N5 EQ '02',
$
TYPE=REPORT,
FONT='ARIAL',
SIZE=9,
COLOR='BLACK',
BACKCOLOR='NONE',
STYLE=NORMAL,
RIGHTGAP=0.125000,
TOPGAP=0.013889,
BOTTOMGAP=0.027778,
$
TYPE=DATA,
COLUMN=N10,
JUSTIFY=RIGHT,
MACRO=COND0002,
$
TYPE=DATA,
BACKCOLOR='SILVER',
STYLE=BOLD,
MACRO=COND0001,
$
TYPE=TITLE,
STYLE=BOLD,
$
TYPE=TABHEADING,
SIZE=12,
STYLE=BOLD,
$
TYPE=TABFOOTING,
SIZE=12,
STYLE=BOLD,
$
TYPE=HEADING,
SIZE=12,
STYLE=BOLD,
$
TYPE=FOOTING,
SIZE=12,
STYLE=BOLD,
$
TYPE=SUBHEAD,
SIZE=10,
STYLE=BOLD,
$
TYPE=SUBFOOT,
SIZE=10,
STYLE=BOLD,
$
TYPE=SUBTOTAL,
BACKCOLOR=RGB(210 210 210),
$
TYPE=ACROSSVALUE,
SIZE=9,
$
TYPE=ACROSSTITLE,
STYLE=BOLD,
$
TYPE=GRANDTOTAL,
BACKCOLOR=RGB(210 210 210),
STYLE=BOLD,
$
ENDSTYLE
END

Looks like you are having some Write permissions issues with the directory.

quote:

This report was written by a consultant, so it looks especially complicating

Surely you are not implying that all consultants write complicating code!

That selection criteria looks scary!

Not sure if "substr(t2.primary_center_number, 1, 3) <> substr(t1.officer_center_number, 1, 3" is efficient.

That whole bit with "customer_acct_classification AND static_irm_segment_description IN ('Core', 'Top Core')" could probably simplified.

How many rows are you expecting as the result?

Why not replace all the JOINs with SQL, why only one of the JOINs?

With the FOCUS code, did you turn the SQL trace messages on to see if any aggregate or JOIN messages would occur?

To make it less complicated, get rid of the STYLE code when you are HOLDing a file, you do not need it.

Also, are you saying that this is your "new" version trying to use the SQL passthru? If so then you are preparing the view and then not using it -

TABLE FILE AX_CIF
PRINT
ID
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE HOLD AS OAS0002A

should be TABLE FILE SQLOUT.

Other than that, try to run succesive sections of code by placing an -EXIT between them and check the output and timings. Use APP HOLD [foldername] to capture the .FTM and .MAS produced so that you can check them.

One question, I take it that the second TABLE request (with the joins) is going against some more ORACLE tables? If so then I suggest this is where you concentrate your efforts. Joining a HOLD file against any SQL based tables is going to be less efficient as WebFOCUS will have to pull what data it can determine is required from predicates, and then perform the JOIN in WebFOCUS. Not the best way to tackle it. As the first extract seems to be purely to determine what IDs are required from the second extract, could you SAVE the data to be used within an SQL "IN" clause? You really need to analyse the SQL in the first and what the second extract does.

It will be a long process to make this more efficient, so good luck.

T

Also the first SELECT might be better by using SELECT DISTINCT t1.id ......

T