Geoserver sql view - parametric errors

Question

I can not get it to work. I started with complex sql and ended with 'hello word' example, but I get errors: My sqls are:

• SELECT t.id, t.user_id, t.coordinates, t.size, t.price FROM my_table t WHERE t.id=%id_param% Error: Failed to create SQL view: ERROR: syntax error at or near ")" Position: 100
• SELECT t.id, t.user_id, t.coordinates, t.size, t.price FROM my_table t WHERE t.id='%id_param%' Error: Failed to create SQL view: ERROR: invalid input syntax for integer: "" Position: 100

I am not sure, what I am missing. I am using approx 2 months old OpenGeo suite (4.0.2).

About GeoServer General information about GeoServer Build Information Version 2.4-SNAPSHOT Git Revision f295d37d70c3b1f0e51942e8b031253e4d75459 Build Date 20-Jan-2014 22:29 GeoTools Version 10-SNAPSHOT (rev 3bb3df4f410750baf9ebc48c9c475a70642aadb6) GeoWebCache Version 1.5.0 (rev 1.5.x/f981cafd349886bb2a1ef45067f26b1aa8e4aff4)

Answer 1

Have you set a Default value for the id_param under the SQL view parameters section? Try using your first query (without the quotes) and entering 0 for under the default value

• Auspex: OMG. Simple but obscure...

Answer 2

Perhaps you are missing the select syntax with "like" as described in this post

• Makla: LIKE for interger field?
• MickyInTheSky: you have never told us that "id_param" was a integer field. I edited my answer.
• Makla: I though that is logic based on field name.
• MickyInTheSky: Does the syntax work in pgadmin or psql?
• Makla: Yes it does work in pgadmin, but not geoserver.