Database Pivot only works with small tables, otherwise fails

[despina_l]

Hi all,

I’m relatively new to Knime community, I will appreciate your help on the following topic:

I need to pivot data in a table and switch from rows to columns a few of the rows available. The database pivot node works fine with a small table, however when testing with big tables (>1mil rows), the node execution fails with error: ORA-01861: literal does not match format string. Mention that I used a ‘number’ field on ‘Manual Aggregation’ to avoid a datatype issue.

Has anyone solved this or met this error to point me to the right direction?

Thank you in avance. L

[ScottF]

Hi @despina_l -

I’m not an Oracle expert, but I don’t think the problem has to do with the size of your dataset per se - rather, you may have some rogue values in your data that have slightly strange formats. Often times this particular error is associated with casting of DateTime values.

Is it possible that there is a DateTime field in your database that has inconsistently formatted data?

[despina_l]

Hi ScottF, thank you for answering. I was just able to solve the issue and indeed - the size of the dataset/tbl was not the problem,but the correct setup of the groups/pivot/manual aggregation.
I removed from my selection any of the DateTime fields and used as Manual Aggregation a text/VARCHAR2(150byte) field instead (Max(ANSWER)), as I should have originally:

This worked fine without error and pivoting was done correctly

Thank you once again!