Link to home
Start Free TrialLog in
Avatar of Anthony
AnthonyFlag for United States of America

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.  

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')

Open in new window

Avatar of Scott Pletcher
Scott Pletcher
Flag of United States of America image

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.
Avatar of Anthony

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
Avatar of Kent Olsen
Kent Olsen
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Anthony

ASKER

Thanks Kent. Appreciate your help.