MapInfo Pro

Hi Steven

The standard way of opening a table from a database into Pro is to just select a single table.

But you also have access to a more advanced way - the Expert Mode - which allows you to write a SQL Select statement using the dialect of the database.

You access the Expert Mode via Options on the Open DBSM dialog.

On the Open DBMS Table Options dialog check Expert Mode and click the SQL View... button to enter your SQL Select statement.

It will initial with the query used to select all records from the table you had selected before going into Options.

You can now modify this query in any way you want but keep in mind that you are writing a SQL statement based on the SQL language of the database server, not using the SQL language of MapInfo Pro. There are certain differences.

Thanks Peter! This looks exactly like what I was looking for.

Hi @Peter Horsbøll Møller? ,

I went and checked this out and it seems to export individual table files for each physical table in the database. Is there any way I can take the MSSQL Query I have and combine the data into one MapInfo Table (see below query) or alternatively is this something I should try achieve using MapBasic?

SELECT 

pid.Location,

prop.Vg_Number,

pid.Part_Location,

pid.Lot_No,

pid.Part_Lot,

pt.Description,

o.Title,o.Init,

o.Other_Names,

o.Surname,

o.Gender,

o.Date_Of_Birth,

o.Pensioner_Number,

o.Abn,

o.Individual,

o.Inactive,

o.Telephone_Number,

o.Mobile_Number,

ralp.Rural_Road_Num,

CONCAT(ras.Street_Name,' ',UPPER(LEFT(cast(ras.Street_Type as nvarchar(max)),1)) + LOWER(SUBSTRING(cast(ras.Street_Type as nvarchar(max)),2,LEN(cast(ras.Street_Type as nvarchar(max)))))) As Rural_Road_Name,

UPPER(LEFT(cast(ralp.Rural_Suburb as nvarchar(max)),1)) + LOWER(SUBSTRING(cast(ralp.Rural_Suburb as nvarchar(max)),2,LEN(cast(ralp.Rural_Suburb as nvarchar(max))))) As Rural_Suburb,

prop.State_In_Australia as Rural_State,

ralp.Rural_Post_Code,

prop.House_No,

CONCAT(prs.Street_Name,' ',UPPER(LEFT(cast(prs.Street_Type as nvarchar(max)),1)) + LOWER(SUBSTRING(cast(prs.Street_Type as nvarchar(max)),2,LEN(cast(prs.Street_Type as nvarchar(max)))))) As Prop_Street_Name,

UPPER(LEFT(cast(prop.Suburb as nvarchar(max)),1)) + LOWER(SUBSTRING(cast(prop.Suburb as nvarchar(max)),2,LEN(cast(prop.Suburb as nvarchar(max))))) As Prop_Suburb,

prop.State_In_Australia,

prop.Post_Code,

o.Resid_House_Number,

CONCAT(rs.Street_Name,' ',UPPER(LEFT(cast(rs.Street_Type as nvarchar(max)),1)) + LOWER(SUBSTRING(cast(rs.Street_Type as nvarchar(max)),2,LEN(cast(rs.Street_Type as nvarchar(max)))))) As Resid_Street_Name,

UPPER(LEFT(cast(o.Resid_Suburb as nvarchar(max)),1)) + LOWER(SUBSTRING(cast(o.Resid_Suburb as nvarchar(max)),2,LEN(cast(o.Resid_Suburb as nvarchar(max))))) As Resid_Suburb,

o.Resid_State,

o.Resid_Postcode,

o.Postal_Street_Code,

o.Postal_House_Number,

CONCAT(ps.Street_Name,' ',UPPER(LEFT(cast(ps.Street_Type as nvarchar(max)),1)) + LOWER(SUBSTRING(cast(ps.Street_Type as nvarchar(max)),2,LEN(cast(ps.Street_Type as nvarchar(max)))))) As Postal_Street_Name,

UPPER(LEFT(cast(o.Postal_Suburb as nvarchar(max)),1)) + LOWER(SUBSTRING(cast(o.Postal_Suburb as nvarchar(max)),2,LEN(cast(o.Postal_Suburb as nvarchar(max))))) As Postal_Suburb,

o.Postal_State,

o.Postal_Post_Code

FROM PROPERTY_ID pid

LEFT JOIN PROPERTY_ID_RURAL_ADDRESSES_LP ralp ON ralp.Land_Parcel=pid.Land_Parcel

LEFT JOIN PROPERTIES prop ON prop.Assess_No = pid.Location

LEFT JOIN PARCEL_TYPES pt ON pid.Parcel_Type = pt.Parcel_Type

LEFT JOIN OWNERS o ON prop.Ratepayer=o.Owner_Code

LEFT JOIN STREETS ras ON ras.Street_Code=ralp.Rural_Street_Code

LEFT JOIN STREETS prs ON prop.Street_Code=prs.Street_Code

LEFT JOIN STREETS rs ON o.Resid_Street_Code=rs.Street_Code

LEFT JOIN STREETS ps ON o.Postal_Street_Code=ps.Street_Code

Have you tried running the query in MSSQL Management Studio? Does it work here? Do you get any errors when running it from Pro?

Hi Peter,

Yes I've ran the query successfully in both MSSQL Studio as well as in a PHP web Script and it works fine.

MapInfo seems to only allow me to add advanced SQL on a per-selected table basis.

I've tried to manually import each of the relevant tables into MapInfo and I've started playing around with the JOINS in the MapInfo SQL Select Dialog to see if I can diagnose what MapInfo doesn't like about it. So far it looks like MapInfo doesn't handle JOINS or aliases well.

I've found how to do column aliases using colname "colalias" but cant work out how or if I can use table aliases ie. MainTable mt, SecondaryTable st.

I'm not sure that its currently possible if MapInfo has a way to use existing MSSQL queries in DBMS to build a single MapInfo table, but that would be amazing. It seems like this is currently assumed there is an SQL query for each and every table individually and no ability to use an existing Database query to build a table from.

Any help would be appreciated

Yeah, my initial though was also that you might run into a few problems with the table aliases.

Typically, I would recommend that you create a view in SQL Server, add the view to the MapCatalog and then access the data via this view.