Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have a table with more than 500 columns. Since Qlik is limited to only 500 columns per table, is it possible to not load and replicate other columns in Qlik replicate? I am using Oracle as source and target.
Hello @rommel_oli ,
In table settings , you can remove some columns from the replication. see transformations for a single table .
A sample: select the column and press button "<" to remove the column:
Hope this helps.
BTW, any detailed info about the max columns is 500? And what's the Replicate version.
Regards,
John.
Hello @rommel_oli ,
I have a table with more than 500 columns. Since Qlik is limited to only 500 columns per table...
There is not such limitation in Replicate. Oracle Maximum Columns per table is 1000. SQL Server Maximum Columns per table is 1024. In SQLite (it's Replicate Repository DB), the maximum columns number per table is 2000.
The 1000 columns tables are replicating successfully in Replicate, include replication from Oracle to Oracle, and from Oracle to SQL Server.
Looks like you are meaning something else. let us know more details.
Regards,
John.
Hello @rommel_oli ,
In table settings , you can remove some columns from the replication. see transformations for a single table .
A sample: select the column and press button "<" to remove the column:
Hope this helps.
BTW, any detailed info about the max columns is 500? And what's the Replicate version.
Regards,
John.
Hello @rommel_oli ,
I have a table with more than 500 columns. Since Qlik is limited to only 500 columns per table...
There is not such limitation in Replicate. Oracle Maximum Columns per table is 1000. SQL Server Maximum Columns per table is 1024. In SQLite (it's Replicate Repository DB), the maximum columns number per table is 2000.
The 1000 columns tables are replicating successfully in Replicate, include replication from Oracle to Oracle, and from Oracle to SQL Server.
Looks like you are meaning something else. let us know more details.
Regards,
John.
Hmm,
I don't think there is a hard 500 column limit. I do believe there is a maximum SQL statement length of perhaps 32KB (or 64KB) which can be exceeded using 500 longish (20 - 30 bytes ) column names.
Just create a test?
If it is the statement length, and also if it is the column count, maybe a view can be used as source instead of the actual table renaming, or skipping, certain columns?
Hein.
Hein,
I think Oracle VIEWS cannot be used to select TABLES.
John,
The 500 columns is due to Qlik limitation of creating the statement which is 31K chars which I believed is reached when column names are almost around 30 chars limit. I have tables with 800 columns but not having the issue since column names are relatively small. What i noticed is when most of the column names are at least 25 chars and the values are lengthy.
Thanks for sharing the way to filter out columns. I will try this one. I am thinking of creating 2 tables in the target and join them together via a view as the final result.
Rommel
>>> due to Qlik limitation of creating the statement which is 31K chars
I could easily be wrong but I don't think it is a Qlik Replicate limitation, but an (Oracle) limitation. Look carefully to the error message. That's not to say Replicate could possibly be smarter about it.
Hein
Sounds it is Oracle limitation. I did following test with a table which has 1000 columns and each column name is 30 characters long.
Oracle -> Oracle
Replicate reported ORA-01460: unimplemented or unreasonable conversion requested.
Oracle -> SQL Server
Record can be inserted successfully.
Desmond
Hello @rommel_oli , copy @Heinvandenheuvel ,
You are right. there is limitation in Replicate: if many columns name are long enough, then the composed INSERT SQL cannot be run, got error:
[ASSERTION ]W: Long Oracle statements (longer than 32K) cannot be applied, causing a data error ORA-01460
This is Replicate limitation instead of Oracle limitation. We have some options for it:
1- as you said, remove some columns. or
2- rename the columns name in transformation, eg shorten some of the columns name to make sure the whole SQL length less than 32K chars. For example I shorten the column name from
COLUMNNAMEISLOOOOOOOOOOOOOG001 to COL001
and apply the transformation to all columns, sample as below. then it works perfect.
3- split the table into multiple tasks, or use materialized view.
However as the renaming columns name is a once job and it solve the problem completely, this is the best choice in my opinion. Certainly it's a boring job to operate so many columns name in GUI, an easier way is exporting the task to JSON, edit/modify the JSON then import it back to Replicate repository. IN fact this is what I did in my labs test.
Best Regards,
John.
Yup, its Oracle limitation.