0

I receive the following error in my query:

Invalid character found in a character string argument of the function "DECFLOAT".

However I have made no changes to any of the fields I am selecting:

select
RQH.COMPANY, 
RQH.LAST_APRV_DT, 
RQH.CREATION_DATE, 
RQH.RELEASED_DATE, 
RQH.REL_OPER_ID, 
RQH.REQ_NUMBER, 
RQH.REQUESTER, 

RLN.COMPANY, 
RLN.BUYER,
RLN.DESCRIPTION, 
RLN.TRAN_UNIT_COST, 
RLN.QUANTITY, 
RLN.ITEM, 
RLN.VEN_ITEM, 
RLN.LINE_NBR, 
RLN.MANUF_CODE, 
RLN.MANUF_NBR, 
RLN.VENDOR, 
RLN.REQ_NUMBER, 
RLN.ENTERED_UOM, 


MMD.LINE_NBR, 
MMD.DOC_NBR_NUM, 

VEN.VENDOR_VNAME, 
VEN.VENDOR_GROUP, 
VEN.VENDOR

from mmd 

join rln on MMD.COMPANY = rln.COMPANY and MMD.DOC_NBR_NUM = rln.REQ_NUMBER 
    and MMD.LINE_NBR = rln.LINE_NBR

join cpy on rln.company = cpy.company

join ven on VEN.VENDOR_GROUP = cpy.VENDOR_GROUP and VEN.VENDOR = rln.VENDOR

join rqh on RLN.COMPANY = RQH.COMPANY and RLN.REQ_NUMBER = RQH.REQ_NUMBER

where ven.vendor = 20200
Cameron Cox
  • 71
  • 2
  • 8
  • I don't see a DECFLOAT function being called here. Smells like a trigger issue. Then again, you are only doing a select... which makes me think it's a data type issue with an implicit conversion. Not super familiar with DB2 so take that with a grain of salt. – Jacob H Dec 15 '17 at 21:13
  • Are these views? – Jacob H Dec 15 '17 at 21:19
  • @JacobH No, I am pulling directly from the tables; I was triaging it more and it looks like the issue is related to my where statement but I don't know why, I've run that exact condition on that same table in another similar query with no issue – Cameron Cox Dec 15 '17 at 21:26
  • Try `ven.vendor = '20200'` – Jacob H Dec 15 '17 at 21:28
  • yea, I tried that but it brings back no results, I did verify that the vendor field is a character string. However on a different query I created that runs correctly (where I make vendor = number field) when I make vendor a text field nothing comes up but as a number field values do come up – Cameron Cox Dec 15 '17 at 21:33
  • Since you are joining `ven.vendor = rln.vendor` maybe the issue is with the data type of rln.vendor instead? What if you query just ven alone for vendor 20200? – Jacob H Dec 15 '17 at 21:37
  • I checked ven and rln both have vendor = character(9) – Cameron Cox Dec 15 '17 at 21:47
  • One of your comparisons has a numeric data type on one side and a character data type on the other. – mustaccio Dec 15 '17 at 21:50

2 Answers2

0

One of your comparisons has a numeric data type on one side and a character data type on the other. One of the values in the character column in that pair cannot be converted to a number. Db2 data type conversion rules state that if one operand is numeric and the other one is of a character type, the character value will be implicitly coerced into a number. This will obviously fail for values that cannot be coerced. If such is a valid value, you'll need to explicitly cast the numeric column to a character data type (padding it with spaces as necessary).

mustaccio
  • 18,234
  • 16
  • 48
  • 57
  • I checked and all the pairs of comparisons have the same data type – Cameron Cox Dec 15 '17 at 22:03
  • You just said that `ven.vendor` is `character(9)`, and you are comparing it with an integer value `20200` – mustaccio Dec 15 '17 at 22:06
  • Sorry let me clarify, in the above query if I use "ven.vendor = 20200" I get the error from the above, if I use "ven.vendor = '20200' " I get no data returned. On a different query that also uses the 'ven' table if I use "ven.vendor = 20200" then data populates. Both queries have data that should populate (this isn't an inner join issue). – Cameron Cox Dec 15 '17 at 22:28
-1

If you remove the "WHERE" clause altogether, do you still get the error or does it go away?

John Hennesy
  • 9
  • 4
  • I can't say for sure because it is so much data it would likely timeout without the where clause before I could know for sure if the decfloat issue comes up, I did try using a date field instead and it ran without the issue – Cameron Cox Dec 27 '17 at 23:37