Документ взят из кэша поисковой машины. Адрес оригинального документа : http://www.stsci.edu/spst/lrpg/documentation/procedures/lrpg_sql_to_isql_conversion.html
Дата изменения: Fri Apr 2 23:38:37 2004
Дата индексирования: Sun Mar 2 12:26:59 2014
Кодировка:
Поисковые слова: t tauri

Converting Smartstar sql to Transact isql has a number of components.

Trailing semicolons ";" must be stripped off.
Temporary table names must be changed. This involves stripping off the double quotes surrounding the temporary table name.
Variable names which start with a colon ":" must be converted to at-signs "@".
"let" statement words must be changed to "select".
"allocate" statements must be changed to "declare".

There is a sed script, /home/lrp/isql/convert_sql_2isql which performs these conversions. Input is the file name. Output is standard output which can be directed to a file. It is possible that colons and perhaps other characters may be incorrectly converted, so the output should be reviewed with this in mind. It has also been noted that the script may miss making some replacements, particularly replacing the ":" with "@" if the colon occurs right after a parenthesis.

There are other important conversions for which no script currently performs such conversions.

function name/example	VMS sql format	UNIX isql format
substring	substring(6,2,sunit_id)	substring(sunit_id,6,2)
int4/convert	int4(substring(2,4,sunit_id))	convert(int,(substring(sunit_id,2,4)))
float4/convert	float4(a1.longest_al)	convert(float,a1.longest_al)
concatenation of strings.	concat(tmp.tar_type,"G")	tmp.tar_type + "G"
outerjoins	see notes below	see notes below
strip double quotes surrounding secondary database name	"spss..qsbranching"	spss..qsbranching

Another important change which must be made is removal of if/endif blocks. This may entail code redesign which removes logic from the sql and places it in the controlling code.

"using" statements must be converted into "from" statements. This requires changing 'using' into 'from', AND moving the statement downward so that it follows the 'set' statements, but before the 'where' clause. The 'from' statements must also contain the name of the table which is being updated.

An exception to the above is a 'using' clause in a 'delete' statement. If the delete transaction uses only one table, the 'using' clause may be omitted entirely with no 'from' clause replacing it.

Additionally, Transact isql appears to be less forgiving of uncompleted joins. Significant performance degradation has been seen in isql when joins between key fields are not propagated between all tables. While a join may not be completely equivalenced across all key fields in Smartstar and still perform quickly, it can be markedly slower unless the joins are completed.

Outerjoins are handled quite differently between the two languages. In Smartstar outerjoins are performed by placing "(outerjoin)" after the table name in the 'from' clause. However, no such syntax is used in Transact isql. Instead, the table name in the from clause is not distinguished from the others, and "*=" or "=*" is used in each table join statement within the where clause. Which is used depends upon whether the outerjoined table is typed first ("=*") or second ("*=").

"\include" statements in Smartstar SQL must be evaluated and replaced with an alternate structure.