Note how the original code is added as a comment - so in case something went wrong in the conversion, it will be easier for you to figure out what the correct result should have been.
Since not everyone likes their code cluttered with these ORIGSQL comments, they can be switched off through an Exodus configuration setting. For brevity I'll be omitting these comments in the examples from now.
Let's try something more interesting. The following PL/SQL prints the number of rows in a table:
DECLARE cnt NUMBER;
select Count(*) into cnt from mytable;
DBMS_OUTPUT.PUT_LINE('#rows in table: '||cnt);
This is converted to the following ASE T-SQL code:
DECLARE @cnt INT
DECLARE @DBMTK_TMPVAR_STRING_1 VARCHAR(16384)
@cnt = COUNT(*)
SET @DBMTK_TMPVAR_STRING_1 = '#rows in table: '||CONVERT(VARCHAR(100),@cnt)
There are various things worth noting here:
First, note how the PL/SQL SELECT-INTO statement is converted to the corresponding ASE syntax which selects a value into a variable (ASE's own SELECT-INTO has entirely different semantics).
In case we'd convert to IQ or SQL Anywhere, the SELECT-INTO would be retained as Watcom SQL supports this syntax too.
Second, the original expression in the PUT_LINE() statement is actually an expression where a string is concatenated with an integer. In ASE, it is not allowed to specify expressions as arguments to the PRINT statement, so Exodus takes the expression out and assigns it to a help variable which is then specified as the argument to PRINT.
(NB: for converting stored procedure calls to ASE, the same approach is used)
Third, Exodus knows that the cnt variable is an integer and it converts it to a string before concatenating it. Unlike PL/SQL, ASE's T-SQL does not support automatic conversion to string datatypes in such expressions, so leaving this expression unchanged would have cuased a syntax error in ASE.
And just in case you'd ask: if the concatenated variable had been declared as a string itself, Exodus would not generate the CONVERT() call.
Lastly, as the converted SELECT-INTO statement shows, Exodus tries to format the generated SQL code nicely and in a standarized manner. If the formatting of the input SQL code is messy, the result will look better.
Finally, the following could well occur in a PL/SQL application:
For those not familiar with PL/SQL, the INITCAP() built-in function capitalizes the first letter of every word in a string. Since none of the SAP databases provide such a built-in function, Exodus comes with a library of so-called "run-time components" which are SQL stored procedures or SQL functions that implement such functionality.
Exodus converts the above statement to ASE as follows:
SET @DBMTK_TMPVAR_STRING_1 = dbo.sp_f_dbmtk_capitalize_word('hello world!')
The SQL function sp_f_dbmtk_capitalize_word() gives the same result as PL/SQL's INITCAP().
When executed, the result is as expected:
Obviously, much of this is pretty basic stuff. In fact, Exodus goes far beyond these elementary conversion requirements.