Skip to main content
  • EN

(on FME 2020.2)

 

Hi, 

 

I have a workspace with an Oracle reader in which I want to filter the features to read with a WHERE clause on a column, let's say COLUMN_A.

 

I created a Choice type published parameter named colA, which contains the possible values for this column.

This way I can filter the features from my table when COLUMN_A contains the value selected in the Choice list.

 

The WHERE clause is : 

"COLUMN_A" = '$(colA)'

Quotes character are used to handle the values with spaces.

 

Problem : the values can contains the quote character as well !

What is expected for the database (Oracle) is that the simple quote in the value should be doubled. For exemple the colA value TES'T should be transformed to TES''T.

 

I tried to find a way to replace the simple quotes with two quotes in the WHERE clause using SQL string functions, without success because it always require to have the doubled quote.

 

The only workaround I found for the moment is to change the Choice parameter to a Choice with alias parameter and double the quotes in the Value but not in the Display name.

Which causes other problems later in the workspace because I need the value without the double quotes (so I have to replace the double quotes to single quote in these cases).

 

Do you have an idea how I can double the quotes only for the WHERE clause of the Oracle reader and this way avoid to have a Choice with alias but only a Choice parameter ?

I'm not sure this is any less work, but instead of using a standard reader, you use a FeatureReader instead, then you can replace a single quote with a double quote in the workspace before it gets used in the where clause.

 

 

I'm not sure this is any less work, but instead of using a standard reader, you use a FeatureReader instead, then you can replace a single quote with a double quote in the workspace before it gets used in the where clause.

 

 

Thanks for your answer.

I already tried this solution before posting my question but without success as I wanted all features merged and by default I got one output port per feature type (I read several tables at once).

I tried again today and I found the correct settings with the Single output port and especially the <Generic> Port / Attributes to Expose.

This way I can get the desired common attributes among the read tables as I can select in the Oracle Reader / User Attributes tab / Exposed attributes choice.

 

If anyone has another idea directly with the Oracle reader, don't hesitate to comment :)

Hi,

I'm having the same problem. I have a WHERE clause using a user parameter. This parameter can either be all numbers or mixed letters/numbers, but should always be read as a string. However, the clause doesn't work when I input something like 05090000 in the user parameter.

 

Has anyone worked a solution?

 

Hi,

I'm having the same problem. I have a WHERE clause using a user parameter. This parameter can either be all numbers or mixed letters/numbers, but should always be read as a string. However, the clause doesn't work when I input something like 05090000 in the user parameter.

 

Has anyone worked a solution? 

 

Hi, 

Hum, not quite the same problem as yours don't involve a quote in the input value.

Did you put you parameter name between quotes in the WHERE clause of your reader ?

e.g. 

"COLUMN"='$parameter_name'

 

If I do so, it just reads the parameter name as is, like the string is litterally "$Num_bv"

Oh! Clearly I didn't try it because it did work just now. Thanks Jeremie, hopefully you find an anwer to your issue as well.

Oh! Clearly I didn't try it because it did work just now. Thanks Jeremie, hopefully you find an anwer to your issue as well.

Good to know it works for you.

On my side, I chose a different solution as mentioned in my answer to ebygomm from 25th june 2021.

Reply


Community Stats

30,124
Posts
114,338
Replies
37,754
Members
Terms & ConditionsCookie 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.