quote:since you can't do the transform on a single table
Frank | |
prod: WF 7.6.10 platform Windows, databases: msSQL2000, msSQL2005, RMS, Oracle, Sybase,IE7 test: WF 7.6.10 on the same platform and databases,IE7 |
-*DM_JOB_TYPE=1 -*DM_USERID=levu_webfocus -*************************************************** -:START_PRC SET PANEL=9999 SET NWTIMESTAMP=ON SET MORE=OFF SET 2PARTNAME=ON -RUN -*[Variables to Control Request] -SET &&CM__TARGET = 'test_exploitatie'; -SET &&CM__AUTHOR = 'levu_webfocus'; -SET &&CM__REQUEST = 'test_exploitatie_budget_totalen'; -SET &&CM__RETURN = 0; -SET &&CM__FOCCPU = &FOCCPU.EVAL; -SET &DISP_JOB = PTHDAT (31, 'test_exploitatie_budget_totalen','A31'); -DEFAULT &DBMSERROR = 10000000 -DEFAULT &STARTAT = 0 -DEFAULT &STOPAT = 1000000000 -TYPE (ICM18122) Request - test_exploitatie_budget_totalen (Owner: levu_webfocus) submitted. -GOTO :DEP_MAIN; -:DEP_MAIN -TYPE (ICM18742) test_exploitatie type MS SQL Server Existing target SET CASESTAT=EXTENDED SQL SET UPCASE=OFF; END LOAD MASTER dp_fact_budget EX -lines 2 EDAPUT MASTER,dp_fact_budget,A,MEM, DEFINE BUDGET_TOT/P21.4=(BUDGET_BEDRAG_DEBET + BUDGET_BEDRAG_CREDIT); $ -RUN -SET &&CM__RETURN = &FOCERRNUM; -IF (&&CM__RETURN NE 0) GOTO :ENDJOB; -TYPE (ICM18429) Issuing PREPARE SQL PREPARE SQLIN FROM SELECT T2.JAAR AS Jaar , T2.MAAND AS Maand , T3.GRB_REKENING AS Grb_rek , T3.GRB_OMS1 AS Omschr_grb_rek , T4.GRB_STR_NIV3 , T4.GRB_STR_NIV3_OMS , T4.GRB_STR_NIV7_OMS , T4.GRB_STR_NIV9_OMS , T6.KPL_NIV4 AS Org_eenheid , T6.KPL_OMS4 AS Omschr_org_eenh , T6.KPL_NIV5 AS Divisie , T6.KPL_OMS5 AS Omschr_divisie , T6.KPL_NIV6 AS Afdeling , T6.KPL_OMS6 AS Omschr_afdeling , T6.KPL_NIV1 AS Kostenplaats , T6.KPL_OMS1 AS Omschr_kpl , T6.BUDGETHOUDER AS Budgethouder , SUM(T1.BUDGET_BEDRAG_DEBET +T1.BUDGET_BEDRAG_CREDIT ) AS Bedrag , SUM(T1.BUDGET_AANTAL ) AS FTE , SUM( CASE T4.GRB_STR_NIV7_OMS WHEN 'Lasten' THEN -1 ELSE 1 END ) AS CASETEST , SUM(T1.BUDGET_TOT ) AS DEFINE_TEST FROM (((((dp_fact_budget T1 INNER JOIN dp_dim_tijd_rapportageperiode T2 ON T1.FIN_TIJD_ID = T2.TIJD_ID AND T2.JAAR >=2007 ) INNER JOIN dp_dim_grootboek T3 ON T1.GRB_ID = T3.GRB_ID AND SUBSTR (T3.GRB_REKENING FROM 1 FOR 1 ) NOT IN ( '5' , '6' ) AND T3.GRB_REKENING >='400000' ) INNER JOIN dp_dim_grb_structuur T4 ON T3.GRB_STR_ID = T4.GRB_STR_ID AND T4.GRB_STR_NIV7_OMS <>'onbekend' ) INNER JOIN dp_dim_administratie T5 ON T1.ADMIN_ID = T5.ADMIN_ID AND T5.ADMIN_CODE = 'DIAK' ) INNER JOIN dp_dim_kostenplaats T6 ON T1.KPL_ID = T6.KPL_ID AND T1.ADMIN_ID = T6.ADMIN_ID ) WHERE T6.KPL_NIV1 = '1001' GROUP BY T2.JAAR , T2.MAAND , T3.GRB_REKENING , T3.GRB_OMS1 , T4.GRB_STR_NIV3 , T4.GRB_STR_NIV3_OMS , T4.GRB_STR_NIV7_OMS , T4.GRB_STR_NIV9_OMS , T6.KPL_NIV4 , T6.KPL_OMS4 , T6.KPL_NIV5 , T6.KPL_OMS5 , T6.KPL_NIV6 , T6.KPL_OMS6 , T6.KPL_NIV1 , T6.KPL_OMS1 , T6.BUDGETHOUDER END -RUN -SET &&CM__RETURN = IF &FOCERRNUM EQ 14104 THEN 0 ELSE &FOCERRNUM; -IF (&&CM__RETURN NE 0) GOTO :ENDJOB; -IF (&SQLAPT EQ 'APT') GOTO :SKIPHOLD; -TYPE (ICM18440) Request will process data via NON-Pass Through (NON-APT) -TYPE (ICM18451) HOLD file will be created for output file named: SQLIN. SQL EXECUTE SQLIN; TABLE ON TABLE HOLD AS SQLIN FORMAT INTERNAL IF RECORDLIMIT EQ &STOPAT END -RUN -SET &&CM__RETURN = &FOCERRNUM; -IF (&&CM__RETURN NE 0) GOTO :ENDJOB; -:SKIPHOLD -TYPE (ICM18743) Starting Load MODIFY FILE test_exploitatie FIXFORM FROM SQLIN ALIAS PROPAGATE GOTO MATCHIT1 CASE MATCHIT1 COMPUTE GRB_STR_NIV3/A9V MISSING ON=E05; GRB_STR_NIV3_OMS/A200V MISSING ON=E06; GRB_STR_NIV7_OMS/A200V MISSING ON=E07; GRB_STR_NIV9_OMS/A200V MISSING ON=E08; FTE/P17.4 MISSING ON=E19; JAAR/P20=E01; MAAND/P20=E02; GRB_REK/A9V=E03; OMSCHR_GRB_REK/A80V MISSING ON=E04; ORG_EENHEID/A50V MISSING ON=E09; OMSCHR_ORG_EENH/A50V MISSING ON=E10; DIVISIE/A50V MISSING ON=E11; OMSCHR_DIVISIE/A50V MISSING ON=E12; AFDELING/A50V MISSING ON=E13; OMSCHR_AFDELING/A50V MISSING ON=E14; KOSTENPLAATS/A50V=E15; OMSCHR_KPL/A50V MISSING ON=E16; BUDGETHOUDER/A100V MISSING ON=E17; BEDRAG/P21.4 MISSING ON=E18; MATCH JAAR ON MATCH INCLUDE ON NOMATCH INCLUDE GOTO TOP ENDCASE CASE AT START START &STARTAT STOP &STOPAT STOP DBMSERRORS &DBMSERROR LOG DBMSERR MSG OFF LOG DUPL MSG OFF LOG INVALID MSG OFF LOG NOMATCH MSG OFF LOG FORMAT MSG OFF LOG ACCEPT MSG OFF LOG TRANS MSG OFF CHECK 1000 ENDCASE DATA VIA SQLGET END -RUN -TYPE (ICM18744) Ending Load -SET &&CM__RETURN = &FOCERRNUM; -SET &&CM__RETURN = IF (&&CM__RETURN EQ 1416) AND (&DBMSERR LT &DBMSERROR) - THEN 0 ELSE &&CM__RETURN; -:ENDJOB -TYPE (ICM18040) Return Code = &&CM__RETURN SET CASESTAT=OFF SQL SET UPCASE=ON; END SET EMGSRV=OFF SQL PURGE SQLIN; END FI SQLIN CLEAR SET EMGSRV=ON SQL SQLMSS COMMIT WORK; END -RUN -TYPE (ICM18076) Request: test_exploitatie_budget_totalen - finished processing -SET &&CM__FOCCPU = &FOCCPU.EVAL - &&CM__FOCCPU; -TYPE (ICM18007) CPU Time : &&CM__FOCCPU -*[Main Condition] -*[Main End] -*[Dependence] -:ENDDEP SET PANEL=0 SET NWTIMESTAMP=OFF SET MORE=ON SET 2PARTNAME=OFF -RUN
Frank | |
prod: WF 7.6.10 platform Windows, databases: msSQL2000, msSQL2005, RMS, Oracle, Sybase,IE7 test: WF 7.6.10 on the same platform and databases,IE7 |