Subscribe to this thread
Home - General / All posts - SELECT... INTO query not working
Forest
578 post(s)
#04-May-18 07:47

M9 9.0.166.5

This query does not work

SELECT * INTO [SortedPhotos]

FROM [Sites_n_photos_20170825]

WHERE [photoSort] IS NOT NULL

ORDER BY [photoSort];

This query does work:

SELECT * INTO [SortedPhotos]

FROM [Sites_n_photos_20170825]

WHERE [photoSort] IS NOT NULL;

This query also works

SELECT * FROM [Sites_n_photos_20170825]

WHERE [photoSort] IS NOT NULL

ORDER BY [photoSort];

I have no idea why the first query fails.

Dimitri

5,036 post(s)
#04-May-18 09:18

There's no such thing as "order" in a table. See the discussion under How Records are Ordered for Display in the Tables topic. See also the discussion in the ORDER BY topic.

ORDER BY is only for viewing results. You can't use it in together with a SELECT ... INTO.

Also, please use the operators that exist: if you enter NULL into the filter box in the query builder you see there is a <value> IS NULL operator, but there isn't any <value> IS NOT NULL operator.

NOT is a different operator (you can see it by entering NOT into the filter box and expanding the resulting Operators section, to see NOT <value> You have to use SQL syntax if you want to string together operators.

So... if you want to construct a WHERE clause you would use

WHERE NOT ( [photoSort] IS NULL )

By the way, as a general rule of thumb when a query doesn't work as expected I recommend a careful reading of any topics that explicitly discuss the operators used. The documentation doesn't cover all possible questions, of course, and it is far from being as complete as, say, it might be in a year or so, but still, it's still often surprisingly helpful to the task at hand. Even if it isn't it often helps teach collateral knowledge that will be helpful in other settings.

ych616 post(s)
#04-May-18 11:13

To have the records ordered into a table would a combined query work ?

like:

SELECT * INTO [SortedPhotos] from (

SELECT * FROM [Sites_n_photos_20170825]

WHERE [photoSort] IS NOT NULL

ORDER BY [photoSort]

)

Dimitri

5,036 post(s)
#04-May-18 16:56

To have the records ordered into a table

There is no order in a table. Please read the topics I cited. Please read, for example, the ORDER BY topic to learn why.

Please also try to help out the quest for knowledge: if something you are asking about can be tried on a sample in about 30 seconds, please try it. Then you can help people out by reporting what you found here in the forum.

Forest
578 post(s)
#05-May-18 00:36

Thanks Dimitri for your efforts.

I think of databases like a bucket of marbles. Not matter how much you stir up the marbles (records), you will still have the same amount of information. The way the marbles are arranged is irrelevant.

However, if I want to export data to a text file, then order can be significant. I received a gdb from ArcGIS user that hot links to photos taken on a transect through a forest. Unfortunately, the table is in random order which means that when I click next photo in the photo view (QGIS eVis), the result is that the map bounces around all over the place. eVis has filters but does not support sorting and as far as I know, QGIS attribute tables can't be ordered in the way the record order can be saved for ms-access tables. To cure this issue, I attempted to use Manifold to create a new table where records were in photoSort order. That is my test use case.

I should add that the error message returned by Manifold ("invalid object reference") when I try to run a query that looks like a valid query does not help me one bit.

Dimitri

5,036 post(s)
#05-May-18 14:11

However, if I want to export data to a text file, then order can be significant. I received a gdb from ArcGIS user that hot links to photos taken on a transect through a forest. Unfortunately, the table is in random order which means that when I click next photo in the photo view (QGIS eVis), the result is that the map bounces around all over the place. eVis has filters but does not support sorting and as far as I know, QGIS attribute tables can't be ordered in the way the record order can be saved for ms-access tables. To cure this issue, I attempted to use Manifold to create a new table where records were in photoSort order. That is my test use case.

As Tim once said, "that's a lot to unpack."

If you have larger data then you shouldn't be keeping it in a text file. Do you want a human readable text report? OK. That's what report writing software is for. A good report writer will enable you to provide reports that are sorted on attributes. If you need some custom output from Manifold, that's easy to do with a script.

I received a gdb from ArcGIS user that hot links to photos taken on a transect through a forest.

It points are intended to be in some order, the usual case is that the author of the data provides a field in which some number gives the order. Usually, if that is the essence of the matter the author of the data is not going to leave something so essential to chance. For example, if a 43 sites must be visited in some specific order, a Site field might have an integer number from 1 to 43 that indicates their order. Makes sense, right?

Want to see a table in that defined order in Manifold? No problem. Ctrl-click on the Sites column header and, like magic, the table is sorted by that field.

If the software you use to browse photos cannot browse them in some order set by a field, that's unfortunate, but that's something to take up with the authors of that software.

One reason I like the idea of bringing data into Manifold as one-photo-per-record or one-binary-blob-per-record for things like audio files or videos (a suggestion I keep resubmitting...) is that once you have the data in Manifold it's easy to order it however you like, to arrange add-ins and scripts that can leverage that and so on.

But in such situations the solution is not to dumb down the database storage engine so that a side effect if it being primitive allows hacks to work that depend upon fixed order. The right solution is to leverage the power of the database in applications that depend upon sequence, for example, building GUI output choices like what photo pops up next based upon a query which orders data in the desired way using ORDER BY.

the record order can be saved for ms-access tables.

Access is a classic example of a seriously primitive database engine where the primitiveness of it sometimes has some useful side effects, so long as you are willing to live within the limits of the thing. But in such cases, well, sure it can be convenient to leverage a side effect of that primitiveness, such as tables physically storing records in a given order,.... right up until that moment when it is not convenient. Why is Access so limited in so many things and why is it unable to keep up even with the requirements of even very old applications like Outlook? Because the limitations of such primitive architecture very rapidly pile up into truly serious roadblocks.

To cure this issue, I attempted to use Manifold to create a new table where records were in photoSort order. That is my test use case.

It is not a "test case" if it is based on operating the package incorrectly. The very first topic on tables in the Basics chapter, Tables says in absolutely clear terms that tables do not have order. Setting up a test case that requires table to have order doesn't test anything about the software, it just demonstrates that failing to RTFM causes problems.

If your objective is to create some special data in a special form with Manifold, OK, that may be your use case. No problem. But you have to get there by working the software correctly, and specialty requirements may require the skills to leverage more sophisticated parts of the system, such as scripts.

----

If you want the software to do different things than what it does, that is perfectly OK and something to express. That's why there is a Suggestions process. There is absolutely nothing wrong and there is everything right with the notion that people might want the package to do something additional or to do things in a different way.

Experience shows that one of the best ways to accomplish that (not the only way, but one of the best ways) is to stand, as Newton put it, on the shoulders of giants when offering suggestions. You can see further and get a better view of the lay of the land standing on the shoulder of a giant. Those giants would be the many, many people who came before you in terms of offering their own insights and suggestions. In the case of something like 9, that includes the phenomenally powerful ideas evolved by very many people working over very many years with DBMS.

To take advantage of that, it is best to learn how to work the product first, and then only second offer suggestions where something doesn't make sense. It helps, of course, to get tips and insights from beginners on how to introduce the software to beginners, but that only goes so far. It is very rare that a beginner who doesn't understand how to work the software correctly stumbles onto a suggestion that has "legs," that is, has staying power and enduring value to people who know what they are doing with the thing.

It also helps to be able to take advantage of the full power of what is already there if you try to solve some interoperability problem. You might think "OK, sure, I get it why DBMS does what it does and why all this makes sense within Manifold. My problem is I need to use this brain-dead software over here which uses text files that must be in a particular order... I need to figure out how to write them out from Manifold."

The classic solution for such things is a) use a third party package that is a report writer and which can accept data in sensible formats, or b) write a script in Manifold to generate what you want. If you think such a thing could be a reasonable built-in that others might care about more than other priorities, by all means, write it up and send it it. Could be just a one-click or menu choice for a type of export.

I should add that the error message returned by Manifold ("invalid object reference") when I try to run a query that looks like a valid query does not help me one bit.

100% agree. Unfortunately, the reality is that coming up with a system which can sensibly explain what error has been made, and do that accurately, especially in situations where multiple errors are typically being made at the same time by people who have not yet learned SQL, is in many ways more difficult than writing a better query engine.

For now, it seems to make more sense to put a stronger focus on ensuring the query engine is totally reliable - never fails, never makes an error - and that it is totally articulated with a full set of big-time DBMS capabilities so it can keep up with connections to big-time DBMS systems and can support the demands of big-time GIS. A focus on error messages should be, first, on helping people who know what they are doing to find and fix simple mechanical errors such as typos, missing parens and so on. It's something Manifold will do.

ych616 post(s)
#05-May-18 09:24

[deleted]

Tested my query in Manifold Viewer 9 and effectively this would not work, although in MS-Access the query does work.

ych616 post(s)
#05-May-18 10:13

I tried this in Manifold 9 Viewer:

Source table

1/ first query : qry 1

result : target table unordered on field1

2/ second query: qry2

result : ordered records on field 1 in target table

Conclusion:

It is possible (qry2) to have ordered records in the target table if in the inner (SELECT ... FROM) query ORDER BY is run on the source table and the field(s) from the source table on which the ORDER BY is applied are explicitly named within the inner query ; but if the inner query is run with the " * " marker (qry1) we get ano ordered target table.

REMARK : I see that the target table obtained by the second query (qry2) is missing the mfd_id field , which on the contrary is created if qry1 is run.

This is the target table scheme run after qry1

This is the target table scheme run after qry2

ych616 post(s)
#05-May-18 10:42

editing :

... Conclusion:

It is possible (qry2) to have ordered records in the target table if in the inner (SELECT ... FROM) query ORDER BY is run on the source table and the field(s) from the source table on which the ORDER BY is applied are explicitly named within the inner query ; but if the inner query is run with the " * " marker (qry1) we get an UNORDERED target table.

Dimitri

5,036 post(s)
#05-May-18 12:33

It is possible (qry2) to have ordered records in the target table

No. The above is a mistaken conclusion. Please read the user manual topic I recommended so you can avoid making such mistakes.

Records in tables are NOT ordered. Thinking they are is a blunder which will lead to follow-on errors in workflow.

If any table gives the happenstance appearance of being ordered, it is a gross error to depend on that in any way. Query the table, change some other table, add or delete a component, or perhaps just open and close the project and the records could be in different order.

The above is true of Oracle, it is true of SQL Server, etc., and it is true of Manifold. All such systems provide superb tools for providing ordered views of data if that is what is desired.

adamw


8,139 post(s)
#05-May-18 15:20

Here is what happens in qry1:

The inner SELECT orders the table. The outer SELECT with INTO can write records into the table without any order. It might de-facto end up using the order in which the inner SELECT provides records, but this is never guaranteed and the order will absolutely differ if the outer SELECT is made more complex. The table stores the resulting records without any order either and is free to move them around, eg, to optimize access speed. The table window sees that the table is stored in the MAP file and outputs records in the order of the built-in MFD_ID field (this is, again, not guaranteed to happen, it is an implementation detail which we might change in the future, and even today it only applies to tables in MAP files).

Here is what happens in qry2:

The inner SELECT orders the table. The outer SELECT with INTO ends up writing records into the table in the order they are provided by the inner SELECT (not guaranteed to do this, it happens now because the number of records is small and because the inner SELECT is providing them fast). The table assigns each record a hidden MFD_ID value (not guaranteed to do this in the future and the algorithm even now is not strictly sequential, but looks sequential for small amounts of data). The table window displays records in the order of MFD_ID values (not guaranteed to do this in the future either, as mentioned).

Nearly every step above is not guaranteed to remain the same and many steps will actually reorder records even now, it is just that on small amounts of data and on simple cases everything clicks and it looks like the order is preserved.

Forest
578 post(s)
#08-May-18 00:58

Thanks Adam for the insights.

To report the solution, I exported the data from the ESRI gdb into a Geopackage. Using the dbmanager in QGIS, I created a query on which I can base a QGIS drawing. Didn't know this was possible. Still worth bringing up the topic and I learn from my mistakes.

adamw


8,139 post(s)
#08-May-18 09:17

The long and short of this is: if you need a specific order, tell us what where and what for. If you need to, say, export records in a specific order, we can add export options. If you need to render records in a specific order, we can add options for that as well. The main point is to not treat order as something that is a given, because many things work better if they do not have to preserve the original order, and instead establish or re-establish order where it is actually used.

tjhb

8,273 post(s)
#08-May-18 09:35

FWIW I think it would be helpful and worth the extra cost in time (if any), if the Export Result command available for a query result table were to write output using displayed order (that is, whenever order has been applied, either by ORDER in the query text (outer layer) or by Edit > Sort on a column in the result).

(I’m not sure about exports of static tables which have column sorting applied. In two minds. Keeping the order of an ordered query result would be strictly speaking enough, and useful in more cases.)

adamw


8,139 post(s)
#08-May-18 09:57

This is a good idea, thanks. If someone took the time to specify what the order should be by clicking on fields / invoking menu commands, perhaps that order is important enough to keep during the export, agree.

Although there is a clash with filters: should filters apply? They do not apply now, and we think they shouldn't, and so if filters don't apply, why should order apply... eh.

tjhb

8,273 post(s)
#08-May-18 10:00

Great Adam, I’m glad that made sense.

tjhb

8,273 post(s)
#08-May-18 10:19

I agree, filters should not apply. My best try at a reason is: they don’t need to.

If you want to export filtered data, export the result of a query (or a new static table).

Whereas, if for some reason you need or want an ordered export... then the export must be ordered.

adamw


8,139 post(s)
#05-May-18 15:00

Regarding ... IS NOT NULL - this is fine, we added specific support for this form and a couple of similar forms in addition to the regular NOT (... IS NULL), because other databases allow them and many users find them better flowing.

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