Table Alias
Is there any way to get the MagicSQL that RB generates to always generate a
unique table alias for tables that are linked multiple time in a query?
I'm working on moving a RB10 based application from DBISAM to ElevateDB.
Unfortunately, the queries generated by RB with a table used multiple times
in a query result in a duplicate table or alias name error in EDB because it
only creates a unique alias for the second instance of a table. I've
already contacted the developers of EDB and they indicate that this is
working as expected, so I need to look at the RB side of things.
If I create a query using the query builder to join a table to another table
twice, it generates a query similar to the following:
select * from items items
join parts parts on items.partno = parts.partno
join parts parts2 on items.partno = parts2.partno
In this case, it creates a unique alias for the second instance of the parts
table, but leaves the table alias the same as the table name for the first
instance. For EDB, the table names and correlation names all must be
unique.
For this query to work in EDB, the table alias for the first instance of the
parts table needs to be changed to something unique across all of the
correlation and table names to work:
select * from items items
join parts parts1 on items.partno = parts1.partno
join parts parts2 on items.partno = parts2.partno
I'm thinking that this would be best done at the MagicSQL generation level
as it would not require that existing templates be reworked and it wouldn't
require any changes to the way that the query builder initially adds the
linked tables to the query when designing new reports.
Any suggestions would be greatly appreciated.
--
---------------------------------------
Terry Swiers
Millennium Software, LLC
http://www.1000years.com
http://www.atrex.com
Atrex Inventory Control/POS -
Big business features without spending big business bucks!
Atrex Electronic Support Options:
Atrex Knowledgebase: http://www.atrex.com/atrexkb.asp
Email: mailto:support@atrex.com
unique table alias for tables that are linked multiple time in a query?
I'm working on moving a RB10 based application from DBISAM to ElevateDB.
Unfortunately, the queries generated by RB with a table used multiple times
in a query result in a duplicate table or alias name error in EDB because it
only creates a unique alias for the second instance of a table. I've
already contacted the developers of EDB and they indicate that this is
working as expected, so I need to look at the RB side of things.
If I create a query using the query builder to join a table to another table
twice, it generates a query similar to the following:
select * from items items
join parts parts on items.partno = parts.partno
join parts parts2 on items.partno = parts2.partno
In this case, it creates a unique alias for the second instance of the parts
table, but leaves the table alias the same as the table name for the first
instance. For EDB, the table names and correlation names all must be
unique.
For this query to work in EDB, the table alias for the first instance of the
parts table needs to be changed to something unique across all of the
correlation and table names to work:
select * from items items
join parts parts1 on items.partno = parts1.partno
join parts parts2 on items.partno = parts2.partno
I'm thinking that this would be best done at the MagicSQL generation level
as it would not require that existing templates be reworked and it wouldn't
require any changes to the way that the query builder initially adds the
linked tables to the query when designing new reports.
Any suggestions would be greatly appreciated.
--
---------------------------------------
Terry Swiers
Millennium Software, LLC
http://www.1000years.com
http://www.atrex.com
Atrex Inventory Control/POS -
Big business features without spending big business bucks!
Atrex Electronic Support Options:
Atrex Knowledgebase: http://www.atrex.com/atrexkb.asp
Email: mailto:support@atrex.com
This discussion has been closed.
Comments
--
---------------------------------------
Terry Swiers
Millennium Software, LLC
http://www.1000years.com
http://www.atrex.com
Atrex Inventory Control/POS -
Big business features without spending big business bucks!
Atrex Electronic Support Options:
Atrex Knowledgebase: http://www.atrex.com/atrexkb.asp
Email: mailto:support@atrex.com
On further investigation, modifying the MagicSQL may not be the way around
this as you can't even create or modify a query with EDB with links to the
same table twice.
--
---------------------------------------
Terry Swiers
Millennium Software, LLC
http://www.1000years.com
http://www.atrex.com
Atrex Inventory Control/POS -
Big business features without spending big business bucks!
Atrex Electronic Support Options:
Atrex Knowledgebase: http://www.atrex.com/atrexkb.asp
Email: mailto:support@atrex.com
This has been handled via email. The solution requires a code mod to daSQL.
--
Nard Moseley
Digital Metaphors
www.digital-metaphors.com
Best regards,
Nard Moseley
Digital Metaphors
www.digital-metaphors.com