Differences between Access, SQL Server, and common Oracle SQL statements: If you want to be compatible with Access, SQL Server, and Oracle database versions, try to use some general statements, but there are some differences that cannot be avoided. Here is a brief summary. The following A represents Access, S generation
Differences between Access, SQL Server, and common Oracle SQL statements: If you want to be compatible with Access, SQL Server, and Oracle database versions, try to use some general statements, but there are some differences that cannot be avoided. Here is a brief summary. The following A represents Access, S generation
Access, SQLServer, OracleCommonSQLStatementApplicationDifferences
Guan Jinsong PMP
To be compatible with three database versions: Access, SQL Server, and OracleStatementIn the process, try to use some commonStatementBut there are still someDifferencesThis is a brief summary.
The following A Indicates Access, S indicates SQL Server, and O indicates Oracle.
1. Obtain the current system time
A: Select Now ()
S: Select Getdate ()
O: Select Sysdate From Dual
2. connection string
A :&
S: +
O: |
3. String functions (intercepting strings, uppercase letters, lowercase letters, and searching string locations)
A: Mid, UCase, LCase, InStr
S: SubString, Upper, Lower, CharIndex
O: SubStr, Upper, Lower, InStr
4. determine the valueStatement
A: IIF
S: Case When Then
O: Decode
5. Time Field
A: Use the # separator or convert it to A date.
SELECT * FROM Books WHERE RegDate = #2007-5-1 # Or
SELECT * FROM Books WHERE RegDate = CDate ('2017-5-1 ′)
S: You can use ''directly''
SELECT * FROM Books WHERE RegDate = '2017-5-1 ′
O: To_Date
SELECT * FROM Books WHERE RegDate = To_Date ('2017-05-01 ', 'yyyy-mm-dd ')
6. Data Type Conversion
A: CStr, CInt, CDate
S: Convert, CAST
O: To_Char, To_Number, To_Date
7. Inser ..... Select... FromStatement
Insert Into a (ii, jj, kk) (Select ii, jj, kk From B)
SuchStatementIn SQL Server and Oracle, but in Access, INSERT INTO is reported.StatementSyntax error. Access should SelectStatementRemove the brackets as follows:
Insert Into a (ii, jj, kk) Select ii, jj, kk From B
At the same time, there will be no errors written in SQL Server and Oracle.
8. Keywords
Some letters are keywords in Access and SQL Server, which need to be defined by [] to solve the problem, for example: at, name
9. Alias
When fields in SQL Server and Oracle start aliases, as can be omitted, and Access cannot
10. Table name prefix
Select *, 1 From
SuchStatementThere is no error in Access and SQL Server, but it cannot be done in Oracle. It must be written as follows:
Select A. *, 1 From
11. IfStatementThere are: (colon), for example, a query with a time field: CalcDate = #11:00:00 #, the following error occurs in Access: the parameter object is incorrectly defined. Provide inconsistent or incomplete information. Set
Set ParamCheck of Query to False.
12. The logical values in Access are-1 and 0, while those in SQL Server are 1 and 0. Therefore, write BoolField = 1.StatementIf there is a compatibility problem, change it to BoolField <> 0
13. The varchar (text) type of Access is only 255 at the maximum. Therefore, if a text field is greater than 255, it is best to define it as the remarks type (Access) or text type (in SQL Server ).