Why doesn't this SQL work? [JD Edwards DB2]

Question

I am attempting to write some SQL that will be used to select data for a report. The data is coming from JD Edwards, using the DB2 database on AS/400.

I have written the following SQL query:

SELECT F3112.WLDOCO AS DOC_NO,
       F3112.WLDCTO AS DOC_TYPE,
       F3112.WLCTS4 AS UDL_AMOUNT,
       F3112.WLCTS9 AS UDL_HOURS,
       F3112.WLLABA AS RUN_LABOR_ACT,
       F3112.WLMACA AS RUN_MACHINE_ACT,
       F0911.GLSBL  AS OBJ_ACCT,
       F0911.GLAA   AS GL_AMOUNT,
       F0911.GLU    AS GL_UNITS,
       F4801.WASRST AS WO_STATUS
FROM   PROD2DTA.F3112 F3112
       INNER JOIN PROD2DTA.F0911 F0911
         ON F3112.WLDOCO = CAST(F0911.GLSBL AS INTEGER)
       INNER JOIN PROD2DTA.F4801 F4801
         ON F3112.WLDOCO = F4801.WADOCO
WHERE  F0911.GLOBJ = '6855'
       AND F4801.WASRST BETWEEN '30' AND '95'

Where:

F3112 - Work Order Routing

F4801 - Work Order Master

WASRST (WO status code) is a string of length 2. It's a set of enumerated values.

F0911 - Account Ledger

GLSBL (G/L subledger) may contain the document number from Work Order Routing (F3112.WLDOCO). The important thing to note is that GLSBL is a string, length 8 and the values are left padded with zeros. F3112.WLDOCO is a numeric value with length 8 (no padding). This is why I have the CAST in the JOIN clause.
GLOBJ (Object account) is a string of length 6. I do not know if there is any padding.

This query will not run. It gives me a somewhat cryptic "data conversion or mapping error". I haven't been able to figure out what the offending SQL is.

Now here's the weird part. If I modify this query slighty to join F3112 with ONLY F4801 OR F0911, either form of the query works. I just can't get them to work together as one query (Is there a clue here?).

So, to illustrate:

SELECT F3112.WLDOCO AS DOC_NO,
       F3112.WLDCTO AS DOC_TYPE,
       F3112.WLCTS4 AS UDL_AMOUNT,
       F3112.WLCTS9 AS UDL_HOURS,
       F3112.WLLABA AS RUN_LABOR_ACT,
       F3112.WLMACA AS RUN_MACHINE_ACT,
       F0911.GLSBL AS OBJ_ACCT,
       F0911.GLAA   AS GL_AMOUNT,
       F0911.GLU    AS GL_UNITS
FROM   PROD2DTA.F3112 F3112
       INNER JOIN PROD2DTA.F0911 F0911
         ON F3112.WLDOCO = CAST(F0911.GLSBL AS INTEGER)
WHERE  F0911.GLOBJ = '6855'

and

SELECT F3112.WLDOCO AS DOC_NO,
       F3112.WLDCTO AS DOC_TYPE,
       F3112.WLCTS4 AS UDL_AMOUNT,
       F3112.WLCTS9 AS UDL_HOURS,
       F3112.WLLABA AS RUN_LABOR_ACT,
       F3112.WLMACA AS RUN_MACHINE_ACT,
       F4801.WASRST AS WO_STATUS
FROM   PROD2DTA.F3112 F3112
       INNER JOIN PROD2DTA.F4801 F4801
         ON F3112.WLDOCO = F4801.WADOCO
WHERE  F4801.WASRST BETWEEN '30' AND '95'

These both work just fine.

what is the version of DB2 ? do you run this query directly for the AS400 or you run it on DB2 LUW using FEDERATION ? i've already faced the similar issu on DB2 LUW, it was a bug (APAR IT34222) — mshabou, Mar 19 '21 at 00:04
what is the time of F4801.WADOCO ? can you upload the DDL of the two tables. — mshabou, Mar 19 '21 at 00:08
Did you try `ON DIGITS(F3112.WLDOCO) = F0911.GLSBL`, does it work ? — nfgl, Mar 19 '21 at 08:08
@nfgl: The query executes and returns results. Not sure if they're the results I need, but it's progress. Why did my approach not work and this one seems to? — Joseph Gagnon, Mar 19 '21 at 12:47
@JosephGagnon F0911.GLSBL probably contains only blanks sometimes, or non digits characters. But your approach is maybe better, if it converts less lines than mine, maybe try to use F0911 as first table — nfgl, Mar 19 '21 at 15:54

score 0 · Answer 1 · answered Mar 18 '21 at 20:29

Please execute this and let me know if you face any error.

Select * from 
      (SELECT F3112.WLDOCO AS DOC_NO,
       F3112.WLDCTO AS DOC_TYPE,
       F3112.WLCTS4 AS UDL_AMOUNT,
       F3112.WLCTS9 AS UDL_HOURS,
       F3112.WLLABA AS RUN_LABOR_ACT,
       F3112.WLMACA AS RUN_MACHINE_ACT,
       F0911.GLSBL AS OBJ_ACCT,
       F0911.GLAA   AS GL_AMOUNT,
       F0911.GLU    AS GL_UNITS
FROM   PROD2DTA.F3112 F3112
       INNER JOIN PROD2DTA.F0911 F0911
         ON F3112.WLDOCO = CAST(F0911.GLSBL AS INTEGER)
WHERE  F0911.GLOBJ = '6855')a
inner join 

(SELECT F3112.WLDOCO AS DOC_NO,
       F3112.WLDCTO AS DOC_TYPE,
       F3112.WLCTS4 AS UDL_AMOUNT,
       F3112.WLCTS9 AS UDL_HOURS,
       F3112.WLLABA AS RUN_LABOR_ACT,
       F3112.WLMACA AS RUN_MACHINE_ACT,
       F4801.WASRST AS WO_STATUS
FROM   PROD2DTA.F3112 F3112
       INNER JOIN PROD2DTA.F4801 F4801
         ON F3112.WLDOCO = F4801.WADOCO
WHERE  F4801.WASRST BETWEEN '30' AND '95')b
on a.WLDOCO=b.WLDOCO

score 0 · Answer 2 · answered Mar 19 '21 at 04:00

Try it like this:

SELECT F3112.WLDOCO AS DOC_NO,
       F3112.WLDCTO AS DOC_TYPE,
       F3112.WLCTS4 AS UDL_AMOUNT,
       F3112.WLCTS9 AS UDL_HOURS,
       F3112.WLLABA AS RUN_LABOR_ACT,
       F3112.WLMACA AS RUN_MACHINE_ACT,
       F0911.GLSBL  AS OBJ_ACCT,
       F0911.GLAA   AS GL_AMOUNT,
       F0911.GLU    AS GL_UNITS,
       F4801.WASRST AS WO_STATUS
FROM   PROD2DTA.F3112 F3112
       INNER JOIN PROD2DTA.F0911 F0911
         ON F3112.WLDOCO = CAST(F0911.GLSBL AS INTEGER)
         AND F0911.GLOBJ = '6855'
       INNER JOIN PROD2DTA.F4801 F4801
         ON F3112.WLDOCO = F4801.WADOCO
       AND F4801.WASRST BETWEEN '30' AND '95'

This worked once I switched from using CAST to DIGITS (see my answer). — Joseph Gagnon, Mar 19 '21 at 15:46

Joseph Gagnon · Accepted Answer · 2021-03-19T15:50:18.900

Thank you to @nfgl. The use of DIGITS was what fixed the "data conversion" issue.

I tried both of the answer suggestions above (they worked once I used DIGITS instead of CAST) and with a little experimentation and tweaking, I basically get the same results in all cases.

My SQL now looks like this:

SELECT F3112.WLDOCO AS DOC_NO,
       F3112.WLDCTO AS DOC_TYPE,
       F3112.WLCTS4 AS UDL_AMOUNT,
       F3112.WLCTS9 AS UDL_HOURS,
       F3112.WLLABA AS RUN_LABOR_ACT,
       F3112.WLMACA AS RUN_MACHINE_ACT,
       F0911.GLSBL  AS OBJ_ACCT,
       F0911.GLAA   AS GL_AMOUNT,
       F0911.GLU    AS GL_UNITS,
       F4801.WASRST AS WO_STATUS
FROM   PROD2DTA.F3112 F3112
       INNER JOIN PROD2DTA.F0911 F0911
         ON DIGITS(F3112.WLDOCO) = F0911.GLSBL
       INNER JOIN PROD2DTA.F4801 F4801
         ON F3112.WLDOCO = F4801.WADOCO
WHERE  F0911.GLOBJ = '6855'
       AND F4801.WASRST BETWEEN '30' AND '95'

The only difference from my original is the use of DIGITS in the ON condition of the first inner join.

I think this gets me what I need. If anyone sees a problem, please let me know.

Why doesn't this SQL work? [JD Edwards DB2]

3 Answers3