Parameterizing SQL Views Geoserver

Question

I must do a view in geoserver. I seen some example here: http://docs.geoserver.org/stable/en/user/data/database/sqlview.html. The example show the operator like for parameterizing view. Suppose, now that in WFS request there is a value for a field (named filed1) example &viewparams=value1:a. If i use the operator like

Select filed1, filed2, field3 
From myTable 
where field1 like %value1% 

the view return tuples where filed1 is a, aa, aaa, aaaa, aaaaa, etc, but i want tuples where filed1='a'. Can i use the operator =, if yes how write my query?

Select filed1, filed2, field3 
From myTable 
where field1 = .......

• ragnvald: Some challenges related to parameters in WMS and standardization is mentioned here: mindland.com/wp/…

Answer 1

From the page you link to:

Within the SQL View query, parameter names are delimited by leading and trailing % signs. The parameters can occur anywhere within the query text, including such uses as within SQL string constants, in place of SQL keywords, or representing entire SQL clauses.

So I suspect you can do:

Select filed1, filed2, field3 
From myTable 
where field1 = %value1%

• Riccardo: Iant, my query is: Select uri_placeinst, ST_AsText(coord_placeinst) as coord_place_inst From public."NewInfoGeometric" where uri_contesto = %urlcont% and GeometryType(ST_AsText(coord_placeinst)) = %typegeom% and tipofeature=%typefeat% . After click on Refresh for convalidate i have ERROR: syntax error at or near "and" Posizione: 141
• Ian Turton: and what do you want me to do about it? look in the log file to see what the actual sql query sent was and use that information to fix your syntax.
• Riccardo: because you responded to my post I thought you might like to know that your suggestion is not the right way to parameterize a request. what to do? anything!
• Ian Turton: No, the parameterization is correct your sql syntax is wrong
• Riccardo: I solved my problem. SQL query is right, the parameterization is wrong, however thanks for your reply.

Answer 2

If the sql command:

Select 
 uri_placeinst, 
 ST_AsText(coord_placeinst) as coord_place_inst 
From 
 public."NewInfoGeometric" 
where 
 uri_contesto = %urlcont% 
 and GeometryType(ST_AsText(coord_placeinst)) = %typegeom% 
 and tipofeature=%typefeat%

The expression GeometryType(ST_AsText(coord_placeinst)) returns a string perhaps you need to put single quotes around the %typegeom% expression.