2620: The format or data contains a bad character

Database

2620: The format or data contains a bad character

Hello

I'm trying to run the following query and keep getting the above error message after the query has been running for a minute or so.

I've tried running it one where clause at a time, have removed the case when and fiddled around with it to try and identify where it is going wrong.

If anyone could help me I'd really appreciate it.

SELECT
T1.Call_Date
,(t1.Trading_Code||((t1.Account_Number/10)(FORMAT '9999999')))
,T1.Call_Type
,T1.BU_Desc
,SUM(CASE WHEN T4.Department_Group_Code IN ('M','W') THEN 'Fashion'
WHEN T4.Department_Group_Code IN ('F') THEN 'Footwear'
WHEN T4.Department_Group_Code IN ('C') THEN 'Lingerie'
ELSE 'Other' END) AS Call_Type

FROM custadmindb.M72_DMCs T1

LEFT JOIN prodvm.orderline_current T3 ON ((t1.Trading_code = T3.Trading_Code) AND (T1.account_number = t3.Account_Number) AND (t1.call_date = t3.date_of_original_order))

LEFT JOIN prodvm.department_code T4 ON (T3.department_code = T4.department_code)

WHERE
T3.Orderline_Status_Code NOT IN ('1', '3', '8', '9')
AND (t3.offer_number) NOT IN
(SEL offer_number
FROM prodvm.offer_new
WHERE offer_type_desc = 'order building only')

GROUP BY 1,2,3,4
ORDER BY 1,2;

Thanks.

1 REPLY
Senior Apprentice

Re: 2620: The format or data contains a bad character

You're SUMming the result of the CASE statement which is a string.
SUM needs a numeric value so it's trying to cast 'Fashion' etc. into a FLOAT.

What are you trying to achieve?

Number of rows per CallType?
,CASE WHEN T4.Department_Group_Code IN ('M','W') THEN 'Fashion'
WHEN T4.Department_Group_Code IN ('F') THEN 'Footwear'
WHEN T4.Department_Group_Code IN ('C') THEN 'Lingerie'
ELSE 'Other' END AS Call_Type
, COUNT(*)
...
GROUP BY 1,2,3,4,5

Dieter