CODEWITHSUNDEEP
oracle-databaseinformatica-powercenter
Debraj Das
Debraj Das

Reputation: 99

ora-01858 a non-numeric character was found where a numeric was expected informatica

I've created a very simple mapping in informatica 10.2.0. Whenever I'm trying to execute the workflow, the session task is failing.

The error is as below,

Severity    Timestamp   Node    Thread  Message Code    Message
ERROR   16-12-2019 05:24:50 PM  node01_prdeim   READER_1_2_1    RR_4035 SQL Error [
ORA-01858: a non-numeric character was found where a numeric was expected

Database driver error...
Function Name : Execute
SQL Stmt : select MARKET_ID,MARKET_NAME,MONTH_YY,'Y' ACTUAL_FLAG  from TABLEAU_NEW_MARKET_COVERAGE
where 
TO_DATE(MONTH_YY)=TO_CHAR(TRUNC(ADD_MONTHS(SYSDATE,-1), 'MON'),'DD-MON-YYYY')
and OFMCG_SALES>667
Database driver error...
Function Name : Execute
SQL Stmt : select MARKET_ID,MARKET_NAME,MONTH_YY,'Y' ACTUAL_FLAG  from TABLEAU_NEW_MARKET_COVERAGE
where 
TO_DATE(MONTH_YY)=TO_CHAR(TRUNC(ADD_MONTHS(SYSDATE,-1), 'MON'),'DD-MON-YYYY')
and OFMCG_SALES>667].

Source Table structure,

Name                   Null? Type          
---------------------- ----- ------------- 
BRANCH_CODE                  VARCHAR2(10)  
MARKET_NAME                  VARCHAR2(255) 
MARKET_ID                    NUMBER(12)    
MARKET_POP_GROUP             VARCHAR2(50)  
MONTH_YY                     DATE          
OFMCG_SALES                  NUMBER        
TYPE                         VARCHAR2(255) 
SERVICE_MODE                 VARCHAR2(255) 
SERVICE_FLAG                 VARCHAR2(255) 
QUALITY_MKT_TARGET           NUMBER        
QUALITY_MKT_UOB              NUMBER        
QUALITY_MARKET               VARCHAR2(26)  
CONCERN_MARKET               VARCHAR2(26)  
QUALITY_MKT_ACTUAL_UOB       NUMBER        
PLAN_FLG                     VARCHAR2(500)

Target Table Structure,

Name         Null?    Type          
------------ -------- ------------- 
MARKET_ID    NOT NULL VARCHAR2(20)  
MARKET_NAME  NOT NULL VARCHAR2(200) 
WDCODE       NOT NULL VARCHAR2(20)  
POP_GROUP             VARCHAR2(50)  
DISTRICT              VARCHAR2(100) 
SUB_DISTRICT          VARCHAR2(100) 
PLAN_FLAG             VARCHAR2(20)  
ACTUAL_FLAG           VARCHAR2(20)  
TRANSDATE    NOT NULL DATE          
LASTUPDATE   NOT NULL NUMBER

In my source qualifier, the sql override is,

select MARKET_ID,MARKET_NAME,MONTH_YY,'Y' ACTUAL_FLAG  from TABLEAU_NEW_MARKET_COVERAGE
where 
MONTH_YY=TO_CHAR(TRUNC(ADD_MONTHS(SYSDATE,-1), 'MON'),'DD-MON-YYYY')
and OFMCG_SALES>667

It's a very simple mapping,

SRC-EXP-JNR-UPD-TGT

Tried to find the error details. Data types are fine in informatica as well as in oracle.

Could someone please guide me what's causing the issue?

Upvotes: 0

Views: 1310

Answers (1)

Popeye
Popeye

Reputation: 35900

Your MONTH_YY is the date column and still, you are trying to convert it to date.

use the following query:

SELECT
    MARKET_ID,
    MARKET_NAME,
    MONTH_YY,
    'Y' ACTUAL_FLAG
FROM
    TABLEAU_NEW_MARKET_COVERAGE
WHERE
    TRUNC(MONTH_YY) = TRUNC(ADD_MONTHS(SYSDATE, - 1), 'MON') 
    -- used trunc for MONTH_YY
    -- removed to_char from right side expression
    AND OFMCG_SALES > 667

Cheers!!

Upvotes: 1

Related Questions