String replace function on MsAccess showing error in pentaho

[balsu333]

Hi,
I was trying to use replace function in access and it is successful. But when using the same syntax in pentaho component it is showing error about replace function. why???????? How to rectify this?

[Mick_data]

Which step do you use?
What are you replacing?

Mick

[balsu333]

Hi Mick,
Iam having some tables in an access database file..And i executed a query and it is succeessful.
Ex: select replace(first_name,"ss","d") from table1;
Here first_name is a col in table1 which contains some data. I just want to replace 'ss' with 'd' in the data for every row.
In access it is successful. but when iam using the same query, to output data to afile it is showing error invalid function 'replace' in query.

Please help me in resolving this.....

Which step do you use?
What are you replacing?

Mick

[Mick_data]

That's because you should use the Table Input step to retrieve data, not to change data.
I would create a transformation with:
Table Input (read data)
Replace in strin (modify data)
Table Output (write data back to DB)

Mick

[balsu333]

Thanks Mick,
I was using Table input step only... But can we define replace directly in the input component query instead of placing another "replace in strin" component

That's because you should use the Table Input step to retrieve data, not to change data.
I would create a transformation with:
Table Input (read data)
Replace in strin (modify data)
Table Output (write data back to DB)

Mick

[marabu]

The MS Access ODBC driver supports a dialect called JetSQL.
MS Access functions are generally not supported.

[Mick_data]

I think that you are using the wrong tool if all you need is to do a serach and replace in MS Access.

Mick

[marabu]

Mick, all he wants is a computed field, I think...

[balsu333]

Thanks marabu...
You have given me the answer which i required..

The MS Access ODBC driver supports a dialect called JetSQL.
MS Access functions are generally not supported.

[balsu333]

Thanks Mick...
I just decribed my thread in a general way..Any way thanks for ur help at #4..

I think that you are using the wrong tool if all you need is to do a serach and replace in MS Access.

Mick