Oracle Communities

Oracle Analytics and AI

Child Item

How to preserve the columns order like in the source table after importing data?

Hi all,
When you get the metadata of a new table to add it to the physical layer, you can see the columns there in alphabetical order.

This is because of an option that is checked by default. Menu Tools, options, Sort objects. If it is not checked, I expect it should show the columns in the original order as the table in the source. Now I have a dim table with 276 columns, where we defined very carefully the order of the columns. In the physical layer I still see the columns in alphabetical order except for the 2-3 first columns that seemed to be random.
I found a bug reported in 2022 that also describes this issue: https://support.oracle.com/epmos/faces/BugDisplay?_afrLoop=164573069546631&parent=DOCUMENT&sourceId=2875313.1&id=34163355&_afrWindowMode=0&_adf.ctrl-state=2bribrmio_129

Do I something wrong? Did it work in an older version?

Thank you for your time

Find more posts tagged with

Accepted answers

Gianni Ceresa

Well … because you can define the physical tables manually, creating one column after the other, that's also a way to define the order of your physical columns (therefore the option to visually sort them A-Z or not). But I would say the option to uncheck the sort of physical columns in that list is more a generic feature and the option menu let you turn it on or off everywhere it is used (the feature is there, therefore doesn't cost them anything to let you enable or disable it).

Just be careful when defining your object by copy/paste from the RPD to a text editor and back: you should strictly respect the syntax, and you can still corrupt your RPD. Backup before and consistency check is your best friend :)

All comments

Gianni Ceresa

In the physical layer there isn't a real concept of "order", you can't move columns up and down (maybe, unchecking the sort option, if you change the table definition it will keep that order, but it's a maybe still). The order of columns starts being a real thing in the logical layer, where you can explicitly set the order of your columns moving them up and down.

Because of the layer structure of the RPD, the order in the physical layer doesn't have any impact on the rest.

What you are maybe annoyed with is that when you create your logical layer you must reorder the columns yourself.

I can't say if it did ever work or not, if the metadata import wizard does respect the original order or not, because the physical layer doesn't matter: what matter in the RPD is the logical layer, that's where the order of things start having a meaning based on the business needs.

Gianni Ceresa

If the order in the physical layer is a key element for you, copy the table to a text editor, reorder the columns as you need them in the table definition (you can also script that if you have 276 and you want the order of your database) and paste it back in the RPD (rename the existing table, to avoid a mess with overriding as I have no idea what order you would be left with).

If you disabled the A-Z sorting in the options for the physical columns, that's apparently the only way to set the order of your columns in the physical layer.

Wim

Thank you Gianni for your answer. Why would there be an option to check on/off? I's a pity. But your idea to use a text-editor might be very helpful. I'll give it a go

Thank you again for your time

Gianni Ceresa