• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 499
  • Last Modified:

MS SQL Server - MIPRO - Workaround to execute Inner Join

MS SQL Server

Hi Experts

My problem now is that I have a COTS GIS (MapInfo Pro) inerfaced directly with my SQL Server database.  The data on a large number of spatial entities is stored in the dB in two tables, a common maser table (Country_Facility) and a specialist table for each Facility Type (eg Facility_Education).  Location_Code is the common PK.
Aftre much struggling MapInfo Corp have admitted there is a known bug in their software that prevents the correct parsing of an INNER JOIN when quering an external SQL database.  They have offered some workarounds but I cannot get the SQL approach to work and need some help please.  I have tried a number of options based on the excellent assistance I have received recently but still to no avail.

MapInfo offered this as an example workaround:
From MI
BUT your right the Expert Mode dialog isn't parsing the INNER JOIN correctly. It is a known bug.

Three Workarounds:
1.  A inner join can be performed by passing the following query:
select * from CITY1K, USA where CITY1K.STATE=USA.STATE My version looks like :-

Select *
From TDB_USER.Country_Facility c, TDB_USER.Facility_Education f
WHERE c.Location_Code = f.Location_Code
AND c.Nation_ID like '6%'

I get Error 'Ambiguous column name 'Location_Code'

The second work around is to create a 'View' in SQL Server which performs the required INNER JOIN and then access the resultant virtual table.  This would be a excellent solution but although each table pair has a common PK, MapInfo doesnt seem able to 'see' the Primary Key and in its absence cannot make the table 'editable' which is essential.

Very Grateful for any ideas

DavidR

0
davidjrichardson
Asked:
davidjrichardson
1 Solution
 
Aneesh RetnakaranDatabase AdministratorCommented:
>I get Error 'Ambiguous column name 'Location_Code'
I hope you got this error at the front end. in such case, replace * with the columns, while replacing, make sure that you select only one among  c.Location_Code , f.Location_Code
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
I agree with aneethattingal, you need to replace the SELECT * ... by SELECT c.Location_Code, c.Nation_id ... etc
the problem is that with SELECT *, you would have 2 times the column named Location_Code, which will give problems in the end result.
0
 
davidjrichardsonAuthor Commented:
Thanks very much for that although I should really have caught that one!!

However although the Query now works fine I run into MapInfo Issues which I have MI Corp looking at.

I also pursued the View Option but have got into a bit of trouble.

Because MIPro didn't seem able to see the PK from the underlying tables that form the View, and consequently could not make the table capable of being displayed on the map,  I ran (not all of which I confess I understand but seemed the only way of successfully creating a Unique Index which MIPro needs)

SET NUMERIC_ROUNDABORT OFF
GO
SET ANSI_PADDING,ANSI_WARNINGS,CONCAT_NULL_YIELDS_NULL,ARITHABORT,QUOTED_IDENTIFIER,ANSI_NULLS ON
GO

CREATE   VIEW View_Airport
WITH   SCHEMABINDING
AS
   SELECT     c.Location_Code, c.Location, c.Facility_Name,
                      c.Category_Code, c.Nation_ID, c.LatLong,
                      c.YCoord, c.XCoord, c.UTM, c.Air_MOT,
                      c.Sea_MOT, c.IWW_MOT, c.Road_MOT,
                      c.Rail_MOT, c.Pipe_MOT, f.Type, f.Uses,
                      f.ICAO, f.Elevation_m
FROM         TDB_USER.Country_Facility c INNER JOIN
                      TDB_USER.Facility_Airport f ON c.Location_Code = f.Location_Code
WHERE     (c.Nation_ID LIKE '6%') OR
                      (c.Nation_ID LIKE '5%')
 GO

CREATE UNIQUE CLUSTERED INDEX IView_Airport ON TDB_USER.View_Airport (Location_Code)
GO

This allowed me to get the View both mappable and editable from the MIPro interface (notwithstanding there is then another MIPro issue).    However I am now getting an error when I try and update my basic Country_Facility table as below:

"UPDATE failed because the following SET options have incorrect settings: 'ARITHABORT'.. Operation Cancelled.
Can someone rescue me please !

DavidR
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now