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

[miansi]

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 FeatureDource 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.-tp2356197p2356197.html
Sent from the FDO Users mailing list archive at Nabble.com.