Oracle's alias Summary

Today, when you write a simple SQL statement and execute it, it throws an error message as shown!

Well, the exception information is obvious, the length of the identifier in the SQL statement is too long, short should be no problem, I looked at my SQL statement found to be my Query field column alias is too long, the actual test result is a column field alias maximum length of 30 bytes!

Note: My test database is Oracle 11.2.0.4.0 (as shown)

OK, the problem is solved, the corresponding more error messages and solutions can also be queried from the Oracle error information Manual for example (for this issue):

Error:
Ora-00972:identifier is too long
Cause:
You tried to reference a table, cluster, view, index, synonym, tablespace, or username with a value of was longer than 3 0 characters.
Action:
The options to resolve this Oracle error is:
Names for tables, clusters, views, indexes, synonyms, tablespaces, and usernames must be-characters or less. You must shorten the name to no more than and characters for these objects.

The above is my analysis and resolution of my SQL statements, query column alias too long the problem of the process, but also I share this little blog introduction! 
Here's a summary of my aliases (note: My test database is the Oracle 11.2.0.4.0 character set is American_america. Al32utf8)
One: Benefits of using aliases
1) Simplify the writing of SQL statements, especially when multiple table associated queries and table names are longer
2) Enhance the readability of SQL statements, especially when the column fields are more complex to query.

Second: Test summary
1) Simple table structure used in testing

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

2) test data used by several tests

3) Maximum length of column name--30 bytes

SELECTID ABCDEABCDEABCDEABCDEABCDEABCDE--30 English uppercase characters, normal execution fromSTUDENTSELECTID ABCDEABCDEABCDEABCDEABCDEABCDE1--add another 1 and it will be reported Ora-00972:identifier is to long fromSTUDENTSELECTID The good student number of our school--10 Chinese character characters, normal execution fromSTUDENTSELECTID The good student number of our school1--add another 1 and it will be reported Ora-00972:identifier is to long fromSTUDENT

4) If you can use the AS keyword-you can use

SELECT  as study number  - The normal execution from   STUDENTSELECT ID number when using as- -  Not applicable as can also be performed normally, and the code is more concise, the recommended way to use  from STUDENT

5) Whether you can start with a number--you can start with a number, but you must enclose it in double quotes.

SELECT ID 123 School Number - execution times "ora-00923:from keyword not found where expected" from STUDENT  Select ID "123 School Number"-- Normal execution

6) for Chinese alias double quotation marks, single quotes, not using quotation marks (note: All English single-or double-lead symbols)--using the single-draw symbol times wrong

SELECTID number--Normal Execution fromSTUDENTSELECTID "study Number"--Normal Execution fromSTUDENTSELECTId'School Number'--Executive times "ora-00923:from keyword not found where expected" fromSTUDENTSELECTID StudentID--Normal Execution fromSTUDENTSELECTID "StudentID"--Normal Execution fromSTUDENTSELECTId'StudentID'--Executive times "ora-00923:from keyword not found where expected" fromSTUDENT

7) Whether you can use other special symbols such as parentheses--can be used but must be enclosed in double quotation marks (note: The other special symbols may be similar, here is a small parenthesis as an example of testing, other situations if interested can try it yourself)

SELECTId as(School number)--English parenthesis and using as, execute the Times "ora-00923:from keyword not found where expected" fromSTUDENTSELECTID (School number)--English parenthesis, Executive Times "ORA-00904:" ID ": Invalid identifier" fromSTUDENTSELECTId as"(School Number)"--Normal Execution fromSTUDENTSELECTId as(School number)--Chinese parenthesis and using as, execute the Times "ora-00923:from keyword not found where expected" fromSTUDENTSELECTID (School number)--Chinese parenthesis, Executive times "ORA-00904:" ID ": Invalid identifier" fromSTUDENTSELECTID "(Study number)"--Normal Execution fromSTUDENT

8) Whether spaces in aliases can be preserved-yes, but must be enclosed in double quotes

 select  ID number --  executive times "Ora-00923:from keyword not found where expected"  from   STUDENT  --  normal execution  from   STUDENT  select  ID XUE hao--  executive Times "Ora-00923:from Keyword not found where expected " from   STUDENT  select  ID "XUE HAO" -- Span style= "color: #008080;" > normal execution  from  STUDENT 

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

SQL statements in Oracle,

The 1.where/group by/having clause can only use columns or constants directly, not the alias of a field, unless the alias comes from a subquery, such as: SELECT .... From (SELECT column_name C from table_name) WHERE C > 1
2. The 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 the SQL statements are executed is roughly as follows:

1. From statement

2. Where statement (binding condition)

3. START with statement

4. CONNECT by statement

5. Where statement

6. GROUP by statement

7. Having a statement

8. Model statement

9. SELECT statement

UNION, minus, intersect, etc. set calculus calculus

One. ORDER by statement

We can see that the SELECT clause executes after the WHERE clause is executed, and the alias of the query column is generated when the SELECT clause executes, so the alias of the column is not visible in the WHERE clause, and of course, the alias of the column cannot be referenced by nature. Therefore, aliases for fields and expressions are not available in the WHERE clause and the GROUP BY clause, and not only can aliases be used in order by, but they can even be sorted directly using the column's subscript, such as an order by ID or an order by 1

SELECTID id_ fromSTUDENTWHEREId_= One--Executive Times "ORA-00904:" id_ ": Invalid identifier"SELECTID id_ fromSTUDENTWHEREId= One--Normal ExecutionSELECTID id_ fromSTUDENTWHEREId=' One'--normal execution, note: ID is data of type numberSELECTID id_ fromSTUDENTWHEREId=" One"--Executive Times "ORA-00904:" one ": Invalid identifier", Note: ID is data of type numberSELECTIdCOUNT(*) C fromSTUDENTGROUP  byID havingC>0--Executive Times "ORA-00904:" C ": Invalid identifier"      SELECTIdCOUNT(*) C fromSTUDENTGROUP  byID having Count(*)>0--Normal ExecutionSELECTIdCOUNT(*) C fromSTUDENTGROUP  byID having COUNT(*)>0ORDER  byC--Normal ExecutionSELECTID id_ fromSTUDENTORDER  byId_--Normal ExecutionSELECTID id_ fromSTUDENTORDER  by 1--Normal Execution

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

SELECTXuehao from(SELECTID Xuehao fromSTUDENT)WHEREXuehao> 1--Normal ExecutionSELECTXuehao from(SELECTID Xuehao fromSTUDENT)WHEREXuehao> 1--Normal ExecutionSELECTXuehao from(SELECTID "Xuehao" fromSTUDENT)WHEREXuehao> 1--Normal ExecutionSELECTXuehao from(SELECTID "Xuehao" fromSTUDENT)WHERE Xuehao > 1--Executive Times "ORA-00904:" Xuehao ": Invalid identifier"SELECTXuehao from(SELECTID "Xuehao" fromSTUDENT)WHERE Xuehao > 1--Executive Times "ORA-00904:" Xuehao ": Invalid identifier"SELECTXuehao from(SELECTID "Xuehao" fromSTUDENT)WHERE"Xuehao"> 1--Executive Times "ORA-00904:" Xuehao ": Invalid identifier"
SELECT"Xuehao" from(SELECTID "Xuehao" fromSTUDENT)WHERE"Xuehao"> 1--Normal Execution

This small example can also see that the SQL statement executes the WHERE clause before executing the SELECT clause!