Last weeks I've been doing some Data Migrator work at a client, and the last few days I've run into an error I can't quite explain or solve (I've got a work-around, but I'm sure there must be a better way).
What I'm trying to do is actually quite simple: I want to create a Transform somewhere in the procedure which uses fields from 2 different tables to generate a SUMmed amount.
- amount 1
- amount 2
Depending on the amounttype amount 1 and amount 2 should be positive or negative (* -1). since you can't do the transform on a single table I've done this at the SQL stage in the data flow.
Whenever I try a construction like this it results in a "(FOC14006) SQL TRANSLATOR ERROR NULL DBE hTblDBE" error.
I've now even simplified the transform to just check the amounttype field and just SUM the two fields; this results in the same error.
When looking through techsupport I've noticed that this error can be caused by SQL being generated which is not compliant with the target DBMS (SQL Server in this case). But I've typed in a SQL CASE manually which workes perfectly in SQL Management Studio (SUM(CASE T6.AMOUNTTYPE WHEN 'Lasten' THEN 1 ELSE T1.AMOUNT1 +T1.AMOUNT2 END) AS BUDGET_TOT), but which generates the error as well.
What does the error mean exactly? Am I trying to do something that's impossible, or am I looking at it the wrong way?
All tables are in SQL Server 2005 (MASTERfiles are refreshed) on a SQL 2005 Adapter in iWay Data Migrator Server 766.This message has been edited. Last edited by: <Jochem>,
Why is that the case?
Can you post the script behind the ETL proces? Or send it by email?
About the Transform on a single table; the Transform would need to access fields from two seperate tables. If I open the painter in DM on a single table I than only have the ability to use fields from that one table (or could I code this differently?).
The code of the DM-procedure:
-*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
In the code you can find "CASE_TEST" and "DEFINE_TEST" which both generate the same error independently.
you can create fields on the fly during an ETL proces.
I take a look on this and try to understand what you want.
I will come back on this tomorrow.
I see you do the same calculation 2 times:
DEFINE BUDGET_TOT/P21.4=(BUDGET_BEDRAG_DEBET + BUDGET_BEDRAG_CREDIT); $
SUM(T1.BUDGET_BEDRAG_DEBET +T1.BUDGET_BEDRAG_CREDIT ) AS Bedrag ,
The last one should be no problem, the missing blank after + can be a problem. Try this T1.BUDGET_BEDRAG_DEBET + T1.BUDGET_BEDRAG_CREDIT
The CASE you have is on a summed field. Because this is a AN field, I think it would be better to do this in a define so you CASE on recordlevel.
Test: WF 8.2
Prod: WF 8.2
DB: Progress, REST, IBM UniVerse/UniData, SQLServer, MySQL, PostgreSQL, Oracle, Greenplum, Athena.
There was an issue that resulted in the FOCUS 14006 error when a JOIN was used that contained an ON condition with constant values, of which you have several. That issue has been addressed in the latest maintenance release 7.6.8 which I would suggest you apply.
That said you should be able to avoid this issue by ensuring that your query can use APT. Automatic Pass Through means that SQL select is passed through to the underlying relational database. Use of APT for queries provides the best througput, so the best practice is to consruct queries that can use APT if at all possible.
Requirments for APT include:
(A) That all of your source tables are in the same database, which you say is MS SQL Server, and the same connection which I can't tell without the synonyms.
(B) That no DEFINE fields in the synonyms or source transformations are used in query. Without the synonyms I can't tell if there are DEFINE fields, but there is just one source transformation.
As Fran noted it doesn't look like you need that transformation.
Then there is the matter of the CASE statement. It's not clear what you want to accomplish here with a SUM of 1 and -1 values, but could you also try removing that to see if that works.
You're the man!
It were in fact the constants within the JOIN that caused the problem. After simply moving those to the WHERE statement everything worked like it should.
What I was trying to do (which now works here) was this:
SUM( CASE T4.GRB_STR_NIV7_OMS WHEN 'Lasten' THEN -1 *(T1.BUDGET_BEDRAG_DEBET +T1.BUDGET_BEDRAG_CREDIT ) ELSE T1.BUDGET_BEDRAG_DEBET +T1.BUDGET_BEDRAG_CREDIT END ) AS AMOUNT
Depending on a string value in table T4 I wanted to the two amounts from table T1 SUMmed and made either positive or negative. (It's a financial overview by the way). I've only put the 1 and -1 numbers in my previous post as an example.
Thanks for your help all!
|Powered by Social Strata|