How to modify LKM File to Oracle in ODI to use CASE WHEN statement in sql

user12251389

Dear All,

I am using ODI 12.2 version. I am trying to load text file into oracle database table. Currently there is no error during loading from odi but its loading only few rows and for few rows the bad file is getting generated with error :

ODI-40469: Value was incorrect for column 40: String N.A. is not a valid representation for a Number

ODI-40469: Value was incorrect for column 37: String N.A. is not a valid representation for a Number

I understood the issue where its trying to insert the value as N.A. in the fields which is Number datatype. But i don't want to change the datatype of those Numeric fields to string in data store as well as in oracle database table because it will change logic for precision and scale used for Numeric datatype.

I want to change LKM File to Oracle such that for all the fields with values N.A. it should set the values to null. I haven't modify LKM or IKM before as its written in groovy so dont know how to do it. I belive we need to change the Select query in LKM in source code and use something like CASE WHEN statement which can be used for all the fields.

Below is some part of Select query in Source Command in LKM File to Oracle

SELECT 

{# IF $[QUERY.isDistinct()] #}{#NL#}

 DISTINCT 

{# ENDIF #}

{# IF ($[QUERY.getAliasList()] != 'null') #}{#NL#}

{# LIST #} $[QUERY.getSelectList().foreach(getText())] $[QUERY.getColumnAliasSeparator()] $[QUERY.getAliasList()] {# SEP #},{#NL#}{# ENDLIST #} 

{# ELSE #}{#NL#}

{# LIST #} $[QUERY.getSelectList().foreach(getText())] {# SEP #},{#NL#}{# ENDLIST #}

{# ENDIF #} 

{#NL#}

{# IF $[QUERY.isConstantQuery()] #} 

{#NL#}

$[QUERY.getConstantFromClauseText()] 

{# ELSE #}FROM {#NL#}

{# LIST #} $[QUERY.getFromList().foreach(getText())]{# SEP #} ,{#NL#} {# ENDLIST #} 

{# ENDIF #}