[fdo-users] SQL 2008. View with JOINS. Not able to select features on the layer.

[Jackie Ng]

Maybe try looking at this data source through FDO Toolbox
(http://fdotoolbox.googlecode.com), and take a look at the feature classes,
and see if the view (with the join) is structurally different from the view
(without the join). 

For example, I could foresee some problem if the view (without the join) has
an Identity Property and the view (with the join) doesn't.

- Jackie


miansi wrote:
> 
> Hello,
> 
> Please advise what, where and how should I poke :confused:
> 
> Here is the issue:
> 
> I have SQL 2008 table with geospatial data. I created Dataconnection,
> Layer, Map and am able to see data from view, which selecting everything
> from my geospatial table:
> 
> TABLE:
> /****** Object:  Table [dbo].[Sanit_GM]    Script Date: 02/19/2009
> 16:14:56 ******/
> SET ANSI_NULLS ON
> GO
> 
> SET QUOTED_IDENTIFIER ON
> GO
> 
> SET ANSI_PADDING ON
> GO
> 
> IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id =
> OBJECT_ID(N'[dbo].[Sanit_GM]') AND type in (N'U'))
> BEGIN
> CREATE TABLE [dbo].[Sanit_GM](
> 	[GeometryItem] [geometry] NULL,
> 	[GeometryIndex] [int] IDENTITY(1,1) NOT NULL,
> 	[AssetID] [varchar](10) NULL,
> 	[InstallYea] [int] NULL,
> 	[LifeStatus] [varchar](50) NULL,
> 	[LocDesc] [varchar](50) NULL,
> 	[MaterialID] [varchar](50) NULL,
> 	[SpatialKey] [varchar](10) NULL,
> 	[SpatialSta] [varchar](50) NULL,
> 	[AssetType] [varchar](50) NULL,
> 	[Backfill] [varchar](50) NULL,
> 	[BedMat] [varchar](50) NULL,
> 	[Capacity] [decimal](38, 18) NULL,
> 	[Diameter] [int] NULL,
> 	[DoStreamIn] [decimal](38, 18) NULL,
> 	[HasLining] [bit] NULL,
> 	[Height] [int] NULL,
> 	[Length] [decimal](38, 18) NULL,
> 	[LiningYear] [int] NULL,
> 	[LiningLen] [decimal](38, 18) NULL,
> 	[PipeClass] [varchar](50) NULL,
> 	[PipeFunc] [varchar](50) NULL,
> 	[PipeShape] [varchar](50) NULL,
> 	[UpStreamIn] [decimal](38, 18) NULL,
> 	[Width] [varchar](50) NULL,
>  CONSTRAINT [Sanit_GM_pkey] PRIMARY KEY CLUSTERED 
> (
> 	[GeometryIndex] ASC
> )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY =
> OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
> ) ON [PRIMARY]
> END
> GO
> 
> SET ANSI_PADDING OFF
> GO
> 
> IF NOT EXISTS (SELECT * FROM sys.check_constraints WHERE object_id =
> OBJECT_ID(N'[dbo].[Sanit_GM_GeometryItem_srid]') AND parent_object_id =
> OBJECT_ID(N'[dbo].[Sanit_GM]'))
> ALTER TABLE [dbo].[Sanit_GM]  WITH CHECK ADD  CONSTRAINT
> [Sanit_GM_GeometryItem_srid] CHECK  (([GeometryItem].[STSrid]=(4269) OR
> [GeometryItem] IS NULL))
> GO
> 
> IF  EXISTS (SELECT * FROM sys.check_constraints WHERE object_id =
> OBJECT_ID(N'[dbo].[Sanit_GM_GeometryItem_srid]') AND parent_object_id =
> OBJECT_ID(N'[dbo].[Sanit_GM]'))
> ALTER TABLE [dbo].[Sanit_GM] CHECK CONSTRAINT [Sanit_GM_GeometryItem_srid]
> GO
> 
> IF NOT EXISTS (SELECT * FROM sys.check_constraints WHERE object_id =
> OBJECT_ID(N'[dbo].[Sanit_GM_GeometryItem_type]') AND parent_object_id =
> OBJECT_ID(N'[dbo].[Sanit_GM]'))
> ALTER TABLE [dbo].[Sanit_GM]  WITH CHECK ADD  CONSTRAINT
> [Sanit_GM_GeometryItem_type] CHECK 
> (([GeometryItem].[STGeometryType]()='MULTILINESTRING' OR [GeometryItem] IS
> NULL))
> GO
> 
> IF  EXISTS (SELECT * FROM sys.check_constraints WHERE object_id =
> OBJECT_ID(N'[dbo].[Sanit_GM_GeometryItem_type]') AND parent_object_id =
> OBJECT_ID(N'[dbo].[Sanit_GM]'))
> ALTER TABLE [dbo].[Sanit_GM] CHECK CONSTRAINT [Sanit_GM_GeometryItem_type]
> GO
> 
> 
> 
> 
> VIEW:
> SELECT     GeometryItem, GeometryIndex, AssetID, InstallYea, LifeStatus,
> LocDesc, MaterialID, SpatialKey, SpatialSta, AssetType, Backfill, BedMat,
> Capacity, Diameter, 
>                       DoStreamIn, HasLining, Height, Length, LiningYear,
> LiningLen, PipeClass, PipeFunc, PipeShape, UpStreamIn, Width
> FROM         dbo.Sanit_GM AS new
> 
> I am able to see layer pointing to this view. AND I am able to select
> features on the map.
>  http://n2.nabble.com/file/n2356197/1.Selecting.png 
> http://n2.nabble.com/file/n2356197/2.Selected.png 
> 
> If I modify View to include any JOIN I will be able to see layer pointing
> to view, BUT would NOT be able to select features on the map:
>  http://n2.nabble.com/file/n2356197/1.Selecting%2B-%2BJOIN.png 
> http://n2.nabble.com/file/n2356197/2.Selected%2B-%2BJOIN.png 
> 
> VIEW:
> SELECT     new.GeometryItem, new.GeometryIndex, new.AssetID,
> new.InstallYea, new.LifeStatus, new.LocDesc, new.MaterialID,
> new.SpatialKey, new.SpatialSta, new.AssetType, 
>                       new.Backfill, new.BedMat, new.Capacity,
> new.Diameter, new.DoStreamIn, new.HasLining, new.Height, new.Length,
> new.LiningYear, new.LiningLen, new.PipeClass, 
>                       new.PipeFunc, new.PipeShape, new.UpStreamIn,
> new.Width
> FROM         dbo.Sanit_GM AS new INNER JOIN
>                       dbo.Asset AS a ON a.DisplayId = new.AssetID
> 
> 
> Now, MYSTERY %-|
> 
> If I will not refresh Dataconnection after adding join to the view - I am
> still able to select features on the map.
> 
> Since Map, Layer and FeatureSource XML looks exactly the same in both
> cases I wonder what is the difference? What is changing when FDO detects
> JOIN in view?
> Can I override this change? Can I force FDO to ignore it? Is it FDO, which
> prevents selection or something else?
> 
> Is this right forum to post? Is this a bug?
> 
> 
> Thank you,
> 
> 

-- 
View this message in context: http://n2.nabble.com/SQL-2008.-View-with-JOINS.-Not-able-to-select-features-on-the-layer.-tp2356197p2356328.html
Sent from the FDO Users mailing list archive at Nabble.com.