Hi. I'm a newbie with Pentaho but an experienced ETL/BI professional. I'm attempting to do a simple validation.
My transformations are:
  1. A simple two steps transformation in order to load de CSV input file into a MySQL -all varchars- RAW table (it works)
  2. Then a second transformation to validate input Data Type. In this case I'm casting the input. It looks like this:


Name:  trans2.jpg
Views: 93
Size:  11.9 KB

The input is casted from the RAW (all varchars) table into the corresponding data types, like this:

SELECT ID_RAW, CAST( CONCAT( '20', SUBSTRING( trim( FECHAVTA ) , 7, 2 ) , '-',
SUBSTRING( trim( FECHAVTA ) , 4, 2 ) , '-',
SUBSTRING( trim( FECHAVTA ) , 1, 2 ) ) AS DATE ) AS FECHAVTA,
cast(trim( ID_CLIENTE ) as signed) AS ID_CLIENTE,
cast(trim( ID_PAISVTA ) as signed) AS ID_PAISVTA,
cast(trim( NROSUCVTA ) as signed) AS NROSUCVTA,
cast(trim( ID_ARTICULO) as signed) AS ID_ARTICULO,
cast(trim( CANTIDAD ) as decimal(5)) AS CANTIDAD,
cast(trim( PRECIOESPECIAL ) as decimal(7,2)) AS PRECIOESPECIAL
FROM rawprueba1


Okay then, the validation step doesn´t work well because I checked the box "Validate Data Type" for every validation of every field. Using a correct file it takes the whole input file as in error inspite of I casted the input for all of them. At the same time if I uncheck all input is accepted disregarding that I entered some numeric data as alpha and the result set of input contains null values for those fields (however I marked he "Only numeric data is expected"

In short. what am I doing wrong? Kettel seems to be intuitive but in the end is full of tricks.

Can anybody help me please?

Thanks in advance,

Marcelo