[QUOTE=Artur Oliveira;36935]Hi,
please, send me your table and application for we test directly in your application.
Best regard
Netmake team[/QUOTE]
Thanks for your feed back.
I have sent you applications with (mysql) database a couple of times about this in recent years. You never replied.
I now have to report the problem is even worse.
Now I find that when I enter the lookup SQL in a SELECT field
SELECT DISTINCT AllocSectionIndex.ID, AllocRows.Name
FROM AllocSectionIndex INNER JOIN AllocRows ON AllocSectionIndex.ROW = AllocRows.ID
WHERE AllocSectionIndex.AREA = {AreaID}
AND AllocSectionIndex.PORTION = {PortionID}
ORDER BY AllocRows.Name
The above works in SC’s SQL Builder, provided I change the field variable to a literal. It fails at runtime.
I get a compile warning and runtime error. Examining the SQL in the runtime error dialog I see it has been corrupted:
SELECT DISTINCT AllocSectionIndex.ID, AllocRows.NameFROM AllocSectionIndex INNER JOIN AllocRows ON AllocSectionIndex.ROW = AllocRows.ID WHERE (AllocSectionIndex.AREA = ) AND [B].portion[/B] = {[B]portioni[/B]d ORDER BY AllocRows.Name
Note how the field name “AllocSectionIndex.PORTION” has been changed to .portioni and {PortionID} has been conformed to lower case? Now, that may be SC trying to normalize names, but it generates errors.
Using field delimiters fieldname
in mysql does not help.
Aliasing field names works sometimes, but not always.
Changing field & table names in the database can help, especially when names are made shorter and DO NOT start with ‘ALL’ - So, generally I come up against this problem when dealing with table names like Allocations
or AllocAreas
. Please note that aliasing the table names does not help. For example "Allocations
AS temptbl
" usually is corrupted by SC as "ocations
AS temptbl
"
Sadly in this case I am working with an established database (mysql) designed & owned by another organisation. I am not allowed to change the table names and, again. trying to alias my way out of trouble does not work.