MS SQL Server - MIPRO - Workaround to execute Inner Join

Posted on 2007-07-20
Last Modified: 2008-02-01
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


Question by:davidjrichardson
    LVL 75

    Expert Comment

    by:Aneesh Retnakaran
    >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
    LVL 142

    Accepted Solution

    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.

    Author Comment

    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)


    CREATE   VIEW View_Airport
       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%')

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

    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 !


    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
    JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
    Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
    Via a live example, show how to shrink a transaction log file down to a reasonable size.

    755 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now