Anthony
asked on
DB2 SQL Issue When Using Combined Columns In A Join
Hi All,
I have the below query, where I'm trying to use a field I created in a table, which is a combination of values from 3 other columns. I tested the logic of the combination of values and that works fine. When I go to use that new field in a join to another like field in another table, I get an SQL0802- Data conversion or data mapping error...
Any help on getting this corrected, will be greatly appreciated.
I have the below query, where I'm trying to use a field I created in a table, which is a combination of values from 3 other columns. I tested the logic of the combination of values and that works fine. When I go to use that new field in a join to another like field in another table, I get an SQL0802- Data conversion or data mapping error...
Any help on getting this corrected, will be greatly appreciated.
SELECT INPOL100.CMPNO AS COMPANY
, INPOL100.PSTAT AS STATUS
, INPOL100.PURCH AS "PO NUM"
, INPOL300."LINE#" AS "PO LINE NUM"
, INPOL100.PODDT AS "PO DATE"
, INPOL300.PORDT AS "LAST RECEIPT DATE"
, INPOL300.SDESC AS "PO DESCRIPTION"
, INPOL300.PRDNO AS "PRODUCT NUMBER"
, INPOL300.P3OR1 AS "ORG CODE 1"
, INPOL300.P3OR2 AS "ORG CODE 2"
, INPOL300.ECOST AS "$ / UNIT"
, INPOL300.P3QOR AS "QUANTITY ORDERED"
, INPOL300.P3QAC + INPOL300.P3QOR AS QUANTITY_RECEIVED
, INPOL300.P3QRJ AS "QUANTITY RETURNED"
, INPOL300.P3QOR - (INPOL300.P3QAC + INPOL300.P3QOR) + INPOL300.P3QRJ AS OPEN_QUANITY
, APACP10X.ACGLA AS "GL ACCOUNT"
FROM
((LOGICLIB.INPOL100 INPOL100
INNER JOIN
LOGICLIB.INPOL300 INPOL300
ON (INPOL100.PURCH = INPOL300.PURCH))
INNER JOIN
HERBERT.APACP10X APACP10X
ON (INPOL100.PURCH = APACP10X.ACPUR))
INNER JOIN
RMSGL800.VALMAST VALMAST
ON (APACP10X.ACGLA = trim(CAST(TRIM(VARCHAR_FORMAT(VALMAST.GLAC2,'000'))||TRIM(VARCHAR_FORMAT(VALMAST.GLAC3,'000'))||TRIM(VARCHAR_FORMAT(VALMAST.GLAC4,'000')) AS decimal (15,0))))
WHERE (INPOL100.PSTAT = 'N')
I'm not sure why you'd want to do a TRIM() on the value after you've cast is as a decimal: that seems very odd.
ASKER
Yeah I noticed that so I removed that part and it still doesn't work. I think it needs to be a temp table first with the values already combined and then I use that for my join.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks Kent. Appreciate your help.