SQL Standard – How to Reference Column Aliases in Projection

ANSI is a private non-profit organization that creates voluntary standards. As such it doesn’t actually regulate anything. Often it is to a company’s benefit to follow recognized standards, which is why many database companies follow the ANSI standard for SQL. Of course as each company seeks to differentiate their products, they will develop additional functionality beyond the standards.

From w3schools:

Although SQL is an ANSI (American National Standards Institute) standard, there are many different versions of the SQL language.

However, to be compliant with the ANSI standard, they all support at least the major commands (such as SELECT, UPDATE, DELETE, INSERT, WHERE) in a similar manner.

Note: Most of the SQL database programs also have their own proprietary extensions in addition to the SQL standard!

The differences between the SQL dialects is too great in the more specialized areas you listed to simplify the problem down to a “rule of thumb”. In these situations you probably should convert the entire statement for each database. By doing this each statement can be optimized for each platform taking advantage of the special features each database can provide. Writing SQL to lowest common denominator (ANSI) may simplify migration, but at the cost of performance.

SQL Dialects Reference gives some useful comparisons of the SQL variants. For more information I recommend searching for the specific dialects you are switching between like this:

   differences between mysql and oracle sql

The precision is the total number of decimal digits that can be stored on both sides of the decimal place. The scale is the total number of decimal digits to the right of the decimal point which can be less than or equal to the precision. Therefore the maximum value that can be stored in a DECIMAL(3,3) or a NUMERIC(3,3) is 0.999. The easiest way to find this out is to actually try it out.

Using mysql 5.5.20 the below returns 0.999:

SET @val = (SELECT CAST(1 AS DECIMAL(3,3)));
SELECT @val; 

Whereas the following returns 1:

SET @val = (SELECT CAST(1 AS DECIMAL(3,0)));
SELECT @val;

I hope this helps.

EDIT: I forgot to mention that on most platforms attempting to perform the above will result in some type of Arithmetic Overflow Exception. For instance the below statement running on Microsoft SQL Server 2008 will throw an error:

DECLARE @val DECIMAL(3,3);
SET @val = 1;
SELECT @val;