2620: The format or data contains a bad character

Database

2620: The format or data contains a bad character

Hello

I keep getting the above error message after the query below has been running for a minute or so.

I looked at this part...

left join (select CCT_External_ID, CCT_Agent_ID from GDW_VIEWS. CCT_AGENT where CCT_External_ID between '0' and '999999') Rep_Trans_IDs on Rep_Emp_IDs.emp_id=Rep_Trans_IDs.CCT_External_ID


because I have a filter on emp_id > 0 (numeric)

and  filter CCT_External_ID between '0' and '999999' ('text')

and then I am joining on the two; numeric to text

I tried changing the filter to  emp_id > '0' (make it a text)

but that didnt help and it still keeps throwing me an error.

Can someone please take look at the query below and suggest what I need to do the get this running again?

SELECT
Calendar .*
, Sales_Hierarchy .*
, Sales_2012 .*
, Agent_Activity .*
, Agent_Calls .CCT_SOURCE_DATE
, Agent_Calls .CCT_AGENT_ID
, Agent_Calls .total_call_count
, Agent_Calls .outbound_call_count
, Agent_Calls .inbound_call_count
, CASE
WHEN Agent_Calls.total_talk_time is not null
THEN Agent_Calls.total_talk_time
ELSE '0:00'
END total_talk_time
, CASE
WHEN Agent_Calls.outbound_talk_time is not null
THEN Agent_Calls.outbound_talk_time
ELSE '0:00'
END outbound_talk_time
, CASE
WHEN Agent_Calls.inbound_talk_time is not null
THEN Agent_Calls.inbound_talk_time
ELSE '0:00'
END inbound_talk_time
, Sales_2011 .*
FROM
(
SELECT
Cal.*
FROM
Reporting_v.OD_CALENDAR Cal
INNER JOIN
Reporting_v.DYNAMIC_DATES DD
ON
DD.BUSINESS_DAY_DT = Cal.FISCAL_DATE_ID
WHERE
DD.DYNAMIC_DATE_TYPE_ID = 4
)
Calendar
CROSS JOIN
(
SELECT
RSD .SALES_REGION_ID AS RSD_ID
, RSD .SALES_REGION_DESC AS RSD_Name
, DSM .SALES_DISTRICT_DESC AS DSM_Name
, DSM .SALES_DISTRICT_ID AS DSM_ID
, Rep .SALES_TERRITORY_ID AS Rep_ID
, Rep .SALES_TERRITORY_DESC AS Rep_Name
, Rep_Emp_IDs .emp_id AS Emp_ID
, Rep_Trans_IDs . CCT_Agent_ID AS Transera_ID
FROM
Reporting_v.SALES_REGION RSD
INNER JOIN
Reporting_v.SALES_DISTRICT DSM
ON
RSD.SALES_REGION_ID = DSM.SALES_REGION_ID
INNER JOIN
Reporting_v.SALES_TERRITORY_CONTACT Rep
ON
DSM.SALES_DISTRICT_ID = Rep.SALES_DISTRICT_ID
LEFT JOIN
(
SELECT DISTINCT
sp_id
, emp_id
FROM
marketbasket.smgr
WHERE
sp_id LIKE 'IS%'
AND emp_id > 0
)
Rep_Emp_IDs
ON
Rep.SALES_TERRITORY_ID = Rep_Emp_IDs.sp_id
LEFT JOIN
(
SELECT
CCT_External_ID
, CCT_Agent_ID
FROM
GDW_VIEWS. CCT_AGENT
WHERE
CCT_External_ID BETWEEN '0' AND '999999'
)
Rep_Trans_IDs
ON
Rep_Emp_IDs.emp_id = Rep_Trans_IDs.CCT_External_ID
WHERE
RSD.SALES_REGION_ID IN ('IS10000', 'IS20000', 'IS30000')
)
Sales_Hierarchy
LEFT JOIN
(
SELECT
Cal .FISCAL_DATE_ID
, Rep .SALES_TERRITORY_ID AS Rep_ID
, SUM (CASE
WHEN Stac.SALES_TERRITORY_TYPE_CD = 'BSD'
AND Rep.SALES_TERRITORY_TITLE <> 'BDR'
THEN SD.EXT_SELLING_PRICE_USD_AMT
ELSE 0
END) AS Rep_Sales
, SUM (CASE
WHEN Stac.SALES_TERRITORY_TYPE_CD = 'BSD'
AND Rep.SALES_TERRITORY_TITLE <> 'BDR'
THEN SD.EXT_SELLING_PRICE_USD_AMT - SD.EXT_ITEM_PO_COST_USD_AMT
ELSE 0
END) AS Rep_Margin
, SUM (CASE
WHEN Stac.SALES_TERRITORY_TYPE_CD = 'AM'
AND Rep.SALES_TERRITORY_TITLE = 'BDR'
THEN SD.EXT_SELLING_PRICE_USD_AMT
ELSE 0
END) AS BDR_Sales
, SUM (CASE
WHEN Stac.SALES_TERRITORY_TYPE_CD = 'AM'
AND Rep.SALES_TERRITORY_TITLE = 'BDR'
THEN SD.EXT_SELLING_PRICE_USD_AMT - SD.EXT_ITEM_PO_COST_USD_AMT
ELSE 0
END) AS BDR_Margin
FROM
Reporting_v.SALES_REGION RSD
INNER JOIN
Reporting_v.SALES_DISTRICT DSM
ON
RSD.SALES_REGION_ID = DSM.SALES_REGION_ID
INNER JOIN
Reporting_v.SALES_TERRITORY_CONTACT Rep
ON
DSM.SALES_DISTRICT_ID = Rep.SALES_DISTRICT_ID
INNER JOIN
Reporting_v.SALES_TERRITORY_ACCOUNT_CURR Stac
ON
Rep.SALES_TERRITORY_ID = Stac.SALES_TERRITORY_ID
INNER JOIN
Reporting_v.BSD_SALES_DETAIL SD
ON
Stac.ACCOUNT_ID = SD.CUSTOMER_ACCOUNT_ID
AND Stac.AOPS_SEQ_NBR = SD.ADDRESS_ID
INNER JOIN
Reporting_v.SALES_TYPE St
ON
St.SALES_TYPE_CD = SD.SALES_TYPE_CD
INNER JOIN
Reporting_v.OD_CALENDAR Cal
ON
Cal.FISCAL_DATE_ID = SD.SALES_CREDIT_FISCAL_DATE_ID
INNER JOIN
Reporting_v.DYNAMIC_DATES DD
ON
DD.BUSINESS_DAY_DT = Cal.FISCAL_DATE_ID
WHERE
DD.DYNAMIC_DATE_TYPE_ID = 4
AND RSD.SALES_REGION_ID IN ('IS10000', 'IS20000', 'IS30000') --and
-- Stac.SALES_TERRITORY_TYPE_CD = 'BSD' and Rep.SALES_TERRITORY_TITLE<>'BDR'
GROUP BY
1
, 2  
)
Sales_2012
ON
Sales_Hierarchy.Rep_ID = Sales_2012.Rep_ID
AND Calendar.FISCAL_DATE_ID = Sales_2012.FISCAL_DATE_ID
LEFT JOIN
(
SELECT
cct_source_date
, cct_agent_id
, SUM (time_spent_actvty) time_spent_actvty
FROM
GDW_VIEWS.CCT_AGENT_ACTIVITY
WHERE
cct_site_id = 304
AND agent_actvty_nm = 'Connected'
GROUP BY
cct_source_date
, cct_agent_id
, time_spent_actvty
)
Agent_Activity
ON
Calendar.dt = Agent_Activity.cct_source_date
AND Sales_Hierarchy.transera_id = Agent_Activity.cct_agent_id
LEFT JOIN
(
SELECT
cct_source_date
, cct_agent_id
, COUNT (session_id) total_call_count
, COUNT (CASE
WHEN entry_point_call_id = 6651
THEN session_id
ELSE 0
END) AS outbound_call_count
, COUNT (CASE
WHEN entry_point_call_id = 6616
THEN session_id
ELSE 0
END) AS inbound_call_count
, TRIM (CAST(SUM(cct_agent_tm) * INTERVAL '0000:00:01' HOUR TO SECOND AS VARCHAR(20)))
total_talk_time
, TRIM (CAST(SUM(CASE
WHEN entry_point_call_id = 6651
THEN cct_agent_tm
ELSE 0
END) * INTERVAL '0000:00:01' HOUR TO SECOND AS VARCHAR(20))) AS outbound_talk_time
, TRIM (CAST(SUM(CASE
WHEN entry_point_call_id = 6616
THEN cct_agent_tm
ELSE 0
END) * INTERVAL '0000:00:01' HOUR TO SECOND AS VARCHAR(20))) AS inbound_talk_time
FROM
GDW_VIEWS.CCT_CENTER_INFO
WHERE
cct_site_id = 304
AND cct_program_id = 53
GROUP BY
cct_source_date
, cct_agent_id
)
Agent_Calls
ON
Calendar.dt = Agent_Calls.cct_source_date
AND Sales_Hierarchy.transera_id = Agent_Calls.cct_agent_id
LEFT JOIN
(
SELECT
Cal .FISCAL_DATE_ID
, Rep .SALES_TERRITORY_ID AS Rep_ID
, SUM (CASE
WHEN Stac.SALES_TERRITORY_TYPE_CD = 'BSD'
AND Rep.SALES_TERRITORY_TITLE <> 'BDR'
THEN SD.EXT_SELLING_PRICE_USD_AMT
ELSE 0
END) AS Rep_Sales
, SUM (CASE
WHEN Stac.SALES_TERRITORY_TYPE_CD = 'BSD'
AND Rep.SALES_TERRITORY_TITLE <> 'BDR'
THEN SD.EXT_SELLING_PRICE_USD_AMT - SD.EXT_ITEM_PO_COST_USD_AMT
ELSE 0
END) AS Rep_Margin
, SUM (CASE
WHEN Stac.SALES_TERRITORY_TYPE_CD = 'AM'
AND Rep.SALES_TERRITORY_TITLE = 'BDR'
THEN SD.EXT_SELLING_PRICE_USD_AMT
ELSE 0
END) AS BDR_Sales
, SUM (CASE
WHEN Stac.SALES_TERRITORY_TYPE_CD = 'AM'
AND Rep.SALES_TERRITORY_TITLE = 'BDR'
THEN SD.EXT_SELLING_PRICE_USD_AMT - SD.EXT_ITEM_PO_COST_USD_AMT
ELSE 0
END) AS BDR_Margin
FROM
Reporting_v.SALES_REGION RSD
INNER JOIN
Reporting_v.SALES_DISTRICT DSM
ON
RSD.SALES_REGION_ID = DSM.SALES_REGION_ID
INNER JOIN
Reporting_v.SALES_TERRITORY_CONTACT Rep
ON
DSM.SALES_DISTRICT_ID = Rep.SALES_DISTRICT_ID
INNER JOIN
Reporting_v.SALES_TERRITORY_ACCOUNT_CURR Stac
ON
Rep.SALES_TERRITORY_ID = Stac.SALES_TERRITORY_ID
INNER JOIN
Reporting_v.BSD_SALES_DETAIL SD
ON
Stac.ACCOUNT_ID = SD.CUSTOMER_ACCOUNT_ID
AND Stac.AOPS_SEQ_NBR = SD.ADDRESS_ID
INNER JOIN
Reporting_v.SALES_TYPE St
ON
St.SALES_TYPE_CD = SD.SALES_TYPE_CD
INNER JOIN
Reporting_v.OD_CALENDAR Cal
ON
Cal.FISCAL_DATE_ID = SD.SALES_CREDIT_FISCAL_DATE_ID
INNER JOIN
Reporting_v.DYNAMIC_DATES DD
ON
DD.BUSINESS_DAY_DT = Cal.FISCAL_DATE_ID
WHERE
DD.DYNAMIC_DATE_TYPE_ID = 5
AND RSD.SALES_REGION_ID IN ('IS10000', 'IS20000', 'IS30000') --and
-- Stac.SALES_TERRITORY_TYPE_CD = 'BSD' and Rep.SALES_TERRITORY_TITLE<>'BDR'
GROUP BY
1
, 2
)
Sales_2011
ON
Sales_Hierarchy.Rep_ID = Sales_2011.Rep_ID
AND Calendar.fiscal_date_last_yr_id_shifted = Sales_2011.FISCAL_DATE_ID
ORDER BY
1 DESC , 2;
4 REPLIES
Junior Contributor

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

CCT_External_ID between '0' and '999999' might still return non-numeric data which fails to convert to an int.

emp_id > '0' in a WHERE will not change the datatype, you must cast emp_id to a string:

            SELECT DISTINCT
sp_id
, trim(emp_id) as emp_id
FROM
marketbasket.smgr
WHERE
sp_id LIKE 'IS%'
AND emp_id > 0

But you better clean your data during load or even better: fix your datamodel.

N/A

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

Hi, I am getting the above error when trying to run the query below, could you please assist me if possible?

SELECT

Date_memo_added,

rep_id_record_created,

description_memo,

CAST(REGEXP_SUBSTR(description_memo, '\$[0-9]*.[0-9]+') AS DECIMAL(10,2) FORMAT '$ZZZZ,ZZ') AS Paid_Value

FROM

Table_1

WHERE Code_memo_type ='DRT'

AND (Description_memo LIKE '%LIABLE%' OR Description_memo LIKE '%REFUNDED%' OR Description_memo LIKE '%REMOVED%')

AND Description_memo NOT LIKE '%CHARGEBACK%'

AND Date_memo_added >= ADD_MONTHS(DATE - (EXTRACT(DAY FROM DATE)-1),-6)

Junior Contributor

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

Remove the CAST and check the data returned by the regex.

Your data type allows xxxxxxxx.xx, but your FORMAT only xxxx.xx

Teradata Employee

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

What is the system locale for the format string '$ZZZZ,ZZ'? If it is en-US, then ',' (comma) is not the typical radix separator and '.' (dot) should be used. Check the SDF specification and the tdlocaledef utility for the setup with other locales.

-Dave