Subscribe to this thread
Home - General / All posts - M9 SQL Difficulties
gpBike1001
20 post(s)
#01-Oct-19 02:32

Using M9 - 169.8

Can't figure it out:

1.

SELECT [Table1].[mfd_id], [Table1].[FieldName2], ..., [Table2].[FieldName1], [Table2].[FieldName2], [Table1].[Geom]

-- INTO [NewTable]

FROM [Table1] LEFT JOIN [Table2]

ON [Table1].[FieldName1] = [Table2].[FieldName1]

results in (as it should) 273 records written to the Results Tab in the Command Window, but I can't save this as an M9 Table

2.

SELECT [Table1].[mfd_id], [Table1].[FieldName2], ..., [Table2].[FieldName1], [Table2].[FieldName2], [Table1].[Geom]

INTO [NewTable]

FROM [Table1] LEFT JOIN [Table2]

ON [Table1].[FieldName1] = [Table2].[FieldName1]

results in the error message: "Cannot add record." , but the [NewTable] Component was created with 14 records appended, not 273

3.

SELECT [Table1].[mfd_id], [Table1].[FieldName2], ..., [Table2].[FieldName1], [Table2].[FieldName2], [Table1].[Geom]

INTO [NewTable]

FROM [Table1] LEFT JOIN [Table2]

WHERE [Table1].[FieldName1] = [Table2].[FieldName1]

results in the error message: "Cannot parse query."

Has anyone got an idea of the correct SQL syntax or a workaround approach to get the results of 1. into an M9 Table component?

I've looked in the M9 User Manual and have drawn a blank.

tjhb

8,883 post(s)
online
#01-Oct-19 05:13

The mfd_id field is special.

(1) Any field named mfd_id always has a BTREE index. You don't need to create that index manually (though probably you should). If you don't add a BTREE, it is added as necessary behind the scenes.*

That means that the values in a field named mfd_id must be unique (and not null).

(*It is possible to create a table having a mfd_id field which appears to have no BTREE defined on it, if you inspect its schema. But the table still behaves exactly if the BTREE index were there. So it is.)

(2) Fields named mfd_id are also self-populating. You never need to supply mfd_id values, since they are added automatically.

You can add values to a mfd_id field manually, provided you respect the uniqueness constraint (and avoid nulls). As soon as you try to add a duplicate or null value, you get the error "Can't add record", and processing will stop.

When you need to keep track of source mfd_id values in a new table, even if there might be duplicates or nulls, insert the values into a field with a different name. A field with a different name is not treated as special.

For an INSERT INTO query, direct mfd_id values into a field with some other name.

For a SELECT INTO query, use an alias, e.g.

SELECT [Table1].[mfd_id] AS [source_id 1], ...

tjhb

8,883 post(s)
online
#01-Oct-19 05:29

(* ... exactly as if ...)

(typo)

Dimitri


5,552 post(s)
#01-Oct-19 06:52

When you need to keep track of source mfd_id values in a new table

A heads up for new SQL'ers, just in case: If a need is felt to do the above, it is probably a mistake. That might arise from the conceptual error of thinking tables are ordered, and then wanting to use any apparent ordering of mfd_id from 1 to n as the "order" of the table. But any order in mfd_id is an illusion.

The mfd_id field is nothing but a synthetic identity field with an index that allows us to manipulate tables. The values it contains mean absolutely nothing and one should never attempt to do anything with them. If order is what we are after, use SQL constructs for ordering results as desired, or, if there is something about the data that provides a real meaning of order, use that something. For example, if we have a table giving the names of countries and a field that gives their population, we might use that field to order them by population.

Sometimes you might import data from a non-DBMS format/system where the keeper of the data organized it too casually, perhaps trusting that the order of records in a table was some permanent thing, an implied order, despite having no explicit "order" field. An example might be a list of roads to be maintained that someone entered into a text list in Notepad, starting the list with those roads in poorer condition to be repaired first, but without any explicit field giving a "priority" number.

Import that list that into a real DBMS and no such implied order within the list remains. The error from a data gathering/stewardship perspective is that there is an important characteristic of each road that was not explicitly recorded, but instead was incorporated in an implied way that depends on the initial storage format and gets lost in other settings.

mfd_id should be thought of nothing but a collection of random numbers with no special characteristics other than being non-NULL and being unique, more part of the internal machinery of the software than an attribute of the data.

tjhb

8,883 post(s)
online
#01-Oct-19 07:48

It would be illusory to want to keep track of order in source values of mfd_id, yes.

But wanting to keep track of the source value of mfd_id is not illusory at all. That is a useful reference to the original object.

For example, let's say we split a line into its segments. We process and adjust the segments, remove some, replace some, bend some others.

Eventually we may want to replace the original line. For that we need to know its mfd_id, so that we can update the source table.

The source mfd_id in that case has nothing to do with order or sequence, it's just a unique reference, that lets us get back to the data we started from. That is often essential.

[Added] We do need to be sure that no one else and nothing else can have messed with the source data in the meantime, yes. But that is usually the case, and if it is unexpectedly not the case, then not being able to reuse the source mfd_id is the least of our problems.

Dimitri


5,552 post(s)
#01-Oct-19 12:50

That's true, and I agree. For that it is handy to have a unique ID. I do that myself when I want a "super undo", beginning first by copying a project, working with the copy of the project, and keeping the original of the project with all the objects in their original form.

You've correctly pointed out that only goes so far. I agree with that as well, which is why for version control you'd want some other way of identifying and tracking objects through changes.

Example: Suppose I want some copies of an object in different places in my drawing. One way to do that is to select and copy the object. I now have a copy on the clipboard. Using Transform with the "selected" checkbox checked, I move the selected object to a new location. De-select, and then paste, and in addition to the "original" object at the new position, you have a new object back at the original position. You could move that too, and then paste again (still have the "original" on the clipboard), to put the object back in the original position.

But... whenever that object is pasted back into the original position it has a different mfd_id value, even though in all particulars other than the mfd_id it is the "original" object.

adamw


8,696 post(s)
#01-Oct-19 14:17

Tim said it all, the MFD_ID field is special (in all tables stored in MAP files, in temporary cache tables, etc), it is required to be unique and non-NULL.

Query 2 fails because you attempt to insert records with duplicate values of the MFD_ID field into the same table. Remove the MFD_ID field from the SELECT list or add an alias to it.

Query 3 fails because it contains a syntax error: LEFT JOIN requires ON. If you change WHERE to ON, the query will run and fail later - for the same reason as query 2.

gpBike1001
20 post(s)
#01-Oct-19 17:20

Well that worked easily enough (dropping the mfd_id field). Thanks for the guidance.

Then in the new table go to the Schema dialog, click on the Add Identity star to generate a new mfd_id field and index, and that's it!

Manifold User Community Use Agreement Copyright (C) 2007-2017 Manifold Software Limited. All rights reserved.