I'm hoping someone can help with this question. This probably has a simple answer but it seems to have me stumped. If I use t.* and b.*, the query runs properly. Similarly, if I specify fields for both tables, all is well. The problem starts when I combine t.* with specified fields for b. Given the number of fields in the first table and the fact that they may change over time, I would prefer not to have to specify them all, but is this a Manifold SQL limitation and, if so is there a workaround?

SELECT

t.*, b.[Field 1], b.[Field 2], b.[Field 3] INTO [ResultTable] FROM [Table1] AS t LEFT JOIN [Table2] AS b ON t.[GEOID] = b.[GEOID];

Elio Spinello RPM Consulting, LLC Santa Clarita, CA

are you using M9? with mfd_id column in [t]? then the problem is the index on mfd_id force it to be unique and your join on b might duplicate some of the lines

the solutions :

call each variable but mfd_id or divide it by 1, instead of *

remove the mdf_id column if not needed

remove the index on mfd_id

Sorry, yes it's M9, and both tables have mfd_id.

Removing mfd_id and/or the index in either or both tables doesn't seem to change anything. As soon as I substitute the * for one or more variables, the error is returned that the first column in the b table is an "unknown name". Removing the variable the from the table then results in the next variable in line being "unknown". The issue seems to be combining * in one table with specified variables in the second.

Elio Spinello RPM Consulting, LLC Santa Clarita, CA

I attempted to reproduce the reported issue and encountered no problems. However, I was able to generate the same error message by using a query where the field name spelling did not match the table definition. This discrepancy is likely the root cause of your issue.

Map Publishing Platform - Next Generation

could you submit us the SQL definition of your two tables? you can copy the table and paste it in the console to have its sql definition

Ok, problem solved. The issue wasn't the name of the column, it was a typo in the table name that coincidentally matched a different layer in the project that did not have that particular field. I was so focused on the error message pointing toward the field name that I missed the fact that it was looking at the wrong table. Lesson learned, and thank you for the suggestions!

Elio Spinello RPM Consulting, LLC Santa Clarita, CA

There's an item in the upcoming build (currently set for 26 July, might be issued sooner) that will help in discussions of table schemas. If you've always found it annoying that you couldn't just open the Edit - Schema dialog and copy rows in it, for your notes, for a discussion, whatever, this is for you:

A Copy button has been added to the Schema dialog's toolbar. Either the button or Ctrl-C copies all selected rows that are visible (ignoring any selected rows that are hidden as a result of text matching in the filter box) to the Clipboard. Pasting from that copy works like paste of rows copied from a table window: The first row that is pasted provides five field names - Category, Name, Type, Expression, and Attributes - that are separated by tabs. Subsequent rows provide values from the Schema dialog also separated by tabs. Note that in the Schema dialog the first field, Category, is indicated by an icon (field, computed field, index, constraint) in the dialog. The last field, Attribute, likewise is indicated by one or more icons in the Schema dialog (Indexed, Indexed (secondary), Projection, Projection (Invalid), Key). Multiple attributes within the same field are separated by a comma followed by a space.

Excellent, thank you! I often keep a OneNote notes page open to document projects as I develop them. Having that will be really helpful.

Elio Spinello RPM Consulting, LLC Santa Clarita, CA