[GIS] Finding values that are different between two tables in MapInfo/MapBasic based on a unique ID

mapbasicmapinfosqlwhere-clause

I am comparing 2 tables which are mostly the same but there have been some amendments, in effect comparing a new version to the old one and displaying all the records with changes in a selection table based on a unique ID.

I run into problems with the following SQL part of the script:

Select * From selectedTableNew, selectedTableOld 
Where (selectedColumnNew = selectedColumnOld) 
And (aColNew <> aColOld) 
Into AmendedRecords

I receive the error

No join specified between selectedTableNew & selectedTableOld. Invalid join condition in WHERE clause."

Best Answer

Make sure that you are using Alias variables in your SQL statement. You might want to change it to something along these lines:

Dim columnNew, selectedColumnNew, selectedTableNew As String
Dim columnOld, selectedColumnOld, selectedTableOld As String
Dim aSelectedColumnNew, aSelectedColumnOld, aColNew, aColOld As Alias

-- Here you assign the values to the string variables
-- I'm assuming you are using a dialog to ask the ser for input
selectedTableNew = "SomeTable"
selectedColumnNew = "SomeColumn"
columnNew = "SomeOtherColumn"
selectedTableOld = "SomeTable2"
selectedColumnOld = "SomeColumn2"
columnOld = "SomeOtherColumn2"

-- Now you link the table and columns to create a Alias
aSelectedColumnNew = selectedTableNew & "." & selectedColumnNew
aSelectedColumnOld = selectedTableOld & "." & selectedColumnOld
aColNew = selectedTableNew & "." & ColumnNew
aColOld = selectedTableOld & "." & ColumnOld

Select * From selectedTableNew, selectedTableOld 
   Where (aSelectedColumnNew = aSelectedColumnOld) 
   And (aColNew <> aColOld) 
   Into AmendedRecords

This should give you the records where the "selected" columns are identical and where the "other" column aren't.

Make sure that SelectedColumnNew/-Old are the ID's that should be the same in the two tables

Related Solutions

[GIS] Finding the type of object in MapBasic (MapInfo)

I think you need to be passing an object variable into the function, not an alias which is just a column reference.

Function IsObjectRegion(selObj As Object) As Logical
Dim ObjectType As SmallInt

ObjectType = ObjectInfo(selObj, OBJ_INFO_TYPE)
Do Case ObjectType
...

If you wanted to test this out with the currently selected object, you could call the function like this:

    Dim test As Logical
    Dim testObject As Object
    testObject = Selection.Obj
    test = IsObjectRegion(testObject)

    Note test

This should return True/False.

James

[GIS] Finding text object variable names in MapInfo/MapBasic

I have done this but as far as I know you have to select the text object using the text that is in that object. Here is some example code which finds an object by looking for the the text in the variable searchText:

Dim searchText, newText as String
Dim oLayout As Object
strLayWinName = WindowInfo(WindowID(FrontWindow()), WIN_INFO_TABLE)
searchText = "@01"
newText = "Text I want to add to the text object"

select * from strLayWinName where val(str$(objectinfo(object,1))) = 10 into strLaySel noselect
select * from strLaySel where Left$(str$(objectinfo(object,3)),Len(searchText)) = searchText into strLayObjSel

oLayout = strLayObjSel.object ' put the selected object into an object variable called oLayout
alter object oLayout info 3, searchText + " " + newText ' where new text is a string variable of your new text
update selection set object = oLayout

To expand on this, the way I use this code is to create a text object on a layout with some standard text at the front of it (for example I place an @ symbol and a number, so on the layout the text would read "@01 Text I want in the text object"). Then I place a white rectangle object on the layout over the "@01" part of the string to hide this.

When using the above code, the searchText variable is set to "@01" and the 7th line of code finds the text object where the first 3 characters are equal to "@01". Once you have this object, it sets its new value to the searchText plus whatever you want to change the remainder of the text object to which you set in the newText variable. If you give all of the text objects on the layout different numbers, then you can refer to them uniquely by checking the first 3 characters of the string. I have edited the code a little to make it clearer.

I know its not a particularly pretty way to do it, but it does work well.

Best Answer

Related Solutions

[GIS] Finding the type of object in MapBasic (MapInfo)

[GIS] Finding text object variable names in MapInfo/MapBasic

Related Question