All Forums Database
terauser1981 1 post Joined 04/14
22 Apr 2014
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;

 

 

dnoeth 4628 posts Joined 11/04
22 Apr 2014

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.

Dieter

NZKay 1 post Joined 07/15
24 Jul 2015

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)
 

dnoeth 4628 posts Joined 11/04
24 Jul 2015

Remove the CAST and check the data returned by the regex.
Your data type allows xxxxxxxx.xx, but your FORMAT only xxxx.xx

Dieter

david.craig 73 posts Joined 05/13
24 Jul 2015

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

You must sign in to leave a comment.