Hi all,

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.
For example:

Table 1:
- amount 1
- amount 2

Table 2:
- Amounttype

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>,

Jochem

quote:

since you can't do the transform on a single table

Why is that the case?

Can you post the script behind the ETL proces? Or send it by email?

Hi Frank,

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.

Thanks!

Jochem

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.

Hi jochem,

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.

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.

Hi Clif,

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!