I am getting this error with the following flow(concatenated for brevity sake)

SQL PREPARE SQLIN FROM
SELECT DISTINCT
T1.GL_ACCOUNT , --G/L ACCOUNT
T1.GL_ACCT_TITLE , --ACCT TITLE
T1.GL_ACCT_CLASS , --GL ACCT CLASS 1-5
CASE WHEN GL_ACCT_CLASS = 1 THEN 'ASSET' WHEN GL_ACCT_CLASS = 2 THEN 'LIABILITY' WHEN GL_ACCT_CLASS = 3 THEN 'OWNERS EQUITY' WHEN GL_ACCT_CLASS = 4 THEN 'INCOME' WHEN GL_ACCT_CLASS = 5 THEN 'EXPENSE' ELSE 'OTHER' END AS Acct_desc
FROM
dwdstg_glfamast T1
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 = IF &LINES EQ 0 THEN 18708 ELSE &FOCERRNUM;
-IF (&&CM__RETURN NE 0) GOTO :ENDJOB;
-:SKIPHOLD

-TYPE (ICM18701) Bulk Insert active. INSERTSIZE = 1000
SQL DB2 SET INSERTSIZE 1000
-TYPE (ICM18743) Starting Load

MODIFY FILE dwdbas_gl_acct_t
FIXFORM FROM SQLIN ALIAS PROPAGATE
GOTO MATCHIT1

CASE MATCHIT1
COMPUTE
GL_ACCT_KEY/P7 MISSING ON=E01;
GL_ACCT_NM/A40V=E02;
GL_ACCT_TYP_ID/I11=E03;
GL_ACCT_DESC/A80V MISSING ON=E04;
MATCH GL_ACCT_ID
ON MATCH INCLUDE
ON NOMATCH INCLUDE
GOTO TOP
ENDCASE


What confuses me is that the only key on my target table is the GL_ACCT_ID field and it is an automatically generated ID from the system.Why would it be including duplicate segments? When I run this flow, the log tells me 1 record was accepted and processed, 0 records were included. However, when I test the transform in DMC, everything looks fine.

This message has been edited. Last edited by: T.Peters,

quote:

automatically generated ID

Never had too much success with these and I will admit that I always resort to alternate methods.

Question: Do you have permissions to create temporary DB2 tables using instream SQL in WF?
If the answer is in the affirmative then I would suggest creating the temp table in DB2 and then use update table from select from temp_table. Much less stress and if your forté is SQL then easier for you to support

T

The problem turned out that I didnt have the primary key that I thought I had. The metadata showed a PK on the Acct_ID field, but the physical file didn't really have it, so I was attempting to insert duplicate Acct_keys.

Tony, I've really had problems using temp tables with this thing. I THINK I can do them, but they're kind of a new idea to me. Think you can give me something to try to see if it will work? I'm always up for some good education on WF.