>>>Hi,
>>>
>>>I have a situation where in the SQL Select, the JOIN should have a table (MyTable2) from another SQL Server database. Here is an example:
>>>
>>>local cJoinSqlServer
>>>cJoinSqlServer = "SQL2Name.dbo."
>>>cSqlSelect = "select * from mytable join " + cJoinSqlServer + "MyTable2 on MyTable.pk_field = MyTable2.pk_field"
>>>
>>>The above syntax works.
>>>And then I tried to add the cJoinSqlServer to the ON part as follows:
>>>
>>>local cJoinSqlServer
>>>cJoinSqlServer = "SQL2Name.dbo."
>>>cSqlSelect = "select * from mytable join " + cJoinSqlServer + "MyTable2 on MyTable.pk_field = " + cJoinSqlServer + "MyTable2.pk_field"
>>>
>>>
>>>The above works too; no error. But do I really need to add this prefix (of the second SQL Server DB) to the ON part?
>>
>>KISS and keep it clear IMHO, yours is not, whether you use the full dbname.schema.tablename or not:
>>
>>cJoinSqlDatabase = "SQL2Name.dbo."
>>
>>TEXT TO cSqlSelect TEXTMERGE NOSHOW PRETEXT 15
>> Select t1.fieldName1, ..., t1.fieldNameN, t2.fieldName1, ..., t2.fieldNameN, t3.fieldName1, ..., t3.fieldNameN
>> FROM myTable t1
>> Join << m.cJoinSqlDatabase >>.MyTable2 t2 on t1.pk_field = t2.pk_field
>> join << m.cJoinSqlDatabase >>.MyTable3 t3 on t2.pk_field = t3.pk_field
>>ENDTEXT
>>
>>PS: Do not forget to rename fields which have the same name (ie: pk_field).
>
>I just looked into changing my SQL Select to use aliases and realized why I didn't use them before.
>My SQL Select has all selected fields with table name aliases. And I would have to change all these aliases with T1 or T2. And I am concerned that I will not change the alias in one place and the code will break.
So are you saying you are instead going to take the longer path and alias them all with their databases as well? Like:
< m.cJoiningDatabase >..myTable3.myField??? I thought t3.myfield would be easier.