Re: [mapguide-users] Mapguide 3.0 Postgre view problem
Two single quotation did the trick. Thanks you very much. -- View this message in context: http://osgeo-org.1560.x6.nabble.com/Mapguide-3-0-Postgre-view-problem-tp5298765p5299227.html Sent from the MapGuide Users mailing list archive at Nabble.com. ___ mapguide-users mailing list mapguide-users@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/mapguide-users
Re: [mapguide-users] Mapguide 3.0 Postgre view problem
solution 1: (for programers people !) note: '' is two Single quotation marks
guide_generic_search_table('CASE when owner = 1 THEN ''one'' when owner =
2 then ''TWO'' END ', 'piping.pipe', 'pipe.dp_oid=' || symg.dp_oid) AS owner
solution 2: (for normal people!)
create a view vw_pipe with the field CASE when owner = 1 THEN 'one' when
owner = 2 then 'TWO' END and use then on fuction:
guide_generic_search_table('field_case', 'piping.vw_pipe', 'pipe.dp_oid=' ||
symg.dp_oid) AS owner
--
View this message in context:
http://osgeo-org.1560.x6.nabble.com/Mapguide-3-0-Postgre-view-problem-tp5298765p5299224.html
Sent from the MapGuide Users mailing list archive at Nabble.com.
___
mapguide-users mailing list
mapguide-users@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/mapguide-users
Re: [mapguide-users] Mapguide 3.0 Postgre view problem
Thank for you assistance but I cant get "CASE WHEN" things working.
What I'm doing wrong:
...
guide_generic_search_table('CASE when owner = 1 THEN one when owner = 2
then TWO END', 'piping.pipe', 'pipe.dp_oid=' || symg.dp_oid) AS owner
--
View this message in context:
http://osgeo-org.1560.x6.nabble.com/Mapguide-3-0-Postgre-view-problem-tp5298765p5299215.html
Sent from the MapGuide Users mailing list archive at Nabble.com.
___
mapguide-users mailing list
mapguide-users@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/mapguide-users
Re: [mapguide-users] Mapguide 3.0 Postgre view problem
Hi Kajar, Yes for both issues. You have to keep in mind that the function requires 3 parameters 1) return field, which can be any valid expression, with when, other functions etc. But remember only one field (or expression) per function call 2) a table name, but can be a view for example 3) a filter expression, which can combine as many boolean expressions as necessary. Here the most important point, this expression should return only 1 line, or none. If you return more than one you will have an error of exception. The trick here is to combine the expression with the values you have from your main table. Knowing how to do this, the sky is the limit !!! ;-) Abs Seba -- View this message in context: http://osgeo-org.1560.x6.nabble.com/Mapguide-3-0-Postgre-view-problem-tp5298765p5299198.html Sent from the MapGuide Users mailing list archive at Nabble.com. ___ mapguide-users mailing list mapguide-users@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/mapguide-users
Re: [mapguide-users] Mapguide 3.0 Postgre view problem
Seba, I have some additional question about the function. 1)Can I use CASE WHEN condition with this function I have situation that owner values are just numbers and I want to convert those values to some text. Sample: CASE WHEN owner = 1 then 'one' ELSE 'none' End 2) Can I have also multiple conditions when join values ,'ditch.dp_oid=' || grag.dp_oid and 'ditch.some_other_id' || grag.some_other_id) Kajar -- View this message in context: http://osgeo-org.1560.x6.nabble.com/Mapguide-3-0-Postgre-view-problem-tp5298765p5299171.html Sent from the MapGuide Users mailing list archive at Nabble.com. ___ mapguide-users mailing list mapguide-users@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/mapguide-users
Re: [mapguide-users] Mapguide 3.0 Postgre view problem
Yeah, I got it working with other tables where PK is defined, then it works, of cause there is little peformance drop compered to regular view with join, but selection is possible. So when there is no PK field in view then view become as nonselectable when using Mapguide. I have tables without PK defined so I need some workaround how to visualize those tables with Mapguide when I need to use joins between tables. Maybe I can define PK's for those tables, but I dont know is it possible. I tried also use Mapguide join when creating dataconnection using Studio, but result(layer in map) is really slow. Sad that Postgresql havent same PK defining function for views like Oracle have. Kajar -- View this message in context: http://osgeo-org.1560.x6.nabble.com/Mapguide-3-0-Postgre-view-problem-tp5298765p5298897.html Sent from the MapGuide Users mailing list archive at Nabble.com. ___ mapguide-users mailing list mapguide-users@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/mapguide-users
Re: [mapguide-users] Mapguide 3.0 Postgre view problem
You should update the layer (recreate if need) and mapguide (if you need to stop and restart the service) Try and see if it works: 1) Try to create another view only from the main table (no join or function), remember to put the primary key in the view 2) Create a new layer for this new view 3) test the mapguide selection 4) If it works, please create a new layer for the view with the function and test again, remembering to include the primary key. The use of functions is not detected by mapguide as a join, so it should work! -- View this message in context: http://osgeo-org.1560.x6.nabble.com/Mapguide-3-0-Postgre-view-problem-tp5298765p5298852.html Sent from the MapGuide Users mailing list archive at Nabble.com. ___ mapguide-users mailing list mapguide-users@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/mapguide-users
Re: [mapguide-users] Mapguide 3.0 Postgre view problem
Thank you. I created view but selection is still not working. -- View this message in context: http://osgeo-org.1560.x6.nabble.com/Mapguide-3-0-Postgre-view-problem-tp5298765p5298827.html Sent from the MapGuide Users mailing list archive at Nabble.com. ___ mapguide-users mailing list mapguide-users@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/mapguide-users
Re: [mapguide-users] Mapguide 3.0 Postgre view problem
Sorry, the previous URL was not the good one. Try this one instead : http://themapguyde.blogspot.com/2010/08/using-fdo-schema-overrides.html It is a post from Jackie's blog. -- View this message in context: http://osgeo-org.1560.x6.nabble.com/Mapguide-3-0-Postgre-view-problem-tp5298765p5298826.html Sent from the MapGuide Users mailing list archive at Nabble.com. ___ mapguide-users mailing list mapguide-users@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/mapguide-users
Re: [mapguide-users] Mapguide 3.0 Postgre view problem
Hi, You can also take a look at this post : http://osgeo-org.1560.x6.nabble.com/Mapguide-3-0-Postgre-view-problem-tp5298765p5298822.html Hope this helps. Georges -- View this message in context: http://osgeo-org.1560.x6.nabble.com/Mapguide-3-0-Postgre-view-problem-tp5298765p5298825.html Sent from the MapGuide Users mailing list archive at Nabble.com. ___ mapguide-users mailing list mapguide-users@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/mapguide-users
Re: [mapguide-users] Mapguide 3.0 Postgre view problem
I have two tables: 1)grag 2)ditch View I want to create is : CREATE OR REPLACE VIEW public.route_lines_view AS SELECT grag.dp_oid, grag.dp_otype, grag.dp_subtype, grag.dp_ctype, grag.dp_mod, grag.dp_state, grag.shape, ditch.owner, ditch.length FROM piping.grag JOIN piping.ditch ON grag.dp_oid = ditch.dp_oid; I really thankful if you can put my data to you function script, then I got knowing how its really works :) -- View this message in context: http://osgeo-org.1560.x6.nabble.com/Mapguide-3-0-Postgre-view-problem-tp5298765p5298820.html Sent from the MapGuide Users mailing list archive at Nabble.com. ___ mapguide-users mailing list mapguide-users@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/mapguide-users
Re: [mapguide-users] Mapguide 3.0 Postgre view problem
Junction of two table for mapguide is already complex (a little ;-))
First you need to create this function in your postgres, then when creating
the view you will only use a table in the "from" getting the second table or
how many more are needed for use in the function.
View example:
Create view myview as
Select cp1, cp2, guide_generic_search_table ('cp2_second_table',
'name_second_table', 'cp1_second_table_key =' || cp3_fk) as mypesq1 from
mytable
If you need help, please forward a your generic model view
--
View this message in context:
http://osgeo-org.1560.x6.nabble.com/Mapguide-3-0-Postgre-view-problem-tp5298765p5298817.html
Sent from the MapGuide Users mailing list archive at Nabble.com.
___
mapguide-users mailing list
mapguide-users@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/mapguide-users
Re: [mapguide-users] Mapguide 3.0 Postgre view problem
Mu view wasn't complex, its was just simple join between two tables. Take some data from one table and take some from another where tables having one common field for joining. Sry but my SQL knowledge specially functions is far away from perfect so how I can use this function :) -- View this message in context: http://osgeo-org.1560.x6.nabble.com/Mapguide-3-0-Postgre-view-problem-tp5298765p5298810.html Sent from the MapGuide Users mailing list archive at Nabble.com. ___ mapguide-users mailing list mapguide-users@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/mapguide-users
Re: [mapguide-users] Mapguide 3.0 Postgre view problem
Hi, I had the same problem here and really complex views do not work in the guide One solution is to use a function to get fields from the second table CREATE OR REPLACE FUNCTION public.guide_generic_search_table(vfield character varying, vNameTable character varying, vfilter character varying) RETURNS character varying AS $BODY$ DECLARE v_out character varying; cSQL character varying; BEGIN cSQL := 'SELECT '||vfield||' FROM '||vNameTable||' WHERE '||vfilter || ';'; EXECUTE cSQL INTO v_out; return (v_out); END; $BODY$ LANGUAGE plpgsql This way you cheat the mapguide, but it works! []s -- View this message in context: http://osgeo-org.1560.x6.nabble.com/Mapguide-3-0-Postgre-view-problem-tp5298765p5298792.html Sent from the MapGuide Users mailing list archive at Nabble.com. ___ mapguide-users mailing list mapguide-users@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/mapguide-users
Re: [mapguide-users] Mapguide 3.0 Postgre view problem
Sorry... - AG-Carto - Bayonne -- View this message in context: http://osgeo-org.1560.x6.nabble.com/Mapguide-3-0-Postgre-view-problem-tp5298765p5298778.html Sent from the MapGuide Users mailing list archive at Nabble.com.___ mapguide-users mailing list mapguide-users@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/mapguide-users
Re: [mapguide-users] Mapguide 3.0 Postgre view problem
Primary key is not supported in Postgresql views. As I previously said using view without joined tables and primary key works just well. Just cant understand why joins ruin the selection possibility. -- View this message in context: http://osgeo-org.1560.x6.nabble.com/Mapguide-3-0-Postgre-view-problem-tp5298765p5298776.html Sent from the MapGuide Users mailing list archive at Nabble.com. ___ mapguide-users mailing list mapguide-users@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/mapguide-users
Re: [mapguide-users] Mapguide 3.0 Postgre view problem
Hi, Immediatly without reflexion, i ask me if you don't loose the primary key notion with your join in the view... Rémy - AG-Carto - Bayonne -- View this message in context: http://osgeo-org.1560.x6.nabble.com/Mapguide-3-0-Postgre-view-problem-tp5298765p5298770.html Sent from the MapGuide Users mailing list archive at Nabble.com.___ mapguide-users mailing list mapguide-users@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/mapguide-users
