Difference between revisions of "Portable SQL/Common/Aliases"
m (OpenDBX Portable SQL moved to Portable SQL - Aliases: content split) |
(content split) |
||
| Line 43: | Line 43: | ||
This also works if other applications will access the tables which aren't using the OpenDBX library and don't adjust the connections to ANSI mode if necessary. | This also works if other applications will access the tables which aren't using the OpenDBX library and don't adjust the connections to ANSI mode if necessary. | ||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
---- | ---- | ||
Back to [[OpenDBX|Overview]] | Back to [[OpenDBX|Overview]] | ||
Revision as of 00:47, 26 April 2007
Contents
Using aliases
Aliasing tables
Don't use the keyword "AS" between the table name and alias as no DBMS requires it and some don't recognize it. Instead, always write:
SELECT * FROM table t WHERE t.id = 0
Aliasing columns
Contrary to "Aliasing tables" the keyword "AS" between column name and alias is required by most database servers and should always be present:
SELECT count(*) AS cnt FROM table
Aliases for computed columns
The naming of computed columns implemented by the database servers is very different. Some name it like the computational expression ('count(*)'), most often in upper case and sometimes it isn't named at all. Therefore computed columns should be always given a name:
SELECT MAX(id) AS maxid FROM table
Quoting in statements
Quoting identifiers
Looking for good identifiers for tables, columns or views can often be a serious pain when your application should work with different database servers. There are a lot of reserved words which can't be used and each database vendor constantly extends his list. How can an application developer be sure that there will be no name clashes in the future? Similarly, databases tend to change the character case of identifiers. Some of them convert all to upper case, some to lower case while many preserve the case. If you want to access the value of a column by using the name instead of the position, things get really tricky.
To avoid both situations, the OpenDBX library forces all databases to operate in ANSI standard mode. Thus, they all support double quotes (") for quoting identifiers, e.g.
CREATE TABLE "order" ( "id" INTEGER, "limit" DECIMAL(10,2) ) SELECT o."limit" FROM "order" o WHERE o."id" = 1
Quoted identifiers can't clash anymore with reserved words (like "order" with ORDER BY) when they are quoted and they always keep their case. The disadvantage is that using them unquoted won't work any more in most cases.
The second possibility is to use unquoted names which are unlikely to get in conflict with reserved SQL words like so_limit
CREATE TABLE shop_order ( so_id INTEGER, so_limit DECIMAL(10,2) ) SELECT so.so_limit FROM shop_order so WHERE so.so_id = 1
This also works if other applications will access the tables which aren't using the OpenDBX library and don't adjust the connections to ANSI mode if necessary.
Back to Overview
