Shulou(Shulou.com)05/31 Report--

This article focuses on "how to use Oracle aliases". Friends who are interested may wish to take a look. The method introduced in this paper is simple, fast and practical. Now let the editor take you to learn how to use Oracle aliases.

Today, when writing a simple SQL statement and executing it, an error message is thrown as shown in the following figure!

Well, this exception message is obvious. The length of the marker in the SQL statement is too long, and it should be OK to be shorter. I checked my SQL statement and found that the column alias of my query field is too long. The actual test result is that the maximum length of column field alias is 30 bytes!

Note: my test database is ORACLE 11.2.0.4.0 (shown in the following figure)

Ok, the problem is solved. For more error messages and solutions, you can also query from ORACLE's error message manual such as (for this problem):

Error:

ORA-00972: identifier is too long

Cause:

You tried to reference a table, cluster, view, index, synonym, tablespace, or username with a value that was longer than 30 characters.

Action:

The options to resolve this Oracle error are:

Names for tables, clusters, views, indexes, synonyms, tablespaces, and usernames must be 30 characters or less. You must shorten the name to no more than 30 characters for these objects.

The above is the process of analyzing and solving the problem of too long aliases in my SQL statements, and it is also the introduction for me to share this small blog post!

Here is a summary of my aliases (Note: my test database is ORACLE 11.2.0.4.0 character set is AMERICAN_AMERICA.AL32UTF8)

One: the benefits of using aliases

1) simplify the writing of SQL statements, especially when multiple tables are associated with queries and the table name is long

2) enhance the readability of SQL statements, especially when the query column fields are more complex.

Second: test summary

1) simple table structure used in testing

CREATE TABLE SALES.STUDENT (ID NUMBER, NAME VARCHAR2 (20 BYTE), SEX CHAR (1 BYTE), BIRTHDAY DATE, HOBBY VARCHAR2 (20 CHAR))

2) several pieces of test data used in the test

3) the maximum length of the column name-30 bytes

SELECT ID ABCDEABCDEABCDEABCDEABCDEABCDE--30 English capital characters, normal execution of FROM STUDENTSELECT ID ABCDEABCDEABCDEABCDEABCDEABCDE1-- plus another 1 will report the good student number of ORA-00972:identifier is to longFROM STUDENTSELECT ID our school-10 Chinese characters, normal implementation of FROM STUDENTSELECT ID good student number of our school 1 Murray-add another 1 will report ORA-00972:identifier is to longFROM STUDENT

4) whether the AS keyword can be used-you can use the

SELECT ID AS student number-- in the case of using AS, normal execution of FROM STUDENTSELECT ID student number-- can be executed normally even if it is not applicable to AS, and the code is more concise. It is recommended to use FROM STUDENT.

5) can you start with a number-you can start with a number but must be enclosed in double quotation marks

SELECT ID 123student number-- Executive Times ORA-00923: FROM keyword not found where expected "FROM STUDENTSELECT ID" 123student number-- normal execution

6) the difference between double quotation marks, single quotation marks and no use of quotation marks in Chinese aliases (note: they are all English single or double quotation marks).

SELECT ID student number-- normal execution FROM STUDENTSELECT ID "student number"-- normal execution FROM STUDENTSELECT ID 'student ID'-- Executive Times "ORA-00923: FROM keyword not found where expected" FROM STUDENTSELECT ID StudentId-- normal execution FROM STUDENTSELECT ID "StudentId"-normal execution FROM STUDENTSELECT ID 'StudentId'-- Executive Times "ORA-00923: FROM keyword not found where expected" FROM STUDENT

7) whether other special symbols such as parentheses can be used but must be enclosed in double quotation marks (Note: other special symbols may be similar. Here we take parentheses as an example. If you are interested, you can try it yourself.)

SELECT ID AS (student number)-in the case of English parentheses and using AS, execute Times "ORA-00923: FROM keyword not found where expected" FROM STUDENTSELECT ID (student number)-English parentheses, executive Times "ORA-00904:" ID ": invalid identifier" FROM STUDENTSELECT ID AS "(student number)"-normal implementation of FROM STUDENTSELECT ID AS (student number)-Chinese parentheses and use AS Executive Times "ORA-00923: FROM keyword not found where expected" FROM STUDENTSELECT ID (student number)-Chinese parentheses, Executive Times "ORA-00904:" ID ": invalid identifier" FROM STUDENTSELECT ID "(student number)"-- normal implementation of FROM STUDENT

8) can spaces in aliases be preserved-yes, but must be enclosed in double quotation marks

SELECT ID academic number-- Executive Times "ORA-00923: FROM keyword not found where expected" FROM STUDENTSELECT ID "academic number"-- normal implementation of FROM STUDENTSELECT ID XUE HAO-- Executive Times "ORA-00923: FROM keyword not found where expected" FROM STUDENTSELECT ID "XUE HAO"-- normal implementation of FROM STUDENT

9) problems caused by the execution order of clauses in SQL statements when using aliases

SQL statement in ORACLE

Fields or constants can only be used directly in the 1.WHERE/GROUP BY/HAVING clause, but not the alias of the field, unless the alias comes from a subquery, such as: SELECT. FROM (SELECT COLUMN_NAME C FROM TABLE_NAME) WHERE C > 1

two。 ORDER BY can use aliases directly, such as SELECT COLUMN_NAME C FROM TABLE_NAME ORDER BY C.

This is related to the order in which SQL is executed, and the order in which SQL statements are executed is roughly as follows:

1. FROM statement

2. WHERE statement (with conditions)

3. START WITH statement

4. CONNECT BY statement

5. WHERE statement

6. GROUP BY statement

7. HAVING statement

8. MODEL statement

9. SELECT statement

10. Set calculus of UNION, MINUS, INTERSECT, etc.

11. ORDER BY statement

We can see that the SELECT clause is executed after the WHERE clause is executed, and the alias of the query column is generated when the SELECT clause is executed, so you can't see the alias of the column at all in the WHERE clause, and of course, you can't reference the alias of the column. So aliases for fields and expressions cannot be used in the where clause and GROUP BY clause, but not only aliases can be used in ORDER BY, but you can even sort them directly using the subscript of the field, such as ORDER BY ID or ORDER BY 1.

SELECT ID ID_ FROM STUDENT WHERE ID_=11-- Executive Times "ORA-00904:" ID_ ": invalid identifier" SELECT ID ID_ FROM STUDENT WHERE ID=11-- normal execution SELECT ID ID_ FROM STUDENT WHERE ID='11'-- normal execution, Note: ID is NUMBER type data SELECT ID ID_ FROM STUDENT WHERE ID= "11"-- Executive Times "ORA-00904:" 11 ": invalid identifier" Note: ID is data of type NUMBER SELECT ID,COUNT (*) CFROM STUDENTGROUP BY ID HAVING C > 0Mui-Executive Times "ORA-00904:" C ": invalid identifier" SELECT ID,COUNT (*) CFROM STUDENTGROUP BY ID HAVING Count (*) > 0Murray-normal execution SELECT ID,COUNT (*) CFROM STUDENTGROUP BY ID HAVING COUNT (*) > 0ORDER BY Cmure-normal execution SELECT ID ID_FROM STUDENTORDER BY ID_-- normal execution SELECT ID ID_FROM STUDENTORDER BY 1Muk-normal execution

10) whether aliases are case-sensitive-case-sensitive (note: English characters are not case-sensitive when not enclosed in double quotes)

SELECT XUEHAOFROM (SELECT ID XUEHAOFROM STUDENT) WHERE XUEHAO > 1 Mustang-normal execution SELECT XUEHAOFROM (SELECT ID xuehao FROM STUDENT) WHERE XUEHAO > 1 Murray-normal execution SELECT XUEHAOFROM (SELECT ID "XUEHAO" FROM STUDENT) WHERE XUEHAO > 1 Murray-normal execution SELECT XUEHAOFROM (SELECT ID "xuehao" FROM STUDENT) WHERE XUEHAO > 1 Mustang ORA-00904: "XUEHAO": invalid identifier "SELECT XUEHAOFROM (SELECT ID" xuehao "FROM STUDENT) WHERE xuehao > 1ft-Enforcement Times ORA-00904: "XUEHAO": invalid identifier "SELECT XUEHAOFROM (SELECT ID" xuehao "FROM STUDENT) WHERE" xuehao "> 1Murray-Executive Times" ORA-00904: "XUEHAO": invalid identifier "SELECT" xuehao "FROM (SELECT ID" xuehao "FROM STUDENT) WHERE" xuehao "> 1Mui-normal execution

In this small example, you can also see that the SQL statement executes the WHERE clause before the SELECT clause!

At this point, I believe you have a deeper understanding of "how to use Oracle aliases". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.