Re: Accessing Remote Database using SQL - requesting an SQL Package?

On 29-Jul-2014 15:35 -0500, Scott Mildenberger wrote:

KEY is also a reserved word, don't know if that could figure into
it. If it mattered Chuck probably would have mentioned :) Maybe try
with a different field name.

I had actually not bothered to mention that, because the references shown, should not be an issue. While the undelimited token _KEY_ is a reserved SQL identifier, the SQL should be able to determine the validity, contextually; the references to that name both as the column named KEY [just about anywhere I can imagine] and as a host variable named KEY [conspicuous to the SQL, per the colon ':' that precedes the token] should not be origin for an error. FWiW, AFaIK, the undelimited column name KEY should only ever be an issue, if the limited contexts of the word KEY being reserved, ever is expanded beyond the use in DDL CONSTRAINT definition clauses; I believe those are the only supported references currently.

Even so, I suppose, best to ensure any reserved identifier could not cause any problems, now, or in the future.

An earlier message in this thread had coded :Source_Key [instead of :Key]. That is probably a better choice for the :HV name, and the column reference in the equal predicate of the WHERE clause probably is best delimited with the double-quote character to eliminate any [future] chance that the value could be misinterpreted as a keyword instead of properly recognized as a column name.

The following would be unambiguous for the SQL, for both _parsing_ and _syntax checking_ [though whatever was the data type\size _validation_ issue with the :HV definition that was diagnosed as -312 aka msg SQL0312, may persist regardless of the following change to eliminate reserved identifiers from the statement]:

exec sql insert into local_machine.needles.test
(select *
from remote_machine.needles.test
where "KEY" = :source_key ) ;