Skip to main content
Solved

Importing published parameter configuration value order

nic_ran
Contributor
Forum|alt.badge.img+16

Hi,

 

I'm importing configuration values for a "Choice or Text" published parameter. The imported values are coming from a database and the list is fairly long.

 

 

I'd like the imported values to be sorted alphabetically but can't figure out a way to do it automatically. I tried adding an "order by" statement to the where clause, e.g.

1=1 order by column_name

but it had no effect.

 

 

I know that I could manually edit the .fmw file with a text editor but my list of imported values is long and will likely need to be updated on a fairly regular basis.

 

 

Anyone have any thoughts on this?

 

 

thanks,

 

Nic

Best answer by takashi

Hi Nic,

 

 

It would be the best solution that FME is improved about this issue. As a present solution, how about adding a view which returns quoted parameter values to the database, and importing it?   e.g. create view vw_name as select '"'||column_name||'"' as param_value from table_name;   I have not the Oracle environment, but this way worked successfully on MS SQL Server 2008 R2.

 

Takashi
View original
Did this help you find an answer to your question?
This post is closed to further activity.
It may be a question with a best answer, an implemented idea, or just a post needing no comment.
If you have a follow-up or related question, please post a new question or idea.
If there is a genuine update to be made, please contact us and request that the post is reopened.

11 replies

nic_ran
Contributor
Forum|alt.badge.img+16
  • nic_ranContributor
  • Author
  • Contributor
  • May 10, 2013
So, I just went and copied the values into a text editor to manually sort them and noticed that any values which include spaces are being surrounded by double-quotes. These values then end up at the top of the list (in order) and the single word values end up in a seperate ordered list below.

 

 

Seems like this might be a "feature" of the import process as the values are not quoted in Oracle.
fmelizard
Safer
Forum|alt.badge.img+18
  • fmelizardSafer
  • Safer
  • May 10, 2013
any chance the data type might have something to do with it?
dustin
Influencer
Forum|alt.badge.img+31
  • dustinInfluencer
  • Influencer
  • May 10, 2013
Could you use an AttributeTrimmer to remove the quotes?
nic_ran
Contributor
Forum|alt.badge.img+16
  • nic_ranContributor
  • Author
  • Contributor
  • May 10, 2013
Thanks for your responses.

 

 

@Cartoscro - I cant use the AttributeTrimmer because this isn't part of the workspace. It's when I import values as part of the configuration process of defining a published parameter.

 

 

@Itay - the published parameter's type is "Choice or Text". The Oracle datatype is varchar2. Shouldn't be any conflict there.

 

 

I think this is just an issue with the way FME is importing the values. Perhaps it should be quoting all values when they're imported and not just those that contain spaces...
fmelizard
Safer
Forum|alt.badge.img+18
  • fmelizardSafer
  • Safer
  • May 10, 2013
Risking stating the obvious, you could create and order the list of parameter values before hand, with a separate ws.

 

I admit it is not very elegant but I dont see any other way to order the values when importing via the GUI.

 

This can for sure go down as a serious improvement request to safe.
nic_ran
Contributor
Forum|alt.badge.img+16
  • nic_ranContributor
  • Author
  • Contributor
  • May 10, 2013
Thanks Itay. I had thought of doing that but, as you say, it's not very elegant.

 

 

I guess I'll just have to see how often my source data changes and how frustrating it becomes to change it by hand before proceeding down that path. I've done it once before for a more critical workspace, so I know it's technically possible...

 

takashi
Influencer
  • takashiInfluencer
  • Best Answer
  • May 11, 2013
Hi Nic,

 

 

It would be the best solution that FME is improved about this issue. As a present solution, how about adding a view which returns quoted parameter values to the database, and importing it?   e.g. create view vw_name as select '"'||column_name||'"' as param_value from table_name;   I have not the Oracle environment, but this way worked successfully on MS SQL Server 2008 R2.

 

Takashi
nic_ran
Contributor
Forum|alt.badge.img+16
  • nic_ranContributor
  • Author
  • Contributor
  • May 13, 2013
Brilliant solution Takashi! I tested it out in Oracle and it works.

 

thanks,

 

Nic
mark2atsafe
Safer
Forum|alt.badge.img+44
Hi Nic,

 

That looks like a definite bug to me. I've filed it as PR#45374 so that we get it fixed. I'll let you know when this is done.

 

 

In the meantime, great solution Takashi!

 

 

Regards

 

 

Mark
mark2atsafe
Safer
Forum|alt.badge.img+44
...and it's fixed already. Won't be in FME until 2014 though. If you have a need for this to be backported to a 2013 Service Pack then do let me know, but it sounds like you have a good workaround.
nic_ran
Contributor
Forum|alt.badge.img+16
  • nic_ranContributor
  • Author
  • Contributor
  • May 13, 2013
Thanks for the fix!

 

I'm fine with using my workaround for the time being.

Helpful Members This Week

Community Stats

31,372
Posts
118,993
Replies
39,004
Members

Latest FME

Terms & ConditionsCookie settings

Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings
A product of
Safe Software respectfully acknowledges that we live, learn and work on the traditional and unceded territories of the Kwantlen, Katzie, and Semiahmoo First Nations.