Subscribe to this thread
Home - General / All posts - SELECT INTO reports Unkown name
KlausDE
6,054 post(s)
#04-Dec-17 15:20

When I turn a query running successfully in MF..163.11 into a SELECT INTO query it bails out reporting an existing field name as 'Unknown name'

The name start with 'o_ ..' as the text field was created by an Overlay Topology Transform. However I think that shouldn't make a difference and aliasing doesn't change anything.

adamw


7,307 post(s)
#04-Dec-17 15:27

Could you post example data + query?

PS: Could this be a wrong position of INTO? Should be SELECT ... INTO ... FROM ... [WHERE, etc], a common mistake is to try doing SELECT ... FROM ... INTO ... [WHERE and others].

KlausDE
6,054 post(s)
#04-Dec-17 15:44

I have found the cause of the failure in an ORDER BY clause starting with this field. Commenting out this clause creates the result table.

[There was a thread about restriction coming with ORDER BY but I can't find it.]

tjhb

7,545 post(s)
#04-Dec-17 22:29

This looks like a bug.

This fails

SELECT *

INTO [test]

FROM [mfd_meta]

ORDER BY [mfd_id]

;

with error

'mfd_id': Unknown name.

This succeeds prima facie

SELECT *

INTO [test]

FROM

  (

  SELECT *

  FROM [mfd_meta]

  ORDER BY [mfd_id] DESC

  )

;

but the order imposed in the inner SELECT is ignored (as perhaps it should be).

The same is true for

SELECT *

INTO [test]

FROM

  (

  SELECT

  SPLIT

    (COLLECT [mfd_id]

    ORDER BY [mfd_id] DESC

    )

  FROM [mfd_meta]

  )

;

Here the order from COLLECT is likewise ignored by the outer node.

tjhb

7,545 post(s)
#04-Dec-17 22:42

There is an argument from strict SQL principles that SELECT... INTO... ORDER BY... makes no sense, because static tables are inherently unordered. Only a view (a virtual table) can really be ordered.

KlausDE
6,054 post(s)
#05-Dec-17 06:26

A workaround for this restriction useful in many situation when an ordered and usually summed up result needs to be taken over in some final report - say as a table in a Word document - would be a copy/paste snapshot of the actually displayed query result into the clipboard in the excange format(s) of Excel.

I usually do not want to transfer the SQL code but the results and I can only select a single cell of the result table as source for clipboard interaction and not records nor the complete (visible?) result table.

But this is another story.

adamw


7,307 post(s)
#05-Dec-17 06:42

Regarding selection - we should perhaps allow selecting either all or no records in a table that does not have a unique index. (Without such an index we can't tell records from each other, but that's not necessary for selecting all or no records.)

KlausDE
6,054 post(s)
#05-Dec-17 06:46

... and the left cell in headings is the control known to operate this complete selection in Excel.

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